Performing query based on another query's result

Question

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?

Answer 1

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';

Answer 2

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')

• Akina: 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 ...
• Lennart - Slava Ukraini: @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.
• Rick James: DISTINCT could still return multiple rows, so that is not a solution. Also, DISTINCT is slower than simply LIMIT 1.