CODEWITHSUNDEEP
mysql
user3774733
user3774733

Reputation: 5

Multi join one to many

Trades

id |Trade_name |
1  |  trade1   |
2  |  trade2   |
3  |  trade3   |
4  |  trade4   |

Users

Name |  Primary_id(FK to trade.id)  |  secondary_id (FK to trade.id)  |
John |            1                 |              2                  |
Alex |            3                 |              4                  |

This is my current SQL which joins trades.t1 to primary & secondary.id:

select 
`users`.`name` , 
`t1`.`trade_name` AS `Primary_trade`,
`t2`.`trade_name` AS `Secondary_trade`,
FROM `users`
right JOIN `trades` `t1` On (`t1`.`trade_id` = `users`.`primary_trade_id`) 
right JOIN `trades` `t2` on (`t2`.`trade_id` = `users`.`secondary_trade_id`)

My question is, how do I identify which trades are not used for users both as primary or secondary. I want to see record where a trade does not exist in both primary or secondary column so I can perform housekeeping.

Thanking you all in advance for your help.

Upvotes: 0

Views: 32

Answers (2)

Javaluca
Javaluca

Reputation: 857

If you need only the trades rows

SELECT t.*
FROM trades t
WHERE NOT EXISTS (  SELECT 'u'
                    FROM Users u
                    WHERE u.Primary_id = t.id
                    OR u.Secondary_id = t.id
                )

Upvotes: 1

mariobgr
mariobgr

Reputation: 2201

I think this should work for you:

SELECT * FROM trades WHERE id NOT IN (SELECT Primary_id FROM Users) AND id NOT IN (SELECT Secondary_id FROM Users)

It selects the rows which are not in either primary_id nor secondary_id

Upvotes: 0

Related Questions