Return duplicates when found in WHERE clause?-CodePudding

I have a query to get a SUM total from a subquery like so:

SELECT ROUND(SUM(x.price),2) as "total" FROM (
   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201 or cd.id = 31374201) 
   GROUP BY cd.name
) x

I want the subquery to add a new distinct row when duplicates are found in the WHERE clause;

Take this example:

   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201) 
   GROUP BY cd.name

It has 1 WHERE condition and the output is;

However, if I modify it and add a duplicate OR cd.id = 31374201 :

   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201 OR cd.id = 31374201) 
   GROUP BY cd.name

Then I want the expected result to be two distinct columns so they can be summed in my original query:

So far, the WHERE clause seems to ignore/exclude duplicate entries when using OR.

I can do this in PHP but I'd like to know if there is a way to avoid that and do it directly in MySQL.

CodePudding user response：

Why do you use same condition?

cd.id = 31374201

CodePudding user response：

As pointed out in the comments by @Barmar and @Frankich, the query is not ignoring OR. The row is simply being selected.

As such, I had to rewrite the query using UNION ALL. Regular UNION would strip duplicates.

SELECT ROUND(SUM(x.price),2) as "total" FROM (
   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201) GROUP BY cd.name
   UNION ALL
   SELECT cd.name, MIN(tcgs.marketPrice) as "price" 
   FROM card_database cd
   INNER JOIN tcgplayer_cards tcgc ON tcgc.name = cd.name
   INNER JOIN tcgplayer_set_prices tcgs ON tcgc.productID = tcgs.productId
   WHERE (cd.id = 31374201) GROUP BY cd.name
) x