0

First I have to run the query (1) using the pagedetailid number to get the adblockid. Then I have to put that adblockid into the other query (2) to get the result. What I would prefer is a single query.

  1. select distinct Flyerid, adblockid, OriginalPagedetailid, Pagedetailid, 
    category, brand, [Sales Price], Size, Format, manufacturer
    from prdcnen where pagedetailid = '67197228466'
    
  2. select distinct Flyerid, adblockid, OriginalPagedetailid, Pagedetailid, 
    category, brand,[Sales Price], Size, Format, manufacturer
    from prdcnen where Adblockid = 1521368536511158
    

How can I merge these two statements?

3

One possible way is an inner self join.

SELECT DISTINCT
       p1.Flyerid,
       p1.adblockid,
       p1.OriginalPagedetailid,
       p1.Pagedetailid,
       p1.category,
       p1.brand,
       p1.[Sales Price],
       p1.Size,
       p1.Format,
       p1.manufacturer
       FROM prdcnen p1
            INNER JOIN prdcnen p2
                       ON p2.Adblockid = p1.Adblockid
       WHERE p2.pagedetailid = '67197228466';
1

In it's simplest form you can just replace the literal for adblockid with a query:

SELECT distinct Flyerid, adblockid, OriginalPagedetailid, Pagedetailid
     , category, brand,[Sales Price], Size, Format, manufacturer 
FROM prdcnen
WHERE adblockid = (select distinct adblockid
                   from prdcnen 
                   where pagedetailid = '67197228466')
3
  • There is no guarantee the adblockid is unique for each pagedetailid. I think you must use LIMIT 1 instead of DISTINCT. Or ... WHERE adblockid = SOME (select ...
    – Akina
    Aug 2 '18 at 12:09
  • @Akina, if it's not unique the question does not make sense. We would then have to choose an adblockid by some criteria, but none is given.
    – Lennart
    Aug 2 '18 at 13:10
  • DISTINCT could still return multiple rows, so that is not a solution. Also, DISTINCT is slower than simply LIMIT 1.
    – Rick James
    Aug 20 '18 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.