| Re: Confused about a MySQL Statement [message #173073 is a reply to message #173072] |
Sun, 20 March 2011 17:10  |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 3/20/2011 12:58 PM, Denis McMahon wrote:
> On Sat, 19 Mar 2011 14:05:56 -0700, Joe wrote:
>
>> I am trying to get a list from one table, and using another table to
>> subtract from that list.
>>
>> I am trying to us:
>>
>> 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?
>
> I don't see how your subquery is extracting from the list, normally if I
> want to exclude the results of a subsquery from a result set I use:
>
> ".... field NOT IN (SELECT field FROM table WHERE condition)"
>
> What do you get if you try the following:
>
> SELECT * FROM entry, chosen WHERE entry.video_id = chosen.video_id AND
> chosen_win = "" AND sort_cat = 2;
>
> SELECT * FROM entry, chosen WHERE entry.video_id = chosen.video_id AND
> chosen_win<> "" AND sort_cat = 2;
>
> Do either of those queries give you the results you want?
>
> Apologies if my sql is a little rusty, It's about 20 years since I did
> that course.
>
> Rgds
>
> Denis McMahon
MySQL is very bad when it comes to subselects.
Another example as to why he should ask in an appropriate newsgroup.
He'll get good answers in the appropriate newsgroup.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]