I need to select all projects with client name but some records have a null id

[LAMDB]

Hi All,

I have to do a rather simple query, but a previous design error that did not make one of the fields required (it allows nulls when it shouldn't have) makes it a bit harder.

I have a table projects and a table clients

Projects_Tbl
ProjId
ProjName
ClientId

Clients_Tbl
ClientId
ClientName

I need to select all projects with client name. The SQL statement should work fine as:

Select * from Projects_Tbl, Clients_Tbl
where Projects_Tbl.ClientId = Clients_Tbl.ClientId

The problem is that it left out all projects with NULL clientId (in Projects_Tbl)

Can anyone help?

I tried using an "OR" such:

where Projects_Tbl.ClientId = Clients_Tbl.ClientId or Projects_Tbl.ClientId is NULL

but it does not return the right results.

Thanks.

[dickersonka]

SELECT p.ProjId, p.ProjName, p.ClientId, c.ClientName 
FROM
Projects_Tbl p 
left join 
Clients_Tbl c
on p.ClientId = c.ClientId

[LAMDB]

dickersonka,

Thanks so much. It worked great!
Now, could you tell me why this would work and not the where clause that I use?
Where should I use JOIN and when should I use WHERE?

Thanks again!

SELECT p.ProjId, p.ProjName, p.ClientId, c.ClientName 
FROM
Projects_Tbl p 
left join 
Clients_Tbl c
on p.ClientId = c.ClientId

[dickersonka]

the left join is the difference, don't think of it in terms of left join vs where, you were doing an inner join

left join will return all rows from the left table (projects) even if there are no matches in the right table (clients)

where limits the results, joins will combine them, thats the best way to think of them, here is your original statement with a inner join

SELECT p.ProjId, p.ProjName, p.ClientId, c.ClientName 
FROM
Projects_Tbl p 
left join 
Clients_Tbl c
on p.ClientId = c.ClientId
SELECT p.ProjId, p.ProjName, p.ClientId, c.ClientName 
FROM
Projects_Tbl p 
inner join 
Clients_Tbl c
on p.ClientId = c.ClientId

notice the major difference changing from left to inner

[LAMDB]

Thanks so much for the clarification! Very helpful indeed!

Thanks!!