Help with SELECT query

[sim18]

I have to list all the names of footballers who have a trophy in London.

The trophies are in the GalleryTable:

galleryId City
G01 London
G02 London
G03 Liverpool

And the footballers in the footballTable:

FootballID FootballerName
F01 John Terry
F02 Frank Lampard
F03 Ryan Giggs

The Gallery table is connected to an Exhibition full of trophies and the Footballer table is connected to a trophy table which lists all the trophies, this is then linked to which exhibition it is held in.

So far I have :

SELECT FootballerName
FROM Footballer, gallery
WHERE gallery.city=London;

When i run this query it asks me for a parameter value (which I don't want)
but if I put London in it comes up with the correct Footballers but just duplicate names :/

Thanks for any help!

 

[pbaldy]

This would need to be

WHERE gallery.city="London"

Since you don't have a join between the tables, you're getting a Cartesian product. I don't see any relationship between those two tables; is there one?

 

[sim18]

This would need to be

WHERE gallery.city="London"

Since you don't have a join between the tables, you're getting a Cartesian product. I don't see any relationship between those two tables; is there one?

Sorry for the late reply, these tables are not related.

I tried what you suggested :

SELECT artistName
FROM Artist, gallery
WHERE gallery.city="London"

That just comes up with all of the footballers names, instead of just the ones from london.

Thanks for your reply

 

[pbaldy]

If they're not related, how is Access supposed to know how to restrict the records returned? What name(s) would you expect to get, and why?

 

[sim18]

If they're not related, how is Access supposed to know how to restrict the records returned? What name(s) would you expect to get, and why?

They are connected via Foreign Keys from other tables for example:

FootballTable----TrophiesTable----ExhibitionTable----GalleryTable

The GalleryTable shows that the city is in "London"

They are connected and from the GalleryTable you can see which trophies are in the exhibition via the foreign keys.

Would this not work then?

Thanks

 

[pbaldy]

If other tables are required to relate them, they would have to be included in the query. It won't work without a join to relate one table to the other.

 

[sim18]

If other tables are required to relate them, they would have to be included in the query. It won't work without a join to relate one table to the other.

Does that mean they would be joined in the FROM part for example :

SELECT artistName
FROM Artist, gallery, exhibition, trophies
WHERE gallery.city="London"

So that it includes all the tables?

Sorry iv'e got myself really confused with this now

thanks

 

[pbaldy]

Well, that would get you even more duplicates because there is still no join, which would look like:

SELECT...
FROM OneTable INNER JOIN AnotherTable ON OneTable.FieldName = AnotherTable.FieldName

Can you post the db here?

 

[sim18]

Well, that would get you even more duplicates because there is still no join, which would look like:

SELECT...
FROM OneTable INNER JOIN AnotherTable ON OneTable.FieldName = AnotherTable.FieldName

Can you post the db here?

Not all fields have been filled in but the important ones are. Thanks very much