Query Help... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help...

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-02-04 : 06:59:57

i have got two tables trnpurchase, trninvoice.
trnpurchase table has fields such as grp, make, item, colour, serialno, orderno, challanno etc.
trninvoice table has fields serialno among other fields.

i want to select grp, make, item, colour, serialno from trnpurchase but the serialno should not be present in
trninvoice table.

i mean, if serialno = 1 is present in both the tables then the grp, make, item, colour, serialno frpm trnpurchase
will not be selected. if it is not present in trninvoice then only these\ things will be selected.

i have written the following :


SELECT TP.Grp, TP.Make, TP.Item, TP.Purchase_Price, TP.Serial_No, TP.Model_No, TP.Warranty, TP.Color, TP.Incoming_Vat 
FROM trnPurchase AS TP, TRNINVOICE AS TI
WHERE TP.Order_No = 'WO/001/07-08' And 
TP.Challan_No = 'C1' AND
TP.SERIAL_NO NOT IN 
(
SELECT SERIAL_NO 
FROM TRNINVOICE 
)

the problem is when the trninvoice table is empty, the query is returning nothing.
any help is appreciated.
thanks and regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 07:18:56

[code]SELECT TP.Grp,
TP.Make,
TP.Item,
TP.Purchase_Price,
TP.Serial_No,
TP.Model_No,
TP.Warranty,
TP.Color,
TP.Incoming_Vat
FROM trnPurchase AS TP
LEFT JOIN TRNINVOICE AS TI ON TI.SERIAL_NO = TP.SERIAL_NO
WHERE TP.Order_No = 'WO/001/07-08'
AND TP.Challan_No = 'C1'
AND TI.SERIAL_NO IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"