CODEWITHSUNDEEP
sqlt-sql
lucifer63
lucifer63

Reputation: 666

How to choose everything except some number of items?

I have 4 tables.

Every item in the first table is linked through second and third tables to some amount of items in the last table.

Here it is :

enter image description here

I select all appropriate items (i mean the black ones in the last table) using this code : 

SELECT DISTINCT a1.Name, a2.Name, st.Name, st.ID
FROM Agents a1
INNER JOIN Agents   a2 ON a1.Owner = 1 AND a1.ID = a2.AgentID
INNER JOIN AgentGTP ag ON ag.AgentID = a2.ID 
INNER JOIN GTP         ON GTP.ID = ag.GTPID
INNER JOIN Stations st ON st.ID = GTP.StationID


I need to choose all grey items in the last table. How can i do this ?

Upvotes: 1

Views: 46

Answers (2)

MarkD
MarkD

Reputation: 5316

Hard for me to verify this without data, but this might work;

SELECT st.*
FROM Agents a1
JOIN Agents   a2 ON a1.Owner = 1 AND a1.ID = a2.AgentID
JOIN AgentGTP ag ON ag.AgentID = a2.ID 
JOIN GTP         ON GTP.ID = ag.GTPID
RIGHT 
JOIN Stations st ON st.ID = GTP.StationID
WHERE GTP.ID IS NULL

Upvotes: 2

KekuSemau
KekuSemau

Reputation: 6856

I think this is it:

SELECT * FROM Stations S
WHERE
    S.ID NOT IN
(
    SELECT DISTINCT st.ID
    FROM Agents a1
    INNER JOIN Agents   a2 ON a1.Owner = 1 AND a1.ID = a2.AgentID
    INNER JOIN AgentGTP ag ON ag.AgentID = a2.ID 
    INNER JOIN GTP         ON GTP.ID = ag.GTPID
    INNER JOIN Stations st ON st.ID = GTP.StationID
)

Upvotes: 1

Related Questions