CODEWITHSUNDEEP
sql
user2800679
user2800679

Reputation:

How to do left join without select

I have two tables, "user", and "user_things". I want to get all users with one or more things, but I don't want to retrieve the things themselves (I only want one row per user returned).

Table 1:
id
username

Table 2:
id
userid
thingname

Example: I want to find all users with a "hat" and a "car". If there are two users with this, I want only two rows returned (not 4).

Upvotes: 1

Views: 4486

Answers (3)

Jose Rui Santos
Jose Rui Santos

Reputation: 15319

A simpler solution is

select user.id, user.name
  from user
 inner join things t on t.userid = user.id
 where t.thingname in ('car', 'hat')
 group by user.id, user.name
having count(*) >= 2; -- (2 for 'car' and 'hat', 3 for 'car', 'hat' and 'bike', ...)

SQL Fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Use aggregation:

select u.userid, u.username
from user u join
     user_things ut
     on ut.userid = u.id
group by t1.userid, t1.username
having sum(case when ut.thingname = 'hat' then 1 else 0 end) > 0 and
       sum(case when ut.thingname = 'car' then 1 else 0 end) > 0

The first part of the having clause counts the number of "hat"s. The second counts the number of "car"s. The > condition requires that both are present.

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116140

Select all users for which a record for 'car' and for 'hat' exists in the other table.

select
  *
from
  User u
where
  exists (
    select 'x' 
    from Things t 
    where t.userid = u.id and t.thingname = 'hat') and
  exists (
    select 'x' 
    from Things t 
    where t.userid = u.id and t.thingname = 'car')

Alternatively, you can do this, although I think it's less nice, less semantically correct:

select distinct
  u.*
from
  Users u
  inner join Things tc on tc.userid = u.id and tc.thingname = 'car'
  inner join Things th on th.userid = u.id and th.thingname = 'hat'

Or even:

select
  u.*
from
  Users u
where
  (select 
    count('x') 
  from Things t
  where t.userid = u.id and t.thingname in ('car', 'hat')) = 2

Although the last one might also return users that have no car and two hats.

Upvotes: 0

Related Questions