Whats wrong with my sql statement with join

Question

I was doing this test sql http://sql-ex.ru/learn_exercises.php

Question was this

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.

Question to solve

Exercise: 2 (Serge I: 2002-09-21)
Find printer makers.
Result set: maker.

My query was

select distinct(p.maker) from Product p
 inner join Printer pr on pr.model = p.model

and it said

Your query produced correct result set on main database, but it failed test on second, checking database.
* Wrong number of records (less by 3)

I don't understood whats the issue

Answer 1

as he says " ... and type ('PC', 'Laptop', or 'Printer'). ..." this means that the type field is actually a text field denoting the type of system it is referring to.

"...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.."

It says that rows in printer table all have products, it does not say all products who are printers have rows in printers. This is a case of x=>y but y does not y=>x

although your query SHOULD result in the same data, your query uses INNER JOIN meaning that both the product and the Printer tables should have that model.

However, he means that product probably has printers not appearing in the Printer table.

 SELECT DISTINCT maker 
 FROM Product  
 WHERE type = 'printer'

which technically would also be achieved with

SELECT p1.maker 
FROM Product p1
LEFT JOIN Printer p2 
ON p1.model = p2.model
where p1.type = 'printer'

the where clause is needed because i told it to show all data in the products table.

the join in this case is not the solution, AGAIN this is due to the products table having printers not appearing in the printers table.

Answer 2

Model is not unique in printer. So if you join to printer.Model you can get 'double counting'. In his solution he is using IN to solve the double counting issue.

For example if we join these tables on Field1=Field7

Table 1                     Table 2
Field1 Field2 Field3        Field7 Field8 Field3
A      B      C             A      Z      Z
B      Z      Z             A      Z      X
                            A      Z      F

How many records do you get? You get 4. You get three A's + 1 B

It could also be solved like this:

select p.maker from Product p
inner join 
(select DISTINCT model from Printer) pr 
on pr.model = p.model

or like this:

select p.maker from Product p
where exists (select 1 from Printer pr where pr.model = p.model)