SELECT gives too much results

[michel1965]

Hi there,

In the following example i get more results than expected.

SELECT DISTINCT

City.Name,
Person.Name,
Person.Adres
FROM
Person,
City
WHERE
Person.CityID = 5

It gives me the following result :

CITY - NAME - ADRESS

Amsterdam - Mike - Kalverstraat 23
Rotterdam - Mike - Kalverstraat 23
Eindhoven - Mike - Kalverstraat 23
Amsterdam - Henk - Rembrandsplein 12
Rotterdam - Henk - Rembrandsplein 12
Eindhoven - Henk - Rembrandsplein 12

CityID 5 is 'Amsterdam', so that's correct and Mike and Henk both live in Amsterdam. But whuy do I get this double results ???

Greetings from the netherlands,

Michel Hendriks

[Anon]

You will get all rows from the city table as you don't have any restriction on that table. As you don't have a join bteween the tables you will get this repeated for all rows in the person table that fulfils the criteria.

SELECT
City.Name,
Person.Name,
Person.Adres
FROM
Person join city on person.cityid = city.cityid
WHERE
Person.CityID = 5

I guessed the name of the id column in the city table.

The distinct is superfluous

[mattr]

Remember DISTINCT works by removing duplicate ROWS (tuples) so this is expected behavior.

Also, you are NOT joining the Person and City tables, so you are getting what is known as a cartesian product (badThing).

I don't know what your foreign keys are, but it should look something like this:
SELECT DISTINCT C.Name,
P.Name,
Adres
FROM Person P,
City C
WHERE P.CityID = C.CityID
AND P.CityID = 5