Left Joining Three tables with Subquery

Question

SELECT p.*
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id
LEFT JOIN Offers o
ON ot.offer_id =  (SELECT id FROM Offers 
                   Where dF<=3 AND dt>=3)

ORDER BY o.id DESC, 
          p.prod_id ASC
LIMIT 20

The Output is:

| PROD_ID | CATEGORY_ID | PROD_NAME |  BRAND | PRICE | STATUS |
---------------------------------------------------------------
|      p3 |          c1 |     prod3 | brand3 |  3000 | active |
|      p3 |          c1 |     prod3 | brand3 |  3000 | active |
|      p1 |          c1 |     prod1 | brand1 |  1000 | active |
|      p2 |          c2 |     prod2 | brand2 |  2000 | active |
|      p4 |          c2 |     prod4 | brand1 |  4000 | active |
|      p5 |          c1 |     prod5 | brand2 |  5000 | active |
|      p6 |          c2 |     prod6 | brand3 |  6000 | active |

But, here as you can see p3 is shown two times. I dont know why.. I had tried all the possible ways but I cant find any remedies.. :(

score 4 · Accepted Answer · edited Oct 03 '12 at 08:47

The problem in

LEFT JOIN Offers o
ON ot.offer_id =  (SELECT id FROM Offers 
                   Where dF<=3 AND dt>=3)

You join ot.offer_id with constant values (there is no meaning you select them from offers table).

The correct way:

SELECT p.*
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id
LEFT JOIN Offers o
ON ot.offer_id = o.id AND o.dF<=3 AND o.dt>=3
ORDER BY o.id DESC, 
          p.prod_id ASC
LIMIT 20

Look at this SQLFiddle

score 1 · Answer 2 · answered Oct 03 '12 at 08:34

Maybe the join condition between 'o' and 'ot' is missing:

SELECT p.*
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id
LEFT JOIN Offers o
ON ot.offer_id IN(SELECT id FROM Offers 
               Where dF<=3 AND dt>=3)
**AND ot.offer_id = o.id***
ORDER BY o.id DESC, 
      p.prod_id ASC
LIMIT 20

Or maybe I missed something and didn't undertand what you want to get from the DB with your query.

score 1 · Answer 3 · answered Oct 03 '12 at 08:37

1

May be you're looking for this. This will return p3 only once. You missed the o.id in Second Left Join condition.

SELECT p.*
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id
LEFT JOIN Offers o
ON o.id =  (SELECT id FROM Offers 
            Where dF<=3 AND dt>=3)

ORDER BY o.id DESC, 
          p.prod_id ASC
LIMIT 20

answered Oct 03 '12 at 08:37

Vishwanath Dalvi

35,388
41
123
155

this is not what i wanted.. here I lost all my sequences.. anyways.. thanks for your answer – Anusha Oct 03 '12 at 08:45

score 0 · Answer 4 · answered Oct 03 '12 at 08:31

0

You probably have two records in offertran or Products with the PROD_ID of p3. You could change it to be SELECT DISTINCT p.* FROM ... and that way any duplicate rows will be ignored.

Just to see what offertran is returning, change you select p.* to select *

answered Oct 03 '12 at 08:31

Adam Plocher

13,994
6
46
79

I have only one record of p3 Adam. Thanks for your reply – Anusha Oct 03 '12 at 08:46

Left Joining Three tables with Subquery

4 Answers4