Avatar of corey gashlin
corey gashlin
 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


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

Open in new window



This is waht I made and it not working.
Microsoft SQL Server

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
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.
corey gashlin

ASKER
The Proj_id only Exists in Table 1.
ASKER CERTIFIED SOLUTION
ste5an

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim Horn

>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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23