SQL for Applying Conditions to Multiple Rows in a Join

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'

Apply where condition when I have multiple rows for each id (Single table or Join multiple ones)

One method is to use aggregation and a having clause;

select b.pid
from basic b
group by p.pid
having sum(case when (b.sid = 2) and (b.st >= 7) then 1 else 0 end) > 0 and
       sum(case when (b.wid = 9) and (b.wt >= 6) then 1 else 0 end) > 0;

Each condition in the having clause counts the rows that match each condition. The > 0 ensure that there is at least one row for each.

How can I apply a condition to multiple rows using SQL? CASE WHEN / PARTITION OVER

You can use MAX() window function to return 'Yes' when there is a name with 'CAL'.

If there isn't, MAX() will return NULL which will be turned to 'No' by COALESCE():

SELECT id, name,
       COALESCE(MAX(CASE WHEN name = 'CAL' THEN 'Yes' END) OVER (PARTITION BY id), 'No') AS fulfil
FROM shipment;

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;

How to do join on multiple criteria, returning all combinations of both criteria

select one.*, two.meal
from table1 as one
left join table2 as two
on (one.weddingtable = two.weddingtable and one.tableseat = two.tableseat)

How to apply a condition only when multiple rows are retrieved in SQL Server

Just order by ci.CLOCK_OUT_TIME if you have multiple rows and some of those are null will be on the top.

ORDER BY 
    CAST(ps.start_date + ' ' + ps.start_time AS DATETIME),
    ci.CLOCK_OUT_TIME

SQL DEMO

how can I apply a condition to just one part of a multiple join in SQL?

There are multiple ways to achieve that

First would be to simply put the filter clause at the end of the query. Mostly this must produce same results, although in some cases, this might not be the case.

SELECT
        tab1.col1, tab2.col1, tab3.col2
FROM
        tab1
JOIN
        tab2
        ON tab1.col1=tab2.col1
LEFT JOIN
        tab3
        ON tab1.col2=tab3.col2
WHERE
        blah blah
        AND
        desired_column=filter_value
GROUP BY
        blah blah
HAVING
        blah blah
;

Second option is to use nested queries. It is more likely that this method with produce the results of your liking. An intelligent query optimizer may rewrite this query to the one above or vice versa.

SELECT
        tab1.col1, tab2.col1, tab3.col2
FROM
        tab1
JOIN
        (SELECT col1, col2 FROM tab2 WHERE desired_column=filter_value) tab2
        ON tab1.col1=tab2.col1
LEFT JOIN
        tab3
        ON tab1.col2=tab3.col2
WHERE
        blah blah
GROUP BY
        blah blah
HAVING
        blah blah
;

A third option, in case you are using INNER JOIN, is to mention this as part on join condition

SELECT
        tab1.col1, tab2.col1, tab3.col2
FROM
        tab1
JOIN
        tab2
        ON tab1.col1=tab2.col1
        AND
        desired_column=filter_value
LEFT JOIN
        tab3
        ON tab1.col2=tab3.col2
WHERE
        blah blah
GROUP BY
        blah blah
HAVING
        blah blah
;

You may also use the last one with OUTER JOINs if you do not want to filter the set and only relate based upon the mentioned criteria.

---

Related Topics