|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
March 11th, 2004, 07:58 PM
|
||||
|
||||
|
MySQL joining same table
I've been stumped for a week now... and I'm pretty sure I've frustrated all my friends...
The easiest way to explain this problem is by example... Basically I have a bridging table linking members and events... I want to find which events that two specific members attended together... The table looks like this: userid, eventid Oracle has simple nesting query techniques, and things like union, join and intersect... However I'm struggling to find a way to do this with a MySQL query... Any suggestions?
|
|
#2
March 12th, 2004, 12:18 AM
|
||||
|
||||
|
Would something like "GROUP BY eventid" help?
|
|
#3
March 12th, 2004, 09:58 AM
|
||||
|
||||
|
I've tried various GROUP BY, but it still shows events that each member attended on their own... I'm looking specifically for only the ones they both attended...
I've tried LEFT JOINs and RIGHT JOINs... but the fact that its from the same table is confusing me... I keep comparing it to Oracle, because this could have been done something similar: Code:
SELECT eventid FROM user_event WHERE userid=1 AND eventid IN (SELECT eventid FROM user_event where usedid=2) I can't figure out the MySQL equavelent
|
|
#4
March 12th, 2004, 10:05 AM
|
||||
|
||||
|
I don't know much of SQL, only the basics, but isn't there just a simple way of doing this?
Code:
SELECT eventid FROM user_event WHERE userid=1 AND userid=2 Dunno for sure, but this is something I would try. I don't understand the JOINs 
__________________
Work to live, don't live to work
|
|
#5
March 12th, 2004, 12:38 PM
|
||||
|
||||
|
Spongy,
how could one id be equal to two values? =) I've tried using OR... (where id=1 or id=2) However this returns all the events that both users attended, including ones that only one person went alone... I need an intersect somehow, but can't figure out how MySQL handles this...
|
|
#6
March 12th, 2004, 01:18 PM
|
||||
|
||||
|
Hmmm, I guess you could do something like this:
Code:
select eventid, userid from user_event where eventid=1 AND userid in(1,2); and figure it's a valid result only if the row count is greater than one.
__________________
Please don't PM me asking for solutions outside the scope of a thread. Keeping all responses in a thread stands to help others who come along later, which is after all what this forum's all about.
|
|
#7
March 12th, 2004, 01:26 PM
|
||||
|
||||
|
dhouston, does that only work for one specific event?
how would I get all the events that the two people share? [i'll try the actual query when i get home from work]
|
|
#8
March 12th, 2004, 01:35 PM
|
||||
|
||||
|
Scrap that. I was assuming you were checking for a given event. Try this:
Code:
mysql> describe user_event; +---------+---------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------------+------+-----+---------+-------+ | userid | int(10) unsigned zerofill | YES | | NULL | | | eventid | int(10) unsigned zerofill | YES | | NULL | | +---------+---------------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from user_event; +------------+------------+ | userid | eventid | +------------+------------+ | 0000000003 | 0000000001 | | 0000000002 | 0000000001 | | 0000000001 | 0000000001 | | 0000000001 | 0000000002 | +------------+------------+ 4 rows in set (0.00 sec) mysql> select eventid, count(userid) as cnt from user_event where userid in(1,2) group by eventid; +------------+-----+ | eventid | cnt | +------------+-----+ | 0000000001 | 2 | | 0000000002 | 1 | +------------+-----+ 2 rows in set (0.00 sec) You still have to verify that the row count for a given event is greater than one. Maybe this'll get you going in the right direction, at least.
|
|
#9
March 12th, 2004, 11:02 PM
|
||||
|
||||
|
Wow dhouston... that's awesome!
I never would have thought to have done it this way. I see the light! =) Curious, how come i can't use cnt in a where clause? For example, where cnt=2... similarly, although i somewhat understand, I can't use where count(userid)=2 However, if i were to use as for a table, i'd have to refer to it as the designated name
|
|
#10
March 15th, 2004, 08:18 AM
|
||||
|
||||
|
My guess is that you can't use cnt in the where clause because it can't be assembled until after the result set is in. SInce it's an aggregate function whose results are contingent upon parsing all the data otherwise selected for, it can't very well be used in the where clause before it's calculated. Not sure about that, but it makes sense to me.
Incidentally, if you're wanting to get really fancy with this, you can stick the user ids in an array, join the array with commas to stick in the IN clause, and then use sizeof() your array to verify that only events that all listed participants are signed up for will be displayed. This would allow for dynamic selection (without hard-coding numbers), a good thing for code maintainability.
|
|
#11
March 15th, 2004, 08:22 AM
|
||||
|
||||
|
To clarify my last statement, it doesn't appear that you could do this in a where clause (based on your last question). But you could compare sizeof() to the count and only handle elegible records.
|
|
#12
March 17th, 2004, 12:47 PM
|
||||
|
||||
|
I guess what you're saying about cnt being assembled after the result set is in makes sense... i suppose it could make a double-pass over the result set, but that would increase the load on the server for larger databases... for now i'm handling that much in my pgoramming language [if (cnt=3)]
Similarly, I already build my query dynamically, joining the ids I wish to search, and testing IF(cnt=sizeof(id_Array))... similar to what you've mentioned... It would just be nice if MySQL would have handled all that for me and ONLY returned the values where cnt is 3... none-the-less, it works... a much appreciated query solution! =)
|
|
#13
March 17th, 2004, 01:07 PM
|
||||
|
||||
|
There could be a way to do this that I simply don't know. I'm kind of a hack and slash query writer who comes up with things that work, even if they're not the optimal solution.
 Glad I could help out in this case.
|
|
#14
April 6th, 2005, 06:09 AM
|
|||
|
|||
Quote:
You can access expressions in the select by using the HAVING clause insted of WHERE. HAVING evalulates after the SELECT part is parsed.
|
|
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > MySQL joining same table |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
