asked on
Join 2 tables and show everything from left table.
I have 2 tables I am trying to join. i have tried joining but it is not working how I would like.
Table 1 - OCI_DISCIPLINES - Shows as below -
discipline
Mechanical
Electrical
Plumbing
Fire Protection
Cx
Low Voltage
Report
Study
CAD
BIM
Table 2 - OCI_VOODOO Shows as below
Department proj_id timing
CAD 2147 21h 0m
Cx 2147 73h 20m
Electrical 2147 40h 30m
Mechanical 2147 88h 30m
Plumbing 2147 42h 0m
Trying to get Table1 to show all the rows for disciplines and join up to table 2.
Result I am looking for is this.
Department proj_id timing
CAD 2147 21h 0m
Cx 2147 73h 20m
Electrical 2147 40h 30m
Mechanical 2147 88h 30m
Plumbing 2147 42h 0m
Low Voltage 2147 0
Study 2147 0
Fire Protection 2147 0
This is waht I made and it not working.
Table 1 - OCI_DISCIPLINES - Shows as below -
discipline
Mechanical
Electrical
Plumbing
Fire Protection
Cx
Low Voltage
Report
Study
CAD
BIM
Table 2 - OCI_VOODOO Shows as below
Department proj_id timing
CAD 2147 21h 0m
Cx 2147 73h 20m
Electrical 2147 40h 30m
Mechanical 2147 88h 30m
Plumbing 2147 42h 0m
Trying to get Table1 to show all the rows for disciplines and join up to table 2.
Result I am looking for is this.
Department proj_id timing
CAD 2147 21h 0m
Cx 2147 73h 20m
Electrical 2147 40h 30m
Mechanical 2147 88h 30m
Plumbing 2147 42h 0m
Low Voltage 2147 0
Study 2147 0
Fire Protection 2147 0
SELECT dbo.OCI_DISCIPLINES.discipline, dbo.OCI_VOODOO.proj_id, dbo.OCI_VOODOO.timing, dbo.OCI_DISCIPLINES.id
FROM dbo.OCI_DISCIPLINES LEFT OUTER JOIN
dbo.OCI_VOODOO ON dbo.OCI_DISCIPLINES.discipline = dbo.OCI_VOODOO.Department Where OCI_VOODOO.proj_id=2147
This is waht I made and it not working.
Microsoft SQL Server
Last Comment
Jim Horn
For those values in Table 1 that are not in Table 2 (Fire Protection, Report, Study), explain for us the logic in assigning a 2147 for proj_id.
ASKER
The Proj_id only Exists in Table 1.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
>The Proj_id only Exists in Table 1.
In your Table 1 sample data there is only one column that is a varchar() Mechanical, Electrical, etc. and I don't see a 2147 value.
So my question is, if you wish to display ALL rows in Table 1 and ONLY the matching values in Table 2, of which one of them is proj_id = 2147, how do you know that for rows in Table 1 and NOT in Table 2 what the proj_id is?
In your Table 1 sample data there is only one column that is a varchar() Mechanical, Electrical, etc. and I don't see a 2147 value.
So my question is, if you wish to display ALL rows in Table 1 and ONLY the matching values in Table 2, of which one of them is proj_id = 2147, how do you know that for rows in Table 1 and NOT in Table 2 what the proj_id is?