CODEWITHSUNDEEP
mysqlsqljoinone-to-many
PoloRM
PoloRM

Reputation: 155

Building SQL query on a one-to-many relationship

I have a search page where I am trying to build a complex search condition on two tables which look something like:

Users
ID     NAME
1      Paul
2      Remy

...

Profiles
FK_USERS_ID   TOPIC      TOPIC ID
1             language   1
1             language   2
1             expertise  1
1             expertise  2
1             expertise  3
2             language   1
2             language   2

The second table Profiles, lists the "languages" or the "expertises" (among other stuff) of each user, and topic id is a foreign key to another table depending on the topic (if topic is "language", than topic ID is the ID of a language in the languages table, etc...).

The search needs to find something like where user name LIKE %PAU% and the user "has" language 1 and has language 2 and has expertise 1 and has expertise 2.

Any help would be really appreciated! I am performing a LEFT JOIN on the two tables although I am not sure that is the correct choice. My main problem lies on the "AND". The same user has to have both languages 1 and 2, and at the same time expertise 1 and 2.

I work in PHP and I usually try to avoid inner SELECTs and even joins, but I think an inner SELECT is imminent here?

Upvotes: 1

Views: 6572

Answers (4)

DRapp
DRapp

Reputation: 48169

I would do based on JOIN conditions multiple times against each condition that you are "requiring". I would also ensure an index on the Profiles table based on the each part of the key looking for... (FK_User_ID, Topic_ID, Topic)

SELECT STRAIGHT_JOIN
      U.ID 
   FROM Users U
      JOIN Profiles P1
         on U.ID = P1.FK_User_ID
         AND P1.Topic_Id = 1
         AND P1.Topic = "language"
      JOIN Profiles P2
         on U.ID = P2.FK_User_ID
         AND P2.Topic_Id = 2
         AND P2.Topic = "language"
      JOIN Profiles P3
         on U.ID = P3.FK_User_ID
         AND P3.Topic_Id = 1
         AND P3.Topic = "expertise"
      JOIN Profiles P4
         on U.ID = P4.FK_User_ID
         AND P4.Topic_Id = 2
         AND P4.Topic = "expertise"
   WHERE
      u.name like '%PAU%'

This way, any additional criteria as expressed in other answer provided shouldn't be too much an impact. The tables are setup by the criteria as if simultaneous, and if any are missing, they will be excluded from the result immediately instead of trying to do a sub-select counting for every entry (which I think might be the lag you are encountering).

So, each of your "required" criteria would take the same "JOIN" construct, and as you can see, I'm just incrementing the "alias" of the join instance.

Upvotes: 0

cairnz
cairnz

Reputation: 3957

You can accomplish this by building a set of users that matches the criterias from your profile tables, something like this:

SELECT FK_USERS_ID 
FROM Profiles 
WHERE topic='x' 
AND TOPIC_ID IN (1,2) 
GROUP BY FK_USERS_ID 
HAVING COUNT(1) = 2

Here you list your users that matches the topics you need. By grouping by the user id and specifying the amount of rows that should be returned, you can effectively say "only those that has x and y in topic z. Just make sure that the COUNT(1) = x has the same number of different TOPIC_IDs to look for.

You can then query the user table 

SELECT ID 
FROM Users 
WHERE name like '%PAU%' 
AND ID IN (<insert above query here>)

You can also do it in a join and a derived table, but the essence should be explained above.

EDIT: if you are looking for multiple combinations, you can use mysql's multi-column IN:

SELECT FK_USERS_ID 
FROM Profiles 
WHERE (topic,topic_id) IN (('x',3),('x',5),('y',3),('y',6))
GROUP BY FK_USERS_ID 
HAVING COUNT(1) = 4

This will look for uses matching the pairs x-3, x-5, y-3 and y-6.

You should be able to build the topic-topic_id pairs easily in php and stuffing it into the SQL string, and also just counting the number of pairs you generate into a variable for using for the count(1) number. See http://www.mysqlperformanceblog.com/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/ for performance talk using this approach.

Upvotes: 2

StevieG
StevieG

Reputation: 8719

select *
from users u, profiles p
where u.id = p.fk_users_id
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic = 'language' 
            and topic_id = 1)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic = 'language' 
            and topic_id = 22)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic = 'expertise' 
            and topic_id = 1)
and exists (select 1 
            from profiles 
            where fk_users_id = u.id 
            and topic = 'expertise' 
            and topic_id = 1)
and u.name like '%PAU%'

EDIT:

Ok, a slight variation on @cairnz' answer:

SELECT ID 
FROM Users 
WHERE name like '%PAU%' 
AND ID IN (SELECT FK_USERS_ID 
           FROM Profiles 
           WHERE topic='x' 
           AND ((TOPIC_ID = 1 AND TOPIC = 'language') 
                OR (TOPIC_ID = 2 AND TOPIC = 'language')
                OR (TOPIC_ID = 1 AND TOPIC = 'expertise')
                OR (TOPIC_ID = 2 AND TOPIC = 'expertise'))
           GROUP BY FK_USERS_ID 
           HAVING COUNT(1) = 4)

Upvotes: 0

Nedret Recep
Nedret Recep

Reputation: 728

Isn't it just a simple classical INNER JOIN?

SELECT
  p.topic, p.topic_id
FROM
  profiles p
INNER JOIN
  users u
ON
  u.id = p.fk_users_id
WHERE
  u.name LIKE '%Paul%'

This query would return all the languages and expertise with their IDs for the users matching the pattern, in this case containing Paul in their name. Is this what you like? Or something else?

Upvotes: 1

Related Questions