FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » Confused about a MySQL Statement
Show: Today's Messages :: Polls :: Message Navigator
Switch to threaded view of this topic Create a new topic Submit Reply
Confused about a MySQL Statement [message #173061] Sat, 19 March 2011 21:05 Go to next message
Joe is currently offline  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 Go to previous messageGo to next message
richard is currently offline  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 Go to previous messageGo to next message
Luuk is currently offline  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 Go to previous messageGo to next message
Captain Paralytic is currently offline  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 Go to previous messageGo to next message
Denis McMahon is currently offline  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 Go to previous message
Jerry Stuckle is currently offline  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
==================
  Switch to threaded view of this topic Create a new topic Submit Reply
Previous Topic: $_POST not set on 404
Next Topic: Stats comp.lang.php (last 7 days)
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 10 16:06:14 GMT 2024

Total time taken to generate the page: 0.02912 seconds