Author |
Message |
Hertzsprung
WebHelper
Joined: 30 Jul 2002
Posts: 56
Location: UK
|
Posted:
Mon Mar 17, 2003 2:41 pm (21 years, 1 month ago) |
|
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? |
|
|
|
|
adam
Forum Moderator & Developer
Joined: 26 Jul 2002
Posts: 704
Location: UK
|
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 |
________________________________ It's turtles all the way down... |
|
|
|
drathbun
WebHelper
Joined: 01 Mar 2003
Posts: 69
Location: Texas
|
Posted:
Tue Mar 18, 2003 3:17 am (21 years, 1 month ago) |
|
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 |
|
|
|
Hertzsprung
WebHelper
Joined: 30 Jul 2002
Posts: 56
Location: UK
|
Posted:
Tue Mar 18, 2003 8:44 am (21 years, 1 month ago) |
|
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 |
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 |
|
|
|
|
Hertzsprung
WebHelper
Joined: 30 Jul 2002
Posts: 56
Location: UK
|
Posted:
Tue Mar 18, 2003 8:45 am (21 years, 1 month ago) |
|
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 |
|
|
|
|
Hertzsprung
WebHelper
Joined: 30 Jul 2002
Posts: 56
Location: UK
|
Posted:
Fri Mar 21, 2003 8:18 am (21 years, 1 month ago) |
|
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 |
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? |
|
|
|
|
adam
Forum Moderator & Developer
Joined: 26 Jul 2002
Posts: 704
Location: UK
|
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. |
________________________________ It's turtles all the way down... |
|
|
|
|
Page generation time: 0.135235 seconds :: 18 queries executed :: All Times are GMT
Powered by
phpBB 2.0
© 2001, 2002 phpBB Group :: Based on an FI Theme