CODEWITHSUNDEEP
sqljoin
Zusee Weekin
Zusee Weekin

Reputation: 1358

Merge data from two tables into one row

I don't have much experience with SQL query. I tried to do this using INNER JOIN but it is resulting two rows. But I need to get one row as I defined in below. This are tables :

Table 1

ID | GP | NAME | T1
12 | 1| AAASAS  | 23

Table 2

ID |GP | k1
12 | 1|  600
12 | 1| 300

Here both ID and GP are same fields in two tables. Table 1's primary key is ID but other table doesn't have any PK.

Expected result 

ID |GP | Name | T1| K1| K1
12 | 1 | AAASAS|23|600 | 300

Any clue or help would be appreciated.

Upvotes: 0

Views: 103

Answers (3)

shree.pat18
shree.pat18

Reputation: 21757

Assuming that 1 row in Table1 maps to only 2 rows in Table2, try this:

select src.id, src.gp, src.name, src.t1, tgt.k1max, tgt.k1min 
from
Table1 src
inner join
(select id, gp, max(k1) k1max, min(k1) k1min
 from Table2
 group by id, gp) tgt
on src.id = tgt.id and src.gp = tgt.gp

Demo

Upvotes: 1

romaneso
romaneso

Reputation: 1122

You just have to write

Select table1.id, table1.gp, table1.name, table1.t1, table2.k1, table2. k112 .... from table1, table2 where table1.id=table2.id;

You just have to take care if the primary key from table1 is a foreign key in table2.

Upvotes: 0

dns_nx
dns_nx

Reputation: 3953

That won't work in 2 seperate columns. You can use a SUM() function to get only one line, but than you would get 900 for the k1 value and not 600 and 300 in two columns.

Upvotes: 0

Related Questions