Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Geek Culture / MySQL subquery question

Author
Message
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 7th Oct 2004 05:41 Edited at: 7th Oct 2004 05:41
Link
Hi,

I have this MySQL Query



Basically I want to find all rows which are in class 11 but not class 4. I do a simple select then put



And use the subquery in brackets.

However, I get told the bit in brackets is an SQL syntax error.

Any ideas? (Rich? )

Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
Jimmy
21
Years of Service
User Offline
Joined: 20th Aug 2003
Location: Back in the USA
Posted: 7th Oct 2004 05:51
Link
Wait.. what are you trying to do?


Remember, Jimmy still loves you.
Back to top
Profile PM Website
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 7th Oct 2004 05:54
Link
I have a BIG table like this:

mem_id
pupil_id
class_id

connecting pupils to classes.

I need to get a query that returns a list of all pupils who are IN class 1 (ie class_id = 11) but NOT IN class 4 (ie class_id = 4).

Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
Jimmy
21
Years of Service
User Offline
Joined: 20th Aug 2003
Location: Back in the USA
Posted: 7th Oct 2004 06:03 Edited at: 7th Oct 2004 06:04
Link


?? dunno for sure


Remember, Jimmy still loves you.
Back to top
Profile PM Website
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 7th Oct 2004 06:05
Link
Looks right, but I get this:





Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
Jimmy
21
Years of Service
User Offline
Joined: 20th Aug 2003
Location: Back in the USA
Posted: 7th Oct 2004 06:22
Link
Looks like MySQL doesn't support subqueries until 4.1, you got that?


Remember, Jimmy still loves you.
Back to top
Profile PM Website
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 7th Oct 2004 06:24
Link
Ah, maybe not

Ah well - I'll stick to my query intensive method

Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
BatVink
Moderator
21
Years of Service
User Offline
Joined: 4th Apr 2003
Location: Gods own County, UK
Posted: 7th Oct 2004 06:49
Link
definitely no subqueries in older versions of MySQL. You'd have to do some fancy JOIN with itself, but I can't get my head around it at the moment.

BatVink
http://biglaugh.co.uk/catalog AMD 3000+ Barton, 512Mb Ram, 120 Gig Drive space, GeForce 5200 FX 128 Mb, Asus A7N8X Mobo.
Terms & Conditions apply
Back to top
Profile PM Email
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 7th Oct 2004 07:52
Link
I orignially has some fantastically complex left join, which involved joining a table to itself

No prizes for guessing whether it worked or not

I now get the pupils in one class into an array, then sort through them seeing if they're in the second, then do a foreach to insert them again. Not clever but it works.

Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
Phaelax
DBPro Master
21
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 7th Oct 2004 08:41
Link
Well your subquery is going to return only records where class_id IS 4. And then the other query is going to return all records that have class_id equals 11, but there won't be anything to return because it's only searching in records where the class_id is 4.
So what you're trying do is find all pupils in class 11 that are not in class 4?

I use SQL, not mySQL. Syntax looks corrrect as far as I know. You could try creating a view to replace the sub-query.

"eureka" - Archimedes
Back to top
Profile PM Email Website
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 7th Oct 2004 17:52
Link
Yeah - my original query was quite bust.

My problem lies with the fact that my version doesn't support subqueries. I'm just going to take the long way around.

Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
Karlos
21
Years of Service
User Offline
Joined: 18th Nov 2002
Location: United Kingdom
Posted: 8th Oct 2004 00:14
Link
I got a bit muddled but this appears to work:



All Hail the Glory of the HypnoToad
XP Pro - Radeon 9000 Mobility- P4 3.0ish
Football management - Football Manager
Back to top
Profile PM Website
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 8th Oct 2004 02:12 Edited at: 9th Oct 2004 18:24
Link
Sorry - I get over 150 records found here

I'ved changed the class ids around a bit - class 4 contains 20 students, and class 18 contains 8 of the students from class 4. Substituting in the numbers into the query still gave over 150 results. I get where you're coming from though - and it's a good idea.

Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
Phaelax
DBPro Master
21
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 9th Oct 2004 18:23
Link
Are those 150 records mostly duplicates? If so, then use DISTINCT in the SELECT statement.

This may work:



"eureka" - Archimedes
Back to top
Profile PM Email Website
Back to top

Login to post a reply

Server time is: 2024-09-22 18:29:21
Your offset time is: 2024-09-22 18:29:21