4WebHelp
 FAQ  •  Search  •  User Groups  •  Forum Admins  •  Smilies List  •  Statistics  •  Rules   •  Login   •  Register
Toggle Navigation Menu

 Nested SELECTs
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic
Author Message
Hertzsprung
WebHelper
WebHelper


Joined: 30 Jul 2002
Posts: 56
Location: UK

PostPosted: Mon Mar 17, 2003 2:41 pm (14 years, 9 months ago) Reply with QuoteBack to Top

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:

+---------------------+
| profile             |    +---------+
+---------------------+    | groups  |
| userid              |    +---------+
| groupid =================> id (PK) |
| PK(userid, groupid) |    | name    |
+---------------------+    +---------+


AFAIK, this cannot be achieved in MySQL (which sucks, btw). Can anyone suggest an alternative?
OfflineView User's ProfileFind all posts by HertzsprungSend Personal MessageVisit Poster's Website
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Mon Mar 17, 2003 5:04 pm (14 years, 9 months ago) Reply with QuoteBack to Top

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

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
drathbun
WebHelper
WebHelper


Joined: 01 Mar 2003
Posts: 69
Location: Texas

PostPosted: Tue Mar 18, 2003 3:17 am (14 years, 9 months ago) Reply with QuoteBack to Top

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

________________________________
Dave
Photography Site :: Query Tools Forum :: Weekend Fun
OfflineView User's ProfileFind all posts by drathbunSend Personal MessageVisit Poster's Website
Hertzsprung
WebHelper
WebHelper


Joined: 30 Jul 2002
Posts: 56
Location: UK

PostPosted: Tue Mar 18, 2003 8:44 am (14 years, 9 months ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by HertzsprungSend Personal MessageVisit Poster's Website
Hertzsprung
WebHelper
WebHelper


Joined: 30 Jul 2002
Posts: 56
Location: UK

PostPosted: Tue Mar 18, 2003 8:45 am (14 years, 9 months ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by HertzsprungSend Personal MessageVisit Poster's Website
Hertzsprung
WebHelper
WebHelper


Joined: 30 Jul 2002
Posts: 56
Location: UK

PostPosted: Fri Mar 21, 2003 8:18 am (14 years, 9 months ago) Reply with QuoteBack to Top

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?
OfflineView User's ProfileFind all posts by HertzsprungSend Personal MessageVisit Poster's Website
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Fri Mar 21, 2003 4:56 pm (14 years, 9 months ago) Reply with QuoteBack to Top

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.

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
Display posts from previous:      
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic


 Jump to:   




You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot edit your posts in this forum.
You cannot delete your posts in this forum.
You cannot vote in polls in this forum.


Page generation time: 0.053718 seconds :: 18 queries executed :: All Times are GMT
Powered by phpBB 2.0 © 2001, 2002 phpBB Group :: Based on an FI Theme