Hello,

I am trying to get a list from one table, and using another table to
subtract from that list. I have tried all sorts of versions, but just
can't hit on the right one.

I am trying to use:

SELECT entry_id, entry.video_id, entry_book, entry_category, sort_cat,
entry_guard_fname, entry_guard_lname, entry_fname, entry_lname
FROM entry, chosen
where sort_cat = 2
and entry.video_id in
(Select video_id from chosen where chosen_win = "")

Which gives me way too may results, 4,042.

The first part, comes up with 157 records, where the second part is
only 5. I want the final list to be 152.

I have hit a wall here, and not sure where to go next. Can anyone
help?

Joe:

What do you consider "the first part"?

Would that be the number of rows from
"Select video_id from chosen where chosen_win = ''"?


Well, if you DON'T want the rows from the latter subquery, you should
specify ...and entry.video_id NOT IN....

But that wouldn't explain why you get so many results.

El 22/03/2011 21:05, Joe escribió/wrote:
IMHO, this join syntax is terribly confusing. You have to mix the join
conditions and the query filters in the WHERE clause and you can never
be 100% sure of how tables are getting joined.

You are already fetching data from `chosen` in the main query :-?

Not being sure of what you want to accomplish, your description suggests
you want something on this line:

SELECT entry_id, en.video_id, entry_book, entry_category, sort_cat,
entry_guard_fname, entry_guard_lname, entry_fname, entry_lname
FROM entry en
LEFT JOIN chosen ch ON en.video_id=ch.video_id
WHERE sort_cat=2
AND ch.chosen_win IS NULL

This also assumes that chosen_win is set to NULL when it doesn't have a
value, rather than empty strings (a fairly non-standard way to store
blank data).


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--