impossible SQL query???

[mdwilliam]

I've just taken over the duties of reporting on a rather sloppy inventory system with missing chunks of data here and there. This missing data has brought a loop hole to my queries.....

I have two tables:
Workstat Table
WS_NUM
NAME
TYPE

Items Table
WS_NUM
ASSET
PUR_PRICE
WARRANTY
KIND

The two tables are inner joined @ WS_NUM and I need to query 'WS_NUM', 'NAME', 'TYPE', 'ASSET', 'PUR_PRICE', 'WARRANTY', & 'KIND' It is a one to many relationship with Workstat being 1 and Items being many. I need to see all of this data only for Items of 'Kind' = Computer. Here's where the problem occurs: some entries in 'Workstat' do not have corresponding entries in Items of 'Kind' = Computer, so how do I only display the 'WS_NUM' and 'NAME' for these special cases as opposed to what my query does now - not display any entry that does not have an associated Item of 'KIND' = computer. Any help would be extremely helpful. Thanks

[DNAunion2000]

mdwilliam: I've just taken over the duties of reporting on a rather sloppy inventory system with missing chunks of data here and there. This missing data has brought a loop hole to my queries.....

I have two tables:
Workstat Table
WS_NUM
NAME
TYPE

Items Table
WS_NUM
ASSET
PUR_PRICE
WARRANTY
KIND

The two tables are inner joined @ WS_NUM and I need to query 'WS_NUM', 'NAME', 'TYPE', 'ASSET', 'PUR_PRICE', 'WARRANTY', & 'KIND' It is a one to many relationship with Workstat being 1 and Items being many. I need to see all of this data only for Items of 'Kind' = Computer. Here's where the problem occurs: some entries in 'Workstat' do not have corresponding entries in Items of 'Kind' = Computer, so how do I only display the 'WS_NUM' and 'NAME' for these special cases as opposed to what my query does now - not display any entry that does not have an associated Item of 'KIND' = computer. Any help would be extremely helpful. Thanks

DNAunion: Try this...a LEFT OUTER JOIN.


SELECT Workstat.WS_NUM, NAME, TYPE, ASSET, PUR_PRICE, WARRANTY, KIND
FROM Workstat LEFT OUTER JOIN Items ON Workstat.WS_NUM = Items.WS_NUM
WHERE KIND = "Computer"


DNAunion: The typical join is an INNER JOIN, where only matches in both tables are returned. That is, if something is in the left table and not in the right table, it doesn't get returned; and if something is in the right table and not in the left one, it doesn't get returned: only if it is in both the left and the right table does it appear in the result set.

OUTER JOINS allow all rows from one table to be returned whether or not a match is found in the other table. A LEFT OUTER JOIN returns all rows for the LEFT table in the JOIN clause, even those records that don't have a match in the right table in that clause.