Author |
Message |
Hertzsprung
WebHelper
![WebHelper WebHelper](images/rank_2.gif)
Joined: 30 Jul 2002
Posts: 56
Location: UK
|
Posted:
Mon Mar 17, 2003 2:41 pm (21 years, 1 month ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
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? |
|
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Hertzsprung Find all posts by Hertzsprung](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
adam
Forum Moderator & Developer
![](images/rank_6.gif)
Joined: 26 Jul 2002
Posts: 704
Location: UK
|
Posted:
Mon Mar 17, 2003 5:04 pm (21 years, 1 month ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
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](/forums/images/smilies/icon_biggrin.gif) |
________________________________ It's turtles all the way down... |
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by adam Find all posts by adam](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
drathbun
WebHelper
![WebHelper WebHelper](images/rank_2.gif)
Joined: 01 Mar 2003
Posts: 69
Location: Texas
|
Posted:
Tue Mar 18, 2003 3:17 am (21 years, 1 month ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
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.
Dave |
________________________________ Dave
Photography Site :: Query Tools Forum :: Weekend Fun |
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by drathbun Find all posts by drathbun](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
Hertzsprung
WebHelper
![WebHelper WebHelper](images/rank_2.gif)
Joined: 30 Jul 2002
Posts: 56
Location: UK
|
Posted:
Tue Mar 18, 2003 8:44 am (21 years, 1 month ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
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](/forums/images/smilies/icon_biggrin.gif) |
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](/forums/images/smilies/icon_rolleyes.gif) |
|
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Hertzsprung Find all posts by Hertzsprung](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
Hertzsprung
WebHelper
![WebHelper WebHelper](images/rank_2.gif)
Joined: 30 Jul 2002
Posts: 56
Location: UK
|
Posted:
Tue Mar 18, 2003 8:45 am (21 years, 1 month ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
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.
Dave |
I must admit I'm very lucky to be using postgreSQL myself -- for a mere £5 a year ![Very Happy](/forums/images/smilies/icon_biggrin.gif) |
|
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Hertzsprung Find all posts by Hertzsprung](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
Hertzsprung
WebHelper
![WebHelper WebHelper](images/rank_2.gif)
Joined: 30 Jul 2002
Posts: 56
Location: UK
|
Posted:
Fri Mar 21, 2003 8:18 am (21 years, 1 month ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
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](/forums/images/smilies/icon_biggrin.gif) |
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? |
|
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Hertzsprung Find all posts by Hertzsprung](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
adam
Forum Moderator & Developer
![](images/rank_6.gif)
Joined: 26 Jul 2002
Posts: 704
Location: UK
|
Posted:
Fri Mar 21, 2003 4:56 pm (21 years, 1 month ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
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... |
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by adam Find all posts by adam](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
|
Page generation time: 0.158304 seconds :: 18 queries executed :: All Times are GMT
Powered by
phpBB 2.0
© 2001, 2002 phpBB Group :: Based on an FI Theme