Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

problem joining two tables 1

Status
Not open for further replies.

bmc1234

Programmer
Jul 21, 2005
50
US
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.

Code:
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"
 
something like this:

Code:
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.
[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]
 
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
 
Thanks flyover, that worked great. I can't believe I didn't think of something so simple as that before. Thanks again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top