CODEWITHSUNDEEP
mysqlsqldatabasepostgresql
keivn
keivn

Reputation: 114

sql query combines columns

I was doing the exercise for sql query, but get to the point where i have 3 columns with the same name and some of them contains null, what is the option so i can combine them into one column call price instead of 3.

Short database description "Computer firm":

The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price

Exercise: 7 Find out the models and prices for all the products (of any type) produced by maker B.

my query: 

SELECT distinct Product.model, PC.price, Laptop.price,Printer.price as price 
from   Product 
left   join PC 
on     Product.model=PC.model 
left JOIN Laptop 
ON Product.model=Laptop.model 
left join Printer 
on Product.model= Printer.model 
where Product.maker='B';

output:

Your query:

model   price   price   price
1121    850     NULL    NULL
1750    NULL    1200    NULL

correct query: 

model   price
1121    850
1750    1200

Upvotes: 1

Views: 1989

Answers (3)

RSZ
RSZ

Reputation: 1

SELECT AVG( price) FROM (
    SELECT price, model
    FROM pc
    WHERE model IN (
        SELECT model
        FROM product
        WHERE maker='A' AND type='PC'
    )
    UNION ALL
    SELECT price, model
    FROM laptop
    WHERE model IN (
        SELECT model
        FROM product
        WHERE maker='A' AND type='Laptop'
    )
) as prod

Upvotes: -1

John Woo
John Woo

Reputation: 263803

try using COALESCE

SELECT distinct Product.model, 
       COALESCE(PC.price, Laptop.price,Printer.price) as price 
from Product left join PC 
            on Product.model = PC.model 
     left JOIN Laptop 
            ON Product.model = Laptop.model 
     left join Printer 
           on Product.model = Printer.model 
where Product.maker='B'

from the definition,

COALESCE Returns the first nonnull expression among its arguments.

UPDATE 1

SELECT  a.model, a.price
FROM    PC a INNER JOIN Product b
            ON a.model = b.model
WHERE   b.maker = 'makerB'
UNION
SELECT  a.model, a.price
FROM    Laptop a INNER JOIN Product b
            ON a.model = b.model
WHERE   b.maker = 'makerB'
UNION
SELECT  a.model, a.price
FROM    Printer a INNER JOIN Product b
            ON a.model = b.model
WHERE   b.maker = 'makerB'

Upvotes: 2

sicKo
sicKo

Reputation: 1256

You can use UNION

SELECT Product.model, newTbl.price FROM Product
  INNER JOIN
(
SELECT model, price FROM PC
  UNION
SELECT model, price FROM Laptop
  UNION
SELECT model, price FROM Printer
)newTbl ON Product.model = newTbl.model

or if you only need the 1 column only from table Product which is model, You can discard the Product table like this

SELECT model, price FROM PC
      UNION
SELECT model, price FROM Laptop
      UNION
SELECT model, price FROM Printer

Upvotes: 0

Related Questions