Select Values That Meet Different Conditions on Different Rows

Select values that meet different conditions on different rows?

SELECT userid
FROM UserRole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;

---

To anyone reading this: my answer is simple and straightforward, and got the 'accepted' status, but please do go read the answer given by @cletus. It has much better performance.

---

Justing thinking out loud, another way to write the self-join described by @cletus is:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid
JOIN userrole t3 ON t2.userid = t3.userid
WHERE (t1.roleid, t2.roleid, t3.roleid) = (1, 2, 3);

This might be easier to read for you, and MySQL supports comparisons of tuples like that. MySQL also knows how to utilize covering indexes intelligently for this query. Just run it through EXPLAIN and see "Using index" in the notes for all three tables, which means it's reading the index and doesn't even have to touch the data rows.

I ran this query over 2.1 million rows (the Stack Overflow July data dump for PostTags) using MySQL 5.1.48 on my Macbook, and it returned the result in 1.08 sec. On a decent server with enough memory allocated to innodb_buffer_pool_size, it should be even faster.

Select rows that matches multiple and/or conditions

You can join the 3 tables, group by product and set the conditions in the HAVING clause:

SELECT p.id, p.name
FROM products p 
INNER JOIN product_tags_link pt ON pt.product_id = p.id
INNER JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.name
HAVING SUM(t.tag = 'color' AND t.value IN ('green', 'red')) > 0
   AND SUM(t.tag = 'pet' AND t.value IN ('dog')) > 0

See the demo.

SQL Select results based on two rows with different condition

To compare the same value in a different row a self join is used. In this case There are two Test1 tables, the first (Tc) matches the C criteria, and the second Td matches the D criteria.

SELECT Result,Tc.B
FROM Test1 Tc
JOIN Test1 Td
  ON Tc.B=Td.B
JOIN Test2
  ON Td.B=Test2.Id
WHERE
   Tc.C = 'x'
   AND Td.D IS NOT NULL

CASE WHEN - Multiple Conditions - Over Multiple Rows of same reference

You need a window function (an aggregation function with an OVER clause) to look at multiple rows at once.

SELECT 
  "o/n", sku, order_type, state,
  CASE
    WHEN order_type <> 'Grouped' THEN
      'single_state'
    WHEN COUNT(DISTINCT state) OVER (PARTITION BY "o/n", order_type) = 1 THEN
      'single_state'
    ELSE
      'multi_state'
  END
FROM data.table
ORDER BY "o/n", sku;

SQL Server : select values from multiple rows when one row met condition

Exists is a better way to do it, but you can do it with self join also like below

Resterster sample

select t1.*
    from products t1
inner join 
    products t2
on 
    t1.product_number=t2.product_number
    and t1.made_by<>t2.made_by
    and t1.model<>0
    and t2.model=0; 

Sample Data

select * from products;

+-----+---------+----------------+-------+
| id  | made_by | product_number | model |
+-----+---------+----------------+-------+
| 122 | U123    |          12345 |     0 |
| 123 | U123    |          12345 |     1 |
| 124 | U552    |          12345 |     2 |
| 125 | U5      |          12346 |     0 |
| 126 | U5      |          12346 |     1 |
| 127 | U6      |          12347 |     0 |
| 128 | U7      |          12347 |     1 |
+-----+---------+----------------+-------+

Output

+-----+---------+----------------+-------+
| id  | made_by | product_number | model |
+-----+---------+----------------+-------+
| 124 | U552    |          12345 |     2 |
| 128 | U7      |          12347 |     1 |
+-----+---------+----------------+-------+  

SQL for applying conditions to multiple rows in a join

You'll want to join the tags table again.

SELECT * FROM users
INNER JOIN tags as t1 on t1.user_id = users.id and t1.name='tag1'
INNER JOIN tags as t2 on t2.user_id = users.id and t2.name='tag2'

Update all rows with different values. When where conditions differ

that would be like this by using case statement:

UPDATE TeamID
SET TmID = case Tm when 'NYY' then 1
                   when 'BOS' then 2
                   when 'LAA' then 3
                   when 'SEA' then 4
                   when 'TBR' then 5               
            end

---

Related Topics