Help with sql query

Question

I am looking for some help in creating the correct SQL for this scenario...

Two table Model and Serials simplified

Model
-----
CODE | MAKE | MODEL

Serials
-------
CODE (FK) | SERIAL

I want to retrieve the CODE, MAKE AND SERIAL where the MAKE = 'some value' I only want the distinct CODES though...

Answer 1

select distinct code, make, model from model inner join code on model.code = serials.code where model = 'some value';

Answer 2

Not sure if that's what you mean, but have you tried:

SELECT DISTINCT code, make, serial
FROM model, serials
WHERE model.code = serials.code
AND make = 'some value'

Answer 3

select distinct Model.Code 
from Model JOIN Serials on Model.Code = Serials.Code 
where make = 'some value' and Serials.Serial not null

Edit after reading comment on other answer.

Answer 4

Assuming code is a PRIMARY KEY (otherwise, you cannot make an FK you mentioned):

SELECT  code, make,
        (
        SELECT  serial
        FROM    serials
        WHERE   serials.code = model.code
        LIMIT 1
        )
FROM    model
WHERE   MAKE = 'some value'

This is MySQL syntax.

You need to define which serial you want to show when you have several serials for a code and add an ORDER BY clause into subquery.

If you have this data:

code  make         model
123   SOME VALUE   Ford Sierra

code  serial
123   1
123   2

, do you want to show serial 1 or 2 and why?

Answer 5

The way your tables seem to be set up there will be multiple rows in Serials for each Code, no? If so, there is no way to get all the serials for a single code without getting multiple rows for that code. Is that what you meant by only getting distinct codes? If so, you can't do that... but otherwise...

try this:

Select Code, Make, Serial
From Model M
  Join Serials S
     On  M.code = S.code
Where Make = 'some value'