problem joining two tables 1

[bmc1234]

I have two tables, a repairdata table that has a field for statusID and a status table that has an autonumber statusID and a status. I'm trying to create a query that shows all entries from repairdata where the status isn't equal to "shpd" or "comp". The problem is that if I specify any condition, it will also not show entries where there is no status listed at all. Here is my SQL code for the query. I'm pretty sure you can ignore the products table join.

SELECT repairdata.ProductID, Products.Description, repairdata.CustomerSerial, repairdata.ITTSerial, status.Status, repairdata.StatusDate, repairdata.Comments, repairdata.LocationID
FROM Products INNER JOIN (status right JOIN repairdata on status.statusid=repairdata.statusid) ON Products.ProductID = repairdata.ProductID
where status.status<>"shpd" and status.status <>"comp"

 

[flyover789]

something like this:

SELECT repairdata.ProductID, Products.Description, repairdata.CustomerSerial, repairdata.ITTSerial, status.Status, repairdata.StatusDate, repairdata.Comments, repairdata.LocationID
FROM Products INNER JOIN (status right JOIN repairdata on status.statusid=repairdata.statusid) ON Products.ProductID = repairdata.ProductID
where (status.status<>"shpd" and status.status <>"comp") [blue]or status.status IS NULL[/blue]

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[PHV]

Another way:
WHERE Nz(status.status, '') Not In ('shpd', 'comp')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bmc1234]

Thanks flyover, that worked great. I can't believe I didn't think of something so simple as that before. Thanks again.