SQL to return a merged set of results

Question

I have the following SQL:

SELECT `table1`.`value`, `table2`.* 
FROM `table2` 
INNER JOIN `table1` ON `table2`.`product_id` = `table1`.`entity_id`
WHERE `table2`.`created_at` > '2012-04-23' and 
(`table1`.`value` = 264 OR `table1`.`value` = 260)
order by order_id

Which returns a result set like this (This is only a part of the returned results):

value  order_id   ...
260    1234
260    1235
260    1236
264    1236
260    1237
260    1238
260    1239
264    1239
264    1240
260    1241

What I want is a query that will take these results and only return orders where the order_id contains both value 260 and 264. Based on this example, the end result I am looking for is

My thought is this can be done with a subset but I am not exactly sure on the details to accomplish it.

I'm having a hard time understanding this. Can you create an SQLfiddle with some test data? — Hawkee, May 07 '12 at 17:01
do you need both `value` and `order_id` or just a list of `order_id`s that have both 260 and 264 value? the former case is already answered, the latter one is a simple self-join (that will be much more faster) — Aprillion, May 07 '12 at 17:50
@deathApril good point about the self-join, with the caveat that it will be harder to extend when the list grows to 3 `order_id`s ... then 4, 5 .... — Matt Fenwick, May 07 '12 at 17:58
Just the list of order_id s that have both 260 and 264 would be ideal. — Greg Demetrick, May 07 '12 at 18:13

Matt Fenwick · Accepted Answer · 2012-05-07T17:35:42.407

This can be accomplished with relational division:

select r.order_id from (
  select 
    dividend.*  
  from your_table_or_query as dividend  -- assumes no duplicates in `dividend`; use `distinct` if there are any
  inner join divisor
  on dividend.value = divisor.value
) as r
group by r.order_id
having count(*) = (select count(*) from divisor);

result:

+----------+
| order_id |
+----------+
|     1236 |
|     1239 |
+----------+
2 rows in set (0.00 sec)

where your query is your_table_or_query and

select 260 as value from dual union select 264 as value from dual

is divisor.

This will return the order ids 1236 and 1239; they can then be joined to original query to get all the rows with those order ids if that's what you want.

Full query along with insert statements:

create table divisor (value int);
insert into divisor values (260), (264);

create table your_table_or_query (value int, order_id int);
insert into your_table_or_query values (260, 1234), (260, 1235), (260, 1236), (264, 1236), (260, 1237), (260, 1238), (260, 1239), (264, 1239), (264, 1240), (260, 1241);


select y.* from (
  select r.order_id from (
    select
      dividend.*
    from your_table_or_query as dividend
    inner join divisor
      on dividend.value = divisor.value
  ) as r 
  group by r.order_id
  having count(*) = (select count(*) from divisor)
) as quotient
inner join your_table_or_query y
  on quotient.order_id = y.order_id;

Result:

+-------+----------+
| value | order_id |
+-------+----------+
|   260 |     1236 |
|   264 |     1236 |
|   260 |     1239 |
|   264 |     1239 |
+-------+----------+
4 rows in set (0.00 sec)

+1 great general-purpose solution,, but in this particular case i think it is a bit too costly using `having` instead of a simple join.. — Aprillion, May 07 '12 at 18:13
How costly it is will depend mainly on the context: size of input, size of output etc. — Matt Fenwick, May 07 '12 at 19:38
This took me a while to wrap my head around what 'divisor' should be but eventually I got there. Thanks! — Greg Demetrick, May 07 '12 at 19:48

score 1 · Answer 2 · answered May 07 '12 at 17:14

How about this?

SELECT table1.value, table2.* 
FROM table2 
INNER JOIN table1 
ON table2.product_id = table1.entity_id
WHERE table2.created_at > '2012-04-23' AND
(table1.value = 264 OR table1.value = 260)
AND table2.order_id IN (
    SELECT table2.order_id  
    FROM table2 
    INNER JOIN table1 
    ON table2.product_id = table1.entity_id
    WHERE table2.created_at > '2012-04-23' AND
    (table1.value = 264 OR table1.value = 260)
    GROUP BY table2.order_id
    HAVING COUNT(*) = 2
)

(sorry for omitting apostrophes, I am not aware what are mysql requirements in this field; this syntax is ms-sql compliant)

I started with this but the time to execute on the server was too much for it to bear and I had to stop the process. It may be a viable solution but not one for 4,600+ records. — Greg Demetrick, May 07 '12 at 19:49

Aprillion · Answer 3 · 2012-05-07T18:09:19.327

this will return a list of orders that have both values 260 and 264 - if someone needs just this result instead of duplicated results for each of the value:

SELECT `table2`.* 
FROM `table2` 
INNER JOIN `table1` as `table1_264`
  ON `table2`.`product_id` = `table1_264`.`entity_id`
  AND `table1_264`.`value` = 264
INNER JOIN `table1` as `table1_260`
  ON `table2`.`product_id` = `table1_260`.`entity_id`
  AND `table1_260`.`value` = 260
WHERE `table2`.`created_at` > '2012-04-23'
order by order_id

or if someone wanted faster solution without costly having clauses (+ of course you can store the query or use a CTE instead of just repeating it)

SELECT 264 as `value`, `table2`.* 
 FROM `table2` 
 INNER JOIN `table1` as `table1_264` ON `table2`.`product_id` = `table1_264`.`entity_id` AND `table1_264`.`value` = 264
 INNER JOIN `table1` as `table1_260` ON `table2`.`product_id` = `table1_260`.`entity_id` AND `table1_260`.`value` = 260
 WHERE `table2`.`created_at` > '2012-04-23'
UNION ALL
SELECT 260, `table2`.* 
 FROM `table2` 
 INNER JOIN `table1` as `table1_264` ON `table2`.`product_id` = `table1_264`.`entity_id` AND `table1_264`.`value` = 264
 INNER JOIN `table1` as `table1_260` ON `table2`.`product_id` = `table1_260`.`entity_id` AND `table1_260`.`value` = 260
 WHERE `table2`.`created_at` > '2012-04-23'
order by order_id

For some reason when I ran this I would get zero results. Removing one of the inner joins would get me the correct number of sub items and the logic is sound so I upvoted it as a possible solution for someone else. In all aspects it should work but I moved on to a different solution as I had options. — Greg Demetrick, May 07 '12 at 19:51

score -1 · Answer 4 · answered May 07 '12 at 17:04

-1

SELECT `table1`.`value`, `table2`.* 
FROM `table1` 
LEFT JOIN `table2` ON `table1`.`entity_id` = `table2`.`product_id`
WHERE
(`table1`.`value` = 264 OR `table1`.`value` = 260)
AND `table2`.`created_at` > '2012-04-23' 
ORDER BY `table2`.order_id

Left join so it only matches the table1.value results with matches and leave the info from the other table out

answered May 07 '12 at 17:04

Scarecrow

205
1
7

I believe the `LEFT JOIN` will do just the opposite. – bfavaretto May 07 '12 at 17:07
perhaps I misunderstood the question then – Scarecrow May 07 '12 at 17:09
Your answer seems to be okay, what is needed is what you added to `WHERE`. Your `LEFT JOIN` will actually behave like an `INNER JOIN` because `WHERE` contains a condition on `table2`. – bfavaretto May 07 '12 at 17:11
are you sure? the condition for the second table to is `AND` so if it doesn't meet the previous condition it will be filtered out no? – Scarecrow May 07 '12 at 17:16
the difference between inner and outer joins is how `null` values will be handled. `>` (greater than inequality operator) will be falsy if either argument is null. => this is the same as an inner join – Aprillion May 07 '12 at 17:43

SQL to return a merged set of results

4 Answers4

Linked