Query help with 2 tables plus one more - easy I think

[hamilton4003]

I have two tables (tblRequest and tblDev) whose items have many-to-many
relationships with each other. I have set things up like this
(simplified):

Table 1 fields: tblRequest.PrimaryKey tblRequest.Description
example: 10 45 Elm
11 63 Green
12 123 Main Street
13 23 Pine

Table 2 fields: tblDev.PrimaryKey tblDev.Description
example: 20 Local. No issues
21 City owned and main
22 Out of county
23 Not seen by officer

Then Table 3 keeps track of the relationships between tables 1 and 2 by
tracking the primary key of the linked items in tables 1 and 2.
Table 3 fields: tblLink.RequestPrimaryKey tblLink.DevPrimaryKey
10 20
10 21
11 22
12 22

Items from tables 1 and 2 may or may not have a relationship listed in
table 3.

So given an item in tblRequest:
tblRequest.PrimaryKey tblRequest.Description
12 123 Main Street

I need two queries...
The first query would return ALL items from tblDev that are not
associated with this item in tblRequest - in this case 20, 21, 23.

The second query would return ALL items from tblDev that ARE associated
with this item in tblRequest - in this case 22.

That's it. Thanks in advance

[David Portas]

For future posts you'll find that a few lines of DDL can save you a lot
of typing and will make things a lot clearer for others. Here's how I
understand your table structure from your narrative:

CREATE TABLE Request (request_no INTEGER PRIMARY KEY, description
VARCHAR(20) NOT NULL UNIQUE)

CREATE TABLE Dev (dev_no INTEGER PRIMARY KEY, description VARCHAR(20)
NOT NULL UNIQUE)

CREATE TABLE Link /* Meaningless name for a table! */ (request_no
INTEGER NOT NULL REFERENCES Request (request_no), dev_no INTEGER NOT
NULL REFERENCES Dev (dev_no), PRIMARY KEY (request_no,dev_no))

(I dropped "tbl". Hungarian notation is generally considered bad form
in a database schema.)

Here are the two queries you asked for:

SELECT D.dev_no, D.description
FROM Dev AS D
LEFT JOIN Link AS J
ON D.dev_no = J.dev_no
AND J.request_no = 12
WHERE J.dev_no IS NULL

SELECT D.dev_no, D.description
FROM Dev AS D
JOIN Link AS J
ON D.dev_no = J.dev_no
AND J.request_no = 12

--
David Portas
SQL Server MVP
--