friedemann_bach Posted April 24, 2008 Share Posted April 24, 2008 Hello, I have a database containing a list of texts, a list of locations mentioned in those texts and a lookup table that connects the locations to the texts (called "sources"). I need a (single) query that returns a list of all locations in alphabetical order and the corresponding number of locations relating to that text. It should be easy, though I have not found find the correct syntax yet. Could anybody give me a hint? These are my tables: sources: source_id (primary key), source_name locations: location_id (primary key), location_name sources_locations: id (primary key), location_id, source_id Quote Link to comment Share on other sites More sharing options...
fenway Posted April 24, 2008 Share Posted April 24, 2008 Sounds like you need a 3-table JOIN... Quote Link to comment Share on other sites More sharing options...
friedemann_bach Posted April 24, 2008 Author Share Posted April 24, 2008 Yup! That's it. SELECT l.*, COUNT(*) AS count_loc FROM sources_locations JOIN sources USING (source_id) JOIN locations l USING (location_id) GROUP BY location_id ORDER BY l.name Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.