I have the following query, where I am trying to get all rows for a specifiec job number from a table named DeliveryDetails and the deliverynote number if it exists and isn't cancelled (i.e. dn.Cancelled = 0):

This only returns rows from DeliveryDetails where there is a deliverynote, because I have specifed that dn.Cancelled should be false (0). What I need to do is write a query to the effect 'dn.jobnumber = xxxx and if dn.DeliveryNumber is not null then dn.cancelled = 0' but obviously this doesn't work. Is this possible?

You may use this in that case.

SELECT dd.*, dn.DeliveryNoteNumber
FROM DeliveryDetails dd FULL OUTER JOIN DeliveryNotes dn
ON dd.DeliveryNumber = dn.DeliveryNumber
WHERE dd.JobNumber = '60123' AND dn.Cancelled = 0
AND dn.DeliveryNumber is not NULL
ORDER BY dd.DeliveryDate, dd.DeliveryNumber

Hope that Helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

I don't think this does what he wants...

Perhaps:

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

That's brilliant Hal. Thanks very much to both of you!

It worked? Wow.... I answered one correctly :)

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Great Hal,

Actually I was think of posting the one you did, But was not sure what data would be there in dn.DeliveryNumber if CANCELLED=0. Then gave this one. Had that not worked for him and had you not anwered him with this, I would be forced to post the one you did;). Anyways, he got that, and you too are happy that it worked

Cheers!

_________________________
-Vijay G
Strive for Perfection