[thelist] MySQL: Problem with JOIN query
Vijay Immanuel
lists at vjdom.com
Sat Feb 28 12:57:59 CST 2004
I'm not really a SQL expert, but did you try this:
SELECT u.user_id, u.first_name FROM users AS u LEFT JOIN user_interest_assoc
AS uia on uia.user_id = u.user_id WHERE uia.interest_id = '1' AND
uia.interest_id = '2' AND uia.user_id IS NOT NULL
Hope it works. I haven't tested this query, though.
VJ
--
"We will find a way, or we will make one."
- Hannibal
----- Original Message -----
From: "Alexis Fellenius" <alexis at winstondesign.se>
Hi.
I'm having a problem with an MySQL-query. (table structure at the
bottom of this mail)
My goal is to select users based on their interests.
But I just can't get it to work!
Example:
I want to select the user who has chosen BOTH computers
(interest_id = 1) AND music (interest_id = 2)
e.g
The query should result in getting user 'Alexis' (user_id = 2)
I have tried using
SELECT U.* FROM users AS U
LEFT JOIN user_interest_assoc AS A ON A.user_id = U.user_id
WHERE A.interest_id = 1 AND A.interest_id = 2
GROUP BY U.user_id
But it doesn't return any rows.
Can someone point out my errors? Is it even possible to do this with
one query?
(The query doesn't return any errors so i guess it's a logical error in
the SQL but being new to all this my eyes is bleeding of all the
looking-straight-at-the-screen-hoping-it-will-solve-it-self hours I've
spent with this)
In other words...
I would be extremely grateful for any tip..
=)
Example table structure: (some columns excluded for simplicity)
users
+---------+-------------+
| user_id | first_name |
+---------+-------------+
| 1 | Joel |
+---------+-------------+
| 2 | Alexis |
+---------+-------------+
| 3 | Eric |
+---------+-------------+
interests:
+-------------+-------------+
| interest_id | interest |
+-------------+-------------+
| 1 | Computers |
+-------------+-------------+
| 2 | Music |
+-------------+-------------+
| 3 | Films |
+-------------+-------------+
| 4 | Finger painting |
+-------------+-------------+
interest_user_assoc (primary key excluded)
+-------------+-------------+
| interest_id | user_id |
+-------------+-------------+
| 1 | 2 |
+-------------+-------------+
| 2 | 2 |
+-------------+-------------+
| 2 | 1 |
+-------------+-------------+
| 4 | 3 |
+-------------+-------------+
/alexis
--
Alexander Fellenius, Winston Design
Adress: Högalidsgatan 36, SE-117 30 Sthlm
Tel: +46 (0)8-464 70 46 > Mobil: +46 (0)704-38 12 83
Webb: http://www.winstondesign.se
--
* * Please support the community that supports you. * *
http://evolt.org/help_support_evolt/
For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !
More information about the thelist
mailing list