CODEWITHSUNDEEP
sql
user1967326
user1967326

Reputation: 45

Query 3 Tables one table has 3 rows I need to combine into 1 in some joint way

I'm having trouble figuring out a query. I have 3 tables. I figured out how to get the data from the tables to show up in one row, but one of the table has the data setup weird. All the tables share a report ID.

Table 1

RID AllOtherData
1    Stuff
2    Stuff
3    Stuff

Table 2

RID Description
1   Descript
2   Descript
3   Descript

Table 3 is where I'm having the trouble

RID Item Code
1     1   RWA
1     2   ABA
1     3   BBC
2     1   BBC
2     2   NULL
2     3   NUll
ETC*

What I'm asking is how would I query to get RID, ALLotherstuf.T1, descript.t2, code.t3 as item1 code, code.t3 as item2 code, code.t3 as item3 code.

If someone could point me or give me an example of what kind of query this is or even possible I'd be much obliged.

Upvotes: 2

Views: 202

Answers (3)

sgeddes
sgeddes

Reputation: 62831

Is this what you're looking for? This was revised from @Gordon's previous response.

select t1.RID, t1.AllOtherData, t2.Description,
       max(case when t3.item = 1 then t3.code end) as item1code,
       max(case when t3.item = 2 then t3.code end) as item2code,
       max(case when t3.item = 3 then t3.code end) as item3code
from table1 t1 join
     table2 t2 on 
     t1.RID = t2.RID join
     table3 t3 on
     t1.RID = t3.RID
group by t1.RID, t1.AllOtherData, t2.Description

And the SQL Fiddle.

Upvotes: 1

Taryn
Taryn

Reputation: 247650

If you are using SQL Server you can also use the PIVOT function:

select *
from
(
  select t1.RID, 
    t1.AllOtherData, 
    t2.Description,
    t3.item,
    t3.code
  from table1 t1 
  inner join table2 t2 
    on t1.RID = t2.RID 
  inner join table3 t3 
    on t1.RID = t3.RID
) src
pivot
(
  max(code)
  for item in ([1], [2], [3])
) piv

See SQL Fiddle with Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want to pivot the columns. First, join all the tables together. This produces the codes on separate rows. To bring them to one row, use group by. The following works in any database:

select t1.RID, t1.ALLotherstuf, t2.descript,
       max(case when t3.item = 1 then t3.code end) as item1code,
       max(case when t3.item = 2 then t3.code end) as item2code,
       max(case when t3.item = 3 then t3.code end) as item3code
      code.t3 as item1 code, code.t3 as item2 code, code.t3 as item3
from t1 join
     t2 o
     on t1.RID = t2.RID join
     t3
     on t1.RID = t3.RID
group by t1.RID, t1.ALLotherstuf, t2.descript

Upvotes: 0

Related Questions