Villa Villa - 6 months ago 20
SQL Question

SQL query to return table with if exists retun price 2 else return price 1

I need help making a query to show the folowing result.

Supose I have tables:

Table 1


ProductId Description
1 Banana
2 Apple
3 Melon
4 Orange


Table 2

ProductId PriceNumber Price
1 1 86
1 2 55
2 1 58
3 1 99
3 3 66
4 1 87
4 2 78


I need to show PriceNumber = 2 and if it doesn't exists show PriceNumber = 1

Wanted result:

ProductId Description PriceNum Price
1 Banana 2 55
2 Apple 1 58
3 Melon 1 99
4 Orange 2 78


Thank you!

Answer

Here's the setup of the tables:

CREATE TABLE Table1
    (`ProductId` int, `Description` varchar(6))
;

INSERT INTO Table1
    (`ProductId`, `Description`)
VALUES
    (1, 'Banana'),
    (2, 'Apple'),
    (3, 'Melon'),
    (4, 'Orange')
;


CREATE TABLE Table2
    (`ProductId` int, `PriceNumber` int, `Price` varchar(5))
;

INSERT INTO Table2
    (`ProductId`, `PriceNumber`, `Price`)
VALUES
    (1, 1, '7,86'),
    (1, 2, '3,55'), 
    (2, 1, '10,58'),
    (3, 1, '2,99'),
    (4, 1, '9,87'),
    (4, 2, '6,78')
;

Here's the actual answer in code:

SELECT  distinct(Table2.ProductId), 
        Description,
        PriceNumber, 
        Price 
FROM Table2
    INNER JOIN Table1
    ON Table1.ProductId = Table2.ProductId

WHERE (PriceNumber = 2) OR 
      (
          (Table2.ProductId not in (
                     SELECT ProductId 
                     FROM Table2 
                     WHERE PriceNumber = 2
                     )
           )
           AND
        (PriceNumber = 1)
       )

Here's a link to a sqlfiddle where you can play with the code: http://sqlfiddle.com/#!9/234ab/4/0