CODEWITHSUNDEEP
mysqlsqlsql-server
Manav Saxena
Manav Saxena

Reputation: 473

SQL Combining data from 3 tables to from a 4th table based on some conditions

I have 3 tables:-

table1 :-

ReportType | ResourceId
  t2         123
  t3           5

table2:-

   Id | Name | Created
   1     A      10
   2     B      11
   123   C      12

table3:-

Id | Name | Created
4     D       13
5     E       14
6     F       15

table1's ResourceId and table2 and 3's Id column have same values

I want to create a 4th table like this:-

ReportType | ResourceId | Name | Created
t2             123         C      12
t3              5          E      14

such that wherever table1's ReportType is t2 I want the Name and Created value from table2 for the condition table1.ResourceId = table2.Id and wherever table1's ResourceType is t3 I want the Name and Created value from table3 for the condition table1.ResourceId = table3.Id.

PS: This isn't some sort of HomeWork. I have been stuck at this query for the past 1 hour, I have read various answers and tried a few queries of my own before posting the question. Any help would really be appreciated.

Upvotes: 0

Views: 36

Answers (2)

prashant sugara
prashant sugara

Reputation: 321

You can use the below query:

select report_type, resourceid,name, created from dbo.t2, dbo.t1 where report_type='t2' and ResourceId=id UNION select report_type, resourceid,name, created from dbo.t3, dbo.t1 where report_type='t3' and ResourceId=id;

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

Explanation in comments :)

--here we join first and second table, but we filter results to include only ReportType = t2
select t1.ReportType, t1.ResourceId, t2.Name, t2.Created from table1 as t1 join table2 as t2 on t1.ResourceId = t2.id
where t1.ReportType = 't2'

union all

--here we join first and third table, but we filter results to include only ReportType = t3
select t1.ReportType, t1.ResourceId, t3.Name, t3.Created from table1 as t1 join table3 as t3 on t1.ResourceId = t3.id
where t1.ReportType = 't3'

Upvotes: 1

Related Questions