By AMIT KUMAR


2018-08-02 11:41:51 8 Comments

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?

2 comments

@Lennart 2018-08-02 12:02:20

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 2018-08-02 12:09:37

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 2018-08-02 13:10:00

@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 2018-08-20 14:53:32

DISTINCT could still return multiple rows, so that is not a solution. Also, DISTINCT is slower than simply LIMIT 1.

@sticky bit 2018-08-02 11:56:14

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

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Multiple Result Tabs in Workbench

3 Answered Questions

[SOLVED] How to select distinct for one column and any in another column?

  • 2012-09-14 14:30:10
  • WilliamKF
  • 178664 View
  • 29 Score
  • 3 Answer
  • Tags:   query

2 Answered Questions

[SOLVED] Count rows in a table based on a DATE field

1 Answered Questions

[SOLVED] Selecting only when specific values exist in another select query

  • 2018-11-30 17:33:59
  • shanik1986
  • 115 View
  • 1 Score
  • 1 Answer
  • Tags:   mysql select

1 Answered Questions

2 Answered Questions

[SOLVED] Need some assistance on MySQL Sub query

  • 2015-07-06 13:04:59
  • Bikram Pahi
  • 60 View
  • 2 Score
  • 2 Answer
  • Tags:   mysql subquery

2 Answered Questions

[SOLVED] How to Join Two Result sets to query on output came from Two statements

  • 2013-08-09 13:05:36
  • Narendar_CH
  • 74974 View
  • 5 Score
  • 2 Answer
  • Tags:   mysql

2 Answered Questions

[SOLVED] How to sort category by path and sort order?

  • 2013-02-20 13:39:08
  • Farid Movsumov
  • 2348 View
  • 4 Score
  • 2 Answer
  • Tags:   mysql

Sponsored Content