• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Damn SQL Joins

original PM

Double Godlike!
Joined
Apr 1, 2008
Messages
12,848
Likes received
2,921
Thanks received
732
Location
Cheshire
Damn...

So this is simple

I have one table and I want to return all records from one table where a value - zero.

I then want to join it to another table but whether there is any data in the other table or not I still want the query to return all records from the first table with just null values if there is no data in the second table...

so thats a left outer join (or left join)

but apparently not

why I am so dumb and what am I missing?
 
Damn...

So this is simple

I have one table and I want to return all records from one table where a value - zero.

I then want to join it to another table but whether there is any data in the other table or not I still want the query to return all records from the first table with just null values if there is no data in the second table...

so thats a left outer join (or left join)

but apparently not

why I am so dumb and what am I missing?

Is the value zero or null in the first table?
Have you a key between them?
What is the relationship - is it one to one, one to many, many to one?
 
Damn...

So this is simple

I have one table and I want to return all records from one table where a value - zero.

I then want to join it to another table but whether there is any data in the other table or not I still want the query to return all records from the first table with just null values if there is no data in the second table...

so thats a left outer join (or left join)

but apparently not

It is.

why I am so dumb

That's probably a thread in its own right ;)

what am I missing?

Post some sample code, expected results, what you're getting
 
zero value in first table (invoice not printed)

there is then going to an audit table - which has multiple rows for each invoice -- however I only want the audit record for the invoice being created

so currently it is

select tblinvoice.invoicenumber, tblaudit.auditdate
from tblinvoice Inner Join tblaudit on tblinvoice.invoicenumber = tblaudit.invoicenumber
where tblinvoice.printflag = 0 and tblaudit.audittypeid = 2

But generally get 2871 records just looking for invoices with a print flag of 0

but only 2848 when trying the above - which is correct as the missing 23 invoices do not have a record in the audit table as they were created prior to the audit functionality coming on line

:sigh: I could pass it to our dev team but it should be simple so killing some time trying to do it myself!
 
zero value in first table (invoice not printed)

there is then going to an audit table - which has multiple rows for each invoice -- however I only want the audit record for the invoice being created

so currently it is

select tblinvoice.invoicenumber, tblaudit.auditdate
from tblinvoice
Inner Join tblaudit on tblinvoice.invoicenumber = tblaudit.invoicenumber
where tblinvoice.printflag = 0 and tblaudit.audittypeid = 2

But generally get 2871 records just looking for invoices with a print flag of 0

but only 2848 when trying the above - which is correct as the missing 23 invoices do not have a record in the audit table as they were created prior to the audit functionality coming on line

:sigh: I could pass it to our dev team but it should be simple so killing some time trying to do it myself!

Inner isn't left. Move your restriction on the audit table to the join, not the where clause

Code:
SELECT tblinvoice.invoicenumber, tblaudit.auditdate
FROM tblinvoice 
LEFT Join tblaudit ON tblinvoice.invoicenumber = tblaudit.invoicenumber AND tblaudit.audittypeid = 2
WHERE tblinvoice.printflag = 0
 
Last edited: 11:30
too slow - MS is in there already
 
found out the problem was not the join

it was the where clause which initially said

where print flag = 0 and audit type = 2

needed to add

or printflag = 0 and audit type = NULL

but cheers everyone rep on it is way!
 

Advertisers

Back
Top