[nycphp-talk] MySQL Query Question
Daniel Convissor
danielc at analysisandsolutions.com
Sun Dec 19 19:25:18 EST 2004
On Sun, Dec 19, 2004 at 07:12:17PM -0500, harvey wrote:
>
> Now suppose I want to query the db and alphabetically list the Name, State,
> and Party of each Senator. Question is this--in general, which (if either)
> is the preferred method?
One query is better. Joining tables is what DBMS's are all about.
I prefer the JOIN syntax rather than the WHERE syntax.
Also, I suggest naming the state_id field (etc) the same in both the
senate and state tables, then you can use the easier USING clause for the
_first_ (in this case) join. So, for example:
> Select senate.senator_name, state.state_name, party.party_name
> From senate, state, party
> Where senate.state_fid = state.state_id
> And senate.party_fid = party.party_id
> Order by senate.senator_name
SELECT senate.senator_name, state.state_name, party.party_name
FROM senate
JOIN state USING state_id
JOIN party ON (party.party_id = senate.party_id)
ORDER BY senate.senator_name
As far as the USING vs ON clause... You can only use JOIN if the columns
are the same in table/join right above it have the same names.
By the way, you've done a good job normalizing your database.
> The first method would seem more elegant, but it seems to take longer also
> (although that might just be my imagination).
I suspect you have a very wild imagination.
--Dan
--
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
data intensive web and database programming
http://www.AnalysisAndSolutions.com/
4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409
More information about the talk
mailing list