FUDforum: comp.lang.php » Confused about a MySQL Statement

Home » Imported messages » comp.lang.php » Confused about a MySQL Statement

Show: Today's Messages :: Polls :: Message Navigator

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:

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

Report message to a moderator

[Message index]

		Confused about a MySQL Statement By: Joe on Sat, 19 March 2011 21:05
		Re: Confused about a MySQL Statement By: richard on Sat, 19 March 2011 21:11
		Re: Confused about a MySQL Statement By: Luuk on Sat, 19 March 2011 22:06
		Re: Confused about a MySQL Statement By: Captain Paralytic on Sat, 19 March 2011 22:15
		Re: Confused about a MySQL Statement By: Denis McMahon on Sun, 20 March 2011 16:58
		Re: Confused about a MySQL Statement By: Jerry Stuckle on Sun, 20 March 2011 17:10

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) ] [

]

Current Time: Tue Nov 26 10:39:34 GMT 2024

Total time taken to generate the page: 0.04744 seconds