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.

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!

Good solve! but I have problem, how to select that with a criteria e.g where id=something?

Hey What is the need of ‘WHERE computers.username IS NULL’ here

Thank you google, for taking me here, and thank you sir for answering my question. You just saved me bunches of time.

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.

yeah…thats a very nice solution to this problem…
very good
:)

Thanks, I had the same problem and also just stared at the screen ;)