November 2nd, 2004, 05:15 AM
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 2
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Join: Conditionally include data from sub rows?
Having one invoice table (ord) and one 'person' table (actor) I would like to include the name of the person who is responsible for an invoice:
SELECT Ord.OrdNo, Ord.Selbuy, AC.Nm AS 'Responsible', AC.EmpNo
FROM Ord
LEFT OUTER JOIN Actor AS AC ON Ord.Selbuy = AC.EmpNo
WHERE ord.ordno = 23505
This works perfectly fine if Ord.Selbuy has a corresponding value in Actor:
|26914 |21|Yvonne| 21|
or if there is no corresponding value in Actor:
|26914 |21|NULL| NULL|
But what if Ord.Selbuy=0? Then I end up with 3285 rows from Actor! This happens because Actor.EmpNo=0 is allowed. Persons which have never been employed or used to be employed gets Actor.EmpNo=0.
Can I create a SELECT statement which only returns data from the INVOICE row if Ord.Selbuy=0?
|26914 |21|NULL| NULL|
p.s. I'm not able to change table structure/behavior of update procedures, because the tables/code belong to a "bought from the shelf"-business system.
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
