CODEWITHSUNDEEP
sqlif-statement
Alfonso Fernandez-Ocampo
Alfonso Fernandez-Ocampo

Reputation: 887

SQL if statement with two tables

Given the following tables:

table objects

id    Name   rating
1     Megan      9
2     Irina     10
3     Vanessa    7
4     Samantha   9
5     Roxanne    1
6     Sonia      8

swap table

id   swap_proposalid   counterpartyid
1                 4                2
2                 3                2

Everyone wants the ten. I would like to make a list for Irina of possible swaps where id 4 and 3 don't appear because the propositions are already there.

output1

id   Name    rating
1     Megan      9
5     Roxanne    1
6     Sonia      8

Thanks

Upvotes: 0

Views: 3811

Answers (3)

onedaywhen
onedaywhen

Reputation: 57063

Guessing that the logic involves identifying the objects EXCEPT the highest rated object EXCEPT propositions with the highest rated object e.g. (using sample DDL and data kindly posted by @nonnb):

WITH ObjectHighestRated
     AS 
     (      
      SELECT ID
        FROM MyTable 
       WHERE Rating = (
                       SELECT MAX(T.Rating)
                         FROM MyTable T
                      )
     ),
     PropositionsForHighestRated
     AS
     (
      SELECT swap_proposalid AS ID
        FROM SwapTable
       WHERE counterpartyid IN (SELECT ID FROM ObjectHighestRated)
     ), 
     CandidateSwappersForHighestRated
     AS
     (
      SELECT ID
        FROM MyTable 
      EXCEPT 
      SELECT ID
        FROM ObjectHighestRated
      EXCEPT 
      SELECT ID
        FROM PropositionsForHighestRated                    
     )
SELECT * 
  FROM MyTable
 WHERE ID IN (SELECT ID FROM CandidateSwappersForHighestRated);

Upvotes: 0

StuartLC
StuartLC

Reputation: 107317

This works

SELECT mt2.ID, mt2.Name, mt2.Rating
FROM [MyTable] mt2  -- Other Candidates
   , [MyTable] mt1 -- Candidate / Subject (Irina)
WHERE mt2.ID NOT IN 
  (
    SELECT st.swap_proposalid
    FROM SwapTable st
    WHERE
      st.counterpartyid = mt1.ID
  )
AND mt1.ID <> mt2.ID -- Don't match Irina with Irina
AND mt1.Name = 'Irina' -- Find other swaps for Irina

-- Test Data

CREATE TABLE MyTable
(
  ID INT, 
  Name VARCHAR(100),
  Rating INT
)
GO
CREATE TABLE SwapTable
(
  ID INT,
  swap_proposalid INT,
  counterpartyid INT
)
GO

INSERT INTO MyTable VALUES(1     ,'Megan',       9)
INSERT INTO MyTable VALUES(2     ,'Irina',      10)
INSERT INTO MyTable VALUES(3     ,'Vanessa',    7)
INSERT INTO MyTable VALUES(4     ,'Samantha',   9)
INSERT INTO MyTable VALUES(5     ,'Roxanne',    1)
INSERT INTO MyTable VALUES(6     ,'Sonia',      8)

INSERT INTO SwapTable(ID, swap_proposalid, counterpartyid)
VALUES (1, 4, 2)
INSERT INTO SwapTable(ID, swap_proposalid, counterpartyid)
VALUES (1, 3, 2)

Upvotes: 0

cjk
cjk

Reputation: 46465

This should do the trick:

SELECT o.id, o.Name, o.rating 
FROM objects o
LEFT JOIN swap s on o.id = s.swap_proposalid
WHERE s.id IS NULL
AND o.Name != 'Irina'

Upvotes: 3

Related Questions