sql query to join two tables

Question

1.00/5 (1 vote)

See more:

hi..
I have a problem. please help me in solving it.

I HAVE TWO TABLE 1ST STRUCTURE IS
ID HEAD1 HEAD2 HEAD3
10 3400 500 600
11 6400 800 600
12 8400 500 600
13 1400 900 600

-----------------------------

2ND TABLE IS

HEADNAME DESCRIPTION
HEAD1 AAAAA
HEAD2 BBBBB
HEAD3 CCCCC
HEAD4 DDDDD
-------------------------------------------------

I HAVE TO GET VALUE WITH THEIR DESCRIPTION. AS :---
ID DESCRIPTION AMT
10 AAAAA 3400
10 BBBBB 500
10 CCCCC 600
11 AAAAA 6400
11 BBBBB 800
11 CCCCC 600
12 AAAAA 6400
12 BBBBB 800
12 CCCCC 600
13 AAAAAA 1400
13 BBBBBB 900
13 CCCCCC 600

Posted 1-Oct-12 0:52am

Member 8083455

Updated 1-Oct-12 0:55am

v2

Add a Solution

Comments

[no name] 1-Oct-12 6:54am

have u tried once..googled or not..???

Abhijit Parab 1-Oct-12 7:06am

This is not possible with simple join. you have to write some logic for that

ridoy 1-Oct-12 7:30am

i think it is not possible until you create a new field for ID in your 2nd table and then join those 2 tables on its ID

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

solanki.net · Answer 1 · 2012-10-01T01:44:00

Solution 1

For joining at least one column should be common in both table but in your table there is no common column and tell me how can u get amount because AMT column does not exist in any table .

Posted 1-Oct-12 1:44am

solanki.net

Aarti Meswania · Answer 2 · 2012-10-01T01:51:00

Solution 2

you have not designed database very well
but as per input output example,
you can use below query,

SQL

select * from
(
select Id,(select Description from table2 where headname='head1') as Description,head1 as amt
from table1
union all
select Id,(select Description from table2 where headname='head2') as Description,head2 as amt
from table1
union all
select Id,(select Description from table2 where headname='head3') as Description,head3 as amt
from table1
) as a
order by id,Description

Happy coding!
:)

Posted 1-Oct-12 1:51am

Aarti Meswania

Comments

Member 8083455 1-Oct-12 8:30am

THANKS AARTI
BUT IF WE INCREASE NO OF HEAD AS HEAD4, HEAD5, .... THEN WE CANT GET IT.
I WANT TO GET IT DYNAMICALLY.
AND PLEASE COULD YOU HINT ME TO MODIFY DATABASE STRUCTURE...

Aarti Meswania 1-Oct-12 8:33am

but you have make it static
see structure of table-1
there are static columns Head1,head2... you have to add columns while adding new head same way you have to change query

Aarti Meswania 1-Oct-12 8:51am

table1's structure should be like below
declare columns as below
id
head
Amt

id head amt
10 head1 3400
10 head2 500
10 head3 600
11 head1 6400
...

then query will be easy
select Id,Description,amt
from table1
left join table2 on headname=head

Member 8083455 4-Oct-12 6:00am

CAN WE GET RIGHT STRUCTURE BY USING DYNAMIC PIVOT.
IF YES PLEASE TELL ME QUERY.

Aarti Meswania 4-Oct-12 6:11am

no your question is not related to pivot view, you are in wrong way,
pivot is what it is something like display data columnwise instead of row wise
e.g in calender i want name of days(sun,mon..) horizontally not vertically in that case i will use pivot query to arrange my data from sql.

if i say in 2nd table enter new row having name head9, then in first table you will going to insert a column having name head9!!!
it is not a practical way to do this.

now guess i have entered head1...head999 now, what?
you will going to create 999 columns in first table?! :)
that's why I have suggested to change database structure.

Member 8083455 5-Oct-12 6:22am

THANKS.

Aarti Meswania 5-Oct-12 6:23am

welcome :)

Member 8083455 11-Oct-12 1:07am

hi aarti.
as you suggest i have changed database structure.
but now how can i dynamically pivot this table in previous structure.
now my table contain date, code,head_name, amount.
and i want o/p in following format:
date , code, head1, head2, head3,.........

Aarti Meswania 11-Oct-12 3:36am

if now tbl1 have structure like below and you want output as given in Question then there is no need for pivot query you will get output as given in question just use below query

id head amt
10 head1 3400
10 head2 500
10 head3 600
11 head1 6400
...

select Id,Description,amt
from table1
left join table2 on headname=head