NYCPHP Meetup

[Daniel Convissor]

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