I am writing a query to fill a select box on a user form and it works fine but when I look at the SQL, I feel like there should be a better, more efficient way to write it. Notice the two nested SELECTs below are from the same table.
I am wondering if there is an SQL trick I can add to my repertoire since I have to do queries like this from time to time. Or perhaps my approach is sloppy by some standards? Any feedback is appreciated.
Here is the SQL:
SELECT c.id, c.county_name, adr.county
FROM (SELECT id, county_name FROM counties WHERE state = (SELECT state FROM members WHERE id = 53)) AS c
LEFT JOIN (SELECT county FROM members WHERE id = 53) AS a ON c.id = a.county;
Here are the results (partial): https://i.sstatic.net/Jawfa.png The "3022" in the right column is member 53's county.
If needed, here is an explanation of the query and my intentions:
I have a members table where the county field is stored as an integer linking to a lookup table called counties. I also want to filter the county results to the member's state.
The query is filling an HTML select element and I want to pre-select the county of the member. The only information my PHP has available (without running a second query to get more) is the member id.
So the query needs to return the county id, the county name and some kind of identifier to let me know which record is matching for the member (I just went with the linking county field and my code will check for that).
The database is an MDB file accessed from a PDO ODBC connection.
You may need not need any subqueries but possibly a self-join:
SELECT counties.id, counties.county_name, members_1.county
FROM counties
INNER JOIN members ON counties.state = members.state
LEFT JOIN members_1 ON counties.id = members_1.county
WHERE members.id = 53 AND members_1.id = 53