CODEWITHSUNDEEP
sqldatabase-designjoin
DeiForm
DeiForm

Reputation: 664

SQL join tables with condition

So I would like to join 2 tables with 1 where condition:

Here is what I've tried so far:

SELECT * FROM (ci_usertags) 
JOIN ci_tags ON ci_usertags.utag_tid
WHERE utag_uid = 1

My tables look like this:

ci_usertags 
  utag_id 
  utag_uid
  utag_tid

ci_tags 
  tag_id
  tag_name

I want to select all tags for user with ID 1 and get the tag name. With this SQL I am getting duplicate results and even the tags which are not for the user with ID 1

Upvotes: 0

Views: 72

Answers (3)

user2130988
user2130988

Reputation: 63

Solution with nested query as replacement for join (speed improvement)

Select tag_id, tag_name
FROM ci_tags
WHERE tag_id IN (Select utag_tid FROM ci_usertags WHERE utag_id = 1 GROUP BY 1)
GROUP BY 1,2

Upvotes: 2

Michael
Michael

Reputation: 16142

replace:

SELECT *
FROM (`ci_usertags`) JOIN
     `ci_tags`
     ON `ci_usertags`.`utag_tid`
WHERE `utag_uid`

on:

SELECT *
FROM `ci_usertags` ut JOIN
     `ci_tags` t
     ON ut.`utag_tid` = t.tag_id
WHERE `utag_uid` = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270703

Your query is:

SELECT *
FROM (`ci_usertags`) JOIN
     `ci_tags`
     ON `ci_usertags`.`utag_tid`
WHERE `utag_uid`

This is sort of non-sensical SQL. The conditions are that utag_tid is not 0 or NULL and utag_uid is not 0 or NULL. This is because there are no conditions, so it is only looking at the value of a variable. In some other SQL engines, this would result in errors.

I think you want something like:

SELECT *
FROM `ci_usertags` ut JOIN
     `ci_tags` t
     ON ut.`utag_tid` = t.tag_id
WHERE `utag_uid` = 1

Upvotes: 3

Related Questions