CODEWITHSUNDEEP
sqljoinwhere-clause
user3016917
user3016917

Reputation: 9

SQL JOIN WITH TWO WHERE CLAUSES

I would like to implement the following SQL query : suppose using JOIN clause, due to now it's running quite slow:

SELECT ID_USER, NICK
FROM TABLE1 
WHERE ID_USER IN 
(
    SELECT ID_INDEX1 
    FROM TABLE2 
    WHERE ID_INDEX2 = '2'
)
AND ID_USER NOT IN 
(
    SELECT ID_INDEX2 
    FROM TABLE2 
    WHERE ID_INDEX1 = '2' AND GO ='NO'
)
ORDER BY NICK ASC

Upvotes: 0

Views: 153

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30845

Assuming that you want to filter by ID_INDEX1 in both cases (see my comment on your question), you can:

  • count the number of rows per user in table2 with value = 2
  • count the number of rows per user in table2 with value = 2 and go = 'NO'
  • return only those for which the first count is greater than 0 and the second count equals 0

i.e.:

select * from (
  select 
    id_user,
    nick,
    sum(case when table2.id_index2 = '2' then 1 else 0 end) as count2_overall,
    sum(case when table2.id_index2 = '2' and go = 'NO' then 1 else 0 end) as count2_no
  from table1
  join table2 on table1.id_user = table2.id_index1
  group by id_user, nick
) 
where count2_overall > 0 and count2_no = 0

Upvotes: 0

Amit
Amit

Reputation: 46361

You could do the "including" part with INNER JOIN and the "excluding" part with a "LEFT JOIN" + filtering:

SELECT DISTINCT t1.ID_USER, t1.NICK
FROM TABLE1 t1
  INNER JOIN TABLE2 t2IN
    ON t1.ID_USER = t2IN.ID_INDEX1
    AND t2IN.ID_INDEX2 = '2'
  LEFT JOIN TABLE2 t2OUT
    ON t1.ID_USER = t2OUT.ID_INDEX2
    AND t2OUT.ID_INDEX1 = '2'
    AND t2OUT.GO = 'NO'
WHERE t2OUT.ID_INDEX IS NULL
ORDER BY t1.NICK ASC

Upvotes: 1

Related Questions