| Confused about a MySQL Statement [message #173061] |
Sat, 19 March 2011 21:05  |
 |
Joe
Messages: 1
Registered: October 2011
Karma: 0
|
Junior Member |
|
|
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?
|
|
|
|
| Re: Confused about a MySQL Statement [message #173062 is a reply to message #173061] |
Sat, 19 March 2011 21:11  |
 |
richard
Messages: 213
Registered: June 2013
Karma: 0
|
Senior Member |
|
|
On Sat, 19 Mar 2011 14:05:56 -0700 (PDT), 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?
try asking in comp.databases.mysql
|
|
|
|
| Re: Confused about a MySQL Statement [message #173064 is a reply to message #173062] |
Sat, 19 March 2011 22:06 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 19-03-2011 22:11, richard wrote:
> On Sat, 19 Mar 2011 14:05:56 -0700 (PDT), 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?
>
> try asking in comp.databases.mysql
or, since you askjed in comp.lang.php
Store the 5 id's which you dont want to be resurned and loop over the
result from you first query, skipping the result if it matched any of
these 5.....
But a mysql solution might be quicker ;)
--
Luuk
|
|
|
|
| Re: Confused about a MySQL Statement [message #173065 is a reply to message #173061] |
Sat, 19 March 2011 22:15 |
Captain Paralytic
Messages: 204
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Mar 19, 9:05 pm, Joe <jruff...@gailborden.info> 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?
This is simple using a LEFT JOIN and a NULL test.
However I suggest that you start laying out your programming using a
neat formatting style or you will get nowhere.
|
|
|
|
| Re: Confused about a MySQL Statement [message #173072 is a reply to message #173061] |
Sun, 20 March 2011 16:58 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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
|
|
|
|
| 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: 0
|
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
]