Mysql does not support MINUS operator which is not the end of the world. For most queries you can really care less if it’s implemented or not. My web app never needs to use it to function properly, which makes MySQL a perfect choice here. But, sometimes human beings are more demanding than simple php applications. Especially if you want to extract some non-obvious information from the database.
Just today I wanted to see who in our company does not have an assigned laptop. I have two tables – one is user table which contains names and info of all the employees. The other one is computer table which contains service tags of all the machines we own. You can join these two on username and you figure out which computer is assigned to who. But I wanted the opposite thing – which is a logical intersection of the two tables on username. Which is usually obtained via MINUS operator.
Of course MySQL does not have MINUS, so I spent 5 minutes staring at the screen and typing mangled SQL queries trying to extract this info. Then I decided to google for it. Here is the solution:
SELECT users.username
FROM users
LEFT JOIN computers
ON computers.username = user.username
WHERE computers.username IS NULL
Duh! I feel dumb that I couldn’t figure this out myself, but I was nowhere near the solution. Anyways it’s good to know for the future.
Thanks, I had the same problem and also just stared at the screen ;)
yeah…thats a very nice solution to this problem…
very good
:)
Very nice, exact solution that I was looking for. It is a simple solution yet difficult unless you look at the problem in different way. Good job.
Thank you google, for taking me here, and thank you sir for answering my question. You just saved me bunches of time.
Hey What is the need of ‘WHERE computers.username IS NULL’ here
Good solve! but I have problem, how to select that with a criteria e.g where id=something?
Table 1 is a roster of Bowlers who can belong to one or many bowling centers. Table 2 is a roster of bowlers signed up for an event today. I list my bowling center roster with all names for that bowling center who are elegible to signup for the event roster (table 2) It is possible to signup bowlers at different times. After I signup a bowler and close the signup page, when I go back to the signup page I do not want to see the bowlers who have already signed up.
I have tried many sql select statements, none of which produce the desired result:
1) SELECT DISTINCT BH.BID
FROM BOWLERS_HOUSE BH
LEFT JOIN EVENT_ROSTER ER
ON ER.BID = BH.BID
WHERE ER.HOUSE_ID = 40
AND ER.EVENT_ID = 201007073010100101010300
AND ER.BID IS NULL; Note: returns empty result
2) SELECT DISTINCT BH.BID
FROM BOWLERS_HOUSE BH
WHERE BH.HOUSE_ID = 40
AND (BH.BID) NOT IN
(“SELECT ER.BID FROM EVENT_ROSTER ER WHERE EVENT_ID = 201007073010100101010300 AND ER.HOUSE_ID = 40”);Note returns all BID’s in Table 1
3) SELECT BOWLERS_HOUSE.BID
FROM BOWLERS_HOUSE LEFT JOIN EVENT_ROSTER ON BOWLERS_HOUSE.BID=EVENT_ROSTER.BID
WHERE EVENT_ROSTER.BID IS NULL;
Note: returns the bowlers who have aready signed up and are on Table 2, I want only the bowlers who have not ALREADY signed up that are NOT ON table 2
I can run the program the way it is or I can build a very convoluted compare routine to eliminate the Table2 records from my display of the House roster, but need something that works. Oh, I tried SQL MINUS, but of course mysql disregards this very useful tool.
Any ideas?
Thanks in advance!
Didn’t it easier to use outer joins instead of left/right inner join with allowing NULL values explicitly? In Database theory the outer join is for this purpose, otherwise the article is good.