CODEWITHSUNDEEP
mysqljoin
nwolybug
nwolybug

Reputation: 472

Query with two selects and joins

In need of assistance forming a query that has two selects with joins. I am searching for duplicate entries within the same information, but the information is located in two tables.

Table ordermeta has orderid and formid. Table orders has orderid and userid

I am joining ordermeta to orders twice and then searching for different orderids where formid and userid match.

Here is what I have tried:

SELECT om0.orderid AS order1, om1.orderid AS order2
FROM ordermeta om0
LEFT JOIN ordermeta om1 ON om0.formid = om0.formid
LEFT JOIN orders o0 ON om0.orderid = o0.orderid
LEFT JOIN orders o1 ON om1.orderid = o1.orderid
WHERE o0.userid = o1.userid
AND om0.orderid != om1.orderid

I also tried this but got thoroughly confused and moved to the above code:

SELECT order1.oid1, order1.pd1, order2.oid2, order2.pd2
FROM (
    SELECT o1.orderid AS oid1, paid AS pd1, uid AS uid1, fid AS fid1 
    FROM  `orders` o1
    JOIN ordermeta om1 ON o1.orderid = om1.orderid
) order1
JOIN (
    SELECT o2.orderid AS oid2, paid AS pd2, uid AS uid2, fid AS fid2 
    FROM  `orders` o2
    JOIN ordermeta om2 ON o2.orderid = om2.orderid
) order2 ON order1.uid1 = order2.uid2 AND order1.fid1 = order2.fid2

I get way too many results than what I'm expecting. Should be in the order of 1000, but am getting over 223000.

Sample Data:

ordermeta table
HEADER: omid, orderid, formid, <other data>
1, 101, 201, ...
2, 102, 202, ...
3, 103, 201, ...
4, 108, 201, ...
5, 109, 202, ...

orders table
HEADER: orderid, userid, <other data>
101, 2000, ...
102, 2000, ...
103, 2001, ...
108, 2000, ...
109, 2001, ...

First, I need to associate the userid to the ordermeta combining the formid. Then, search through the combined data for duplicates where formid=formid and userid=userid and return the two orderids.

In the sample above, I should get orderids 101 and 108 as their userids and formids are a match after being joined.

Upvotes: 0

Views: 41

Answers (2)

pala_
pala_

Reputation: 9010

Your logic was pretty close:

select * 
  from ordermeta om1
  inner join
  orders o1
    on om1.orderid = o1.orderid
  inner join
  ordermeta om2
    on om1.formid = om2.formid
  inner join 
  orders o2 
    on om2.orderid = o2.orderid
  where o1.userid = o2.userid
    and o1.orderid <> o2.orderid

Demo fiddle here: http://sqlfiddle.com/#!9/b0936/1

In fact, your original query was fine if you didn't typo the join predicate for the two ordermeta tables

Upvotes: 1

Bohemian
Bohemian

Reputation: 424993

Your problem is in the first join condition:

LEFT JOIN ordermeta om1 ON om0.formid = om0.formid

Obviously a typo, but om0.formid = om0.formid is always true. Change it to:

LEFT JOIN ordermeta om1 ON om0.formid = om1.formid

You should move the conditions int he qhere clause into the join clauses too, so the whole query looks like:

SELECT om0.orderid AS order1, om1.orderid AS order2
FROM ordermeta om0
LEFT JOIN ordermeta om1 ON om0.formid = om0.formid
    AND om0.orderid != om1.orderid
LEFT JOIN orders o0 ON om0.orderid = o0.orderid
LEFT JOIN orders o1 ON om1.orderid = o1.orderid 
    AND o0.userid = o1.userid

Upvotes: 1

Related Questions