CODEWITHSUNDEEP
mysqlselectjoin
André Figueira
André Figueira

Reputation: 6726

Match results in multiple tables MYSQL

Hi I have a query that is giving me a few problems and it was suggested I ask a separate question about the end result rather than the problem.

So I have three tables and some user input.

the tables are:

each table has a related ID the users table has ID and on the other two they have userID to match skills and languages to users

the user input is dynamic but for example it can be 1 for usersLanguages and 2 for usersSkills

The user input is taken from a form and what i need to do is match get the results of users depending on the language IDs or skill ids passed through. for example i can pass two user ids and three language ID's.

SELECT DISTINCT users.ID, users.name
FROM users 
INNER JOIN usersSkills 
ON users.ID = usersSkills.userID 
INNER JOIN usersLanguages ON users.ID = usersLanguages.userID 
WHERE activated = "1" 
      AND type = "GRADUATE" AND usersSkills.skillID IN(2) 
      AND usersLanguages.languageID IN(2)
GROUP BY usersSkills.userID HAVING COUNT(*) = 1,
         usersLanguages.userID HAVING COUNT(*) = 1

Upvotes: 0

Views: 440

Answers (3)

Moyed Ansari
Moyed Ansari

Reputation: 8461

try this 

SELECT users.ID,user.name
FROM users 
INNER JOIN usersSkills ON users.ID = usersSkills.userId
INNER JOIN AND usersLanguages ON users.ID = usersLanguages.userID
WHERE activate = '1'
AND type = 'GRADUATE'
AND usersSkill.skillID IN (2)
AND usersLanguages.languageID IN (2)
GROUP BY users.ID

Upvotes: 1

Johan
Johan

Reputation: 76693

You do not mix group by and having clauses.
having is not a part of group by, in fact you can have having without a group by, in which case it will work as a more capable (and slower) where clause.

SELECT u.ID, u.name
FROM users u 
INNER JOIN usersSkills us ON u.ID = us.userID 
INNER JOIN al ON u.ID = ul.userID 
WHERE u.activated = '1' 
      AND u.type LIKE 'GRADUATE' AND us.skillID IN('2') 
      AND ul.languageID IN('2')
GROUP BY u.ID
HAVING COUNT(*) = 1
  • because of the join criteria, ul.userID = us.userID = u.ID so it makes no sense to group by both. Just group by u.id, because that's the ID you select after all.
  • u.name is functionally dependent on ID, so that does not need to be listed (*).
  • When doing a test like u.type = 'GRADUATE', I prefer to do u.type LIKE 'GRADUATE' because LIKE is case insensitive and = may not be depending on the collation, but that's just me.
  • Distinct is not needed; group by already makes the results distinct.

Having works on the resultset up to and including group by, so in this case you need only one having clause. If you want to have more, you need to treat them the same as a where clause:

having count(*) = 1 AND SUM(money) > 10000

(*) this is only true in MySQL, in other SQL's you need to list all non-aggregated selected columns in your group by clause. Whether they are functionally dependent on the group by column or not. This slows things down and makes for very long query-syntax, but it does prevent a few beginners errors.
Personally I like the MySQL way of doing things.

Upvotes: 1

smp7d
smp7d

Reputation: 5032

something like 

SELECT * from users left join usersLanguage on users.id=usersLanguage.userID 
left join usersSkills on usersSkills.userID=users.id 
where usersLanguage.id in (1, 2, 3) and usersSkills.id in (1, 2, 3)
GROUP BY users.id

will probably work

Upvotes: 1

Related Questions