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 Link to comment https://forums.phpfreaks.com/topic/102708-solved-query-subquery-help/ 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... Link to comment https://forums.phpfreaks.com/topic/102708-solved-query-subquery-help/#findComment-526048 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 Link to comment https://forums.phpfreaks.com/topic/102708-solved-query-subquery-help/#findComment-526105 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.