[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