Query to find data not in a junction table

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

General SQL Server Forums

New to SQL Server Programming

Query to find data not in a junction table

Author

Topic

maffc
Starting Member

1 Post

Posted - 2011-05-02 : 15:24:52

Hi All

I have a problem trying to create a query to return data from one table that does not exist in another. The problem is the 'Other' Table is a Junction table and the data does exist, just not for that particular union.

Table 1 lists data called Schedule types and has the following columns

ST_id int [PK]
ST_Desc nvarchar(10)
ST_active bit

Table 2 lists data called Result types and has the following columns

RST_id int [PK]
RST_RESCAT_id int
RST_desc nvarchar(50)
RST_Active bit

The junction table was created to allow many to many relationships between Table 1 and Table 2 and contains the following columns

JRSTST_id int [PK]
JRSTST_RST_id int [FK to table 2]
JRSTST_ST_id int [FK to table 1]

I have easily created a stored procedure to return all schedule types (table 1) that exist in the junction table for any given result type.

SELECT dbo.[JUNCTION REST ST].JRSTST_id, dbo.SCHEDTYPES.ST_Desc
FROM dbo.[JUNCTION REST ST] INNER JOIN
dbo.SCHEDTYPES ON dbo.[JUNCTION REST ST].JRSTST_ST_id = dbo.SCHEDTYPES.ST_id
WHERE (dbo.[JUNCTION REST ST].JRSTST_RST_id = @selID)
ORDER BY dbo.SCHEDTYPES.ST_Desc

However what i am finding hard to do is create one to return the schedule types that don't exist in the table for a given result type.
I've tried the following

SELECT dbo.SCHEDTYPES.ST_id, dbo.SCHEDTYPES.ST_Desc, dbo.[JUNCTION REST ST].JRSTST_RST_id
FROM dbo.SCHEDTYPES INNER JOIN
dbo.[JUNCTION REST ST] ON dbo.SCHEDTYPES.ST_id = dbo.[JUNCTION REST ST].JRSTST_ST_id
WHERE (dbo.[JUNCTION REST ST].JRSTST_RST_id = @selID) AND (dbo.[JUNCTION REST ST].JRSTST_ST_id IS NULL)
ORDER BY dbo.SCHEDTYPES.ST_Desc

Unfortunately this gives me zero returns. The sample RST_id I provide to the query is known to have only 3 of the 5 possible ST_id associated to it in the junction table.
I'm assuming the problem is because other two ST_ids do exist in the junction table, just not for the RST_id.

Any help on resolving this would be very much appreciated.

Thanks For Looking

Mat

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-02 : 15:36:26

Think you are looking for all the possible combiations that don't exist

select a.ST_ID, b.RST_ID
(select distinct ST_ID from scheculetypes) a
cross join
(select distinct Rst_iD from resulttypes) b
left join junctiontable c
on a.ST_ID = c.JRSTST_ST_ID
and b.RST_ID = c.JRSTST_RST_ID
where c.JRSTST_ST_ID is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources