Damn SQL Joins

[LondonManc]

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!

Or just where print flag = 0 and COALESCE(audit type,2) =2

[GreenMirror]

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!

And audit type is null......

[original PM]

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!

[WTFH]

Well that's the most productive I've been so far today

Likewise!

[mudskipper]

too slow - MS is in there already

Well that's the most productive I've been so far today

[WTFH]

too slow - MS is in there already

[mudskipper]

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

SELECT tblinvoice.invoicenumber, tblaudit.auditdate
FROM tblinvoice 
LEFT Join tblaudit ON tblinvoice.invoicenumber = tblaudit.invoicenumber AND tblaudit.audittypeid = 2
WHERE tblinvoice.printflag = 0

[original PM]

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!

[mudskipper]

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

[WTFH]

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?

[original PM]

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?