CODEWITHSUNDEEP
mysqlsql
jmarx34
jmarx34

Reputation: 228

select DISTINCT email based off of matching ID across tables

I'm trying to pull unique emails that have matching IDs across two tables. 

SELECT line_items.order_id, line_items.id, orders.email, orders.name
FROM orders INNER JOIN
     line_items
     ON orders.id = line_items.order_id
WHERE line_items.order_id IN (SELECT DISTINCT email FROM orders WHERE status = 0 AND created_at BETWEEN '2018-01-10' AND NOW() )
LIMIT 50;

I know my error is based upon the fact that the line_items.order_is is an INT and therefore the IN parameter is looking for another int column to match against. However, I'm not sure how to modify this to get pull the proper results. Any and all help is greatly appreciated.

Upvotes: 0

Views: 101

Answers (2)

kjmerf
kjmerf

Reputation: 4345

Hard to follow but I think you want:

SELECT sub.order_id, sub.line_item_id, sub.email, o.name
FROM  
(SELECT o.email, MIN(o.id) AS order_id, MIN(i.id) AS line_item_id
 FROM orders o
 INNER JOIN line_items i
 ON o.id = i.order_id
 WHERE o.status = 0
 AND o.created_at BETWEEN '2018-01-10' AND NOW()
 GROUP BY o.email) sub
LEFT JOIN orders o
ON sub.order_id = o.id

In the sub-query, select each email along with the first order ID and line item ID. Then join this back to orders to pull the order name. This does assume that the MIN(line_item) will show up with the MIN(order_id) for each email, so you'll have to let me know if that is not a valid assumption.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269733

I'm trying to pull unique emails that have matching IDs across two tables.

If you mean distinct emails, then your subquery would appear to do this:

SELECT DISTINCT o.email
FROM o.orders
WHERE o.status = 0 AND o.created_at BETWEEN '2018-01-10' AND NOW();

Because an order should have at least one line item, I don't see why that table is necessary.

Your query comes close to answering the question: "Pull all orders for emails that have made a recent order with status 0". If that is what you want:

SELECT li.order_id, li.id, o.email, o.name
FROM orders o INNER JOIN
     line_items li
     ON o.id = li.order_id
WHERE o.email IN (SELECT o2.email FROM orders o2 WHERE o2.status = 0 AND o2.created_at BETWEEN '2018-01-10' AND NOW() )
LIMIT 50;

Upvotes: 1

Related Questions