Nested SELECTs

WebHelper Joined: 30 Jul 2002 Posts: 56 Location: UK

In postgres, you could do this:

SELECT name FROM groups WHERE id IN (SELECT groupid FROM profile WHERE userid=123)

with a some tables looking like this:

Code:

AFAIK, this cannot be achieved in MySQL (which sucks, btw). Can anyone suggest an alternative?

Posted: Mon Mar 17, 2003 5:04 pm (21 years, 1 month ago)

SELECT groups.name FROM groups LEFT JOIN profile ON groups.id=profile.groupid WHERE profile.userid=123

I believe that should take care of it Very Happy

WebHelper Joined: 01 Mar 2003 Posts: 69 Location: Texas

The way they take care of it in phpBB is to run the initial select (the sub-query) and build a string. That string becomes part of the where clause for the next query.

I agree, it's amazing that MySQL has come as far as it has without this basic structure. Rolling Eyes

Dave

WebHelper Joined: 30 Jul 2002 Posts: 56 Location: UK

adam wrote:

SELECT groups.name FROM groups LEFT JOIN profile ON groups.id=profile.groupid WHERE profile.userid=123

I believe that should take care of it Very Happy

Thanks, I'll give it a go. If it works, I'll have to find a way of kludging it into my DB
abstraction layer Rolling Eyes

WebHelper Joined: 30 Jul 2002 Posts: 56 Location: UK

drathbun wrote:

The way they take care of it in phpBB is to run the initial select (the sub-query) and build a string. That string becomes part of the where clause for the next query.

I agree, it's amazing that MySQL has come as far as it has without this basic structure. Rolling Eyes

Dave

I must admit I'm very lucky to be using postgreSQL myself -- for a mere £5 a year Very Happy

WebHelper Joined: 30 Jul 2002 Posts: 56 Location: UK

adam wrote:

SELECT groups.name FROM groups LEFT JOIN profile ON groups.id=profile.groupid WHERE profile.userid=123

I believe that should take care of it Very Happy

Well, that worked. (I used INNER JOIN in the end but I dont think it makes much difference in my circumstance).

Now I'm trying to retreieve all the rows from group that are /not/ in profile WHERE profile.userid=123.
I thought this might work:

Code:

SELECT groups.name FROM groups INNER JOIN profile ON groups.id!=profile.groupid WHERE profile.userid=123

But it doesn't work at all.

Any ideas?

Posted: Fri Mar 21, 2003 4:56 pm (21 years, 1 month ago)

hmm...thats a tough one. the easiest way (and the only one I can think of) would be to select *, then use php (or the scripting language of your choice) to check through the results and store whichever != 123.