Good afternoon math site sql-ex.ru and for several days scratching their heads over 75 puzzles, like the puzzle is not difficult, but 2 test base says otherwise)

Initially I jainil each table with the Product table

Not accepted

After, I decided to combine tables

Which was also rejected by the test base)

The question that I forgot to consider what a feature of the test database "Computer firm"?

Initially I jainil each table with the Product table

```
select
maker
max(l.price) as laptop,
max(pc.price) as pc,
max(r.price) as printer
from
Product t
left join Laptop l
on t.model=l.model
left join PC
on t.model=pc.model
left join Printer r
on t.model=r.model
group by
maker
```

Not accepted

After, I decided to combine tables

```
with
B as (
select
p.maker, p.type, max(a.price)
from
Product p
inner join (
select distinct model, price from Laptop
union
select distinct model, price from PC
union
select distinct model, price from Printer
) a
on
p.model=a.model
group by
p.maker, p.type
)
select
b1.maker
coalesce((select max from B where maker=b1.maker and type='Laptop'), null) as Laptop,
coalesce((select max from B where maker=b1.maker and type='PC'), null) as PC,
coalesce((select max from B where maker=b1.maker and type='Printer'), null) as Printer
from
B b1
group by
b1.maker
```

Which was also rejected by the test base)

The question that I forgot to consider what a feature of the test database "Computer firm"?

asked April 7th 20 at 15:52

1 answer

answered on

Solution

You forgot the condition "... that have a product with a known price of at least one of the tables..."

That is necessary:

That is necessary:

```
select
maker
max(l.price) as laptop,
max(pc.price) as pc,
max(r.price) as printer
from
Product t
left join Laptop l
on t.model=l.model
left join PC
on t.model=pc.model
left join Printer r
on t.model=r.model
group by
maker
having
max(l.price) IS NOT NULL
OR max(pc.price) IS NOT NULL
OR max(r.price) IS NOT NULL
```

Find more questions by tags PostgreSQLSQL