How do I Query Multiple Tables?

brianN

What would the SQL statement be to query multiple tables in the same database? all the tables are the same format and the colums are the same names and all.

nnichols

What db server are you running?? mysql?? pgsql??

Ideally you would do it using a UNION to join between the tables but mysql does not currently support this, so you would need to use separate queries or a temporary table.

brianN

Its MySQL

nnichols

in that case you will have to use one of the following -

a temporary table
multiple selects - pain because you can't order across the multiple tables
work around for union - http://www.nstep.net/~mpbailey/programming/tutorials.union.php
[/list=1]

The best option from the above depends on the data and what you want to do with it. Give an example of the tables, their data and how you expect the data to be returned.

dalecosp

edit!!Nevermind...I just re-read your point #2...it's only ordering across the "stories" table...

I've got a script example here that tries this:

$sql = "select s.id, s.headline, 10 * sum(k.weight) / $num_keywords as score
        from stories s, keywords k
        where s.id = k.story
        $and
        group by s.id, s.headline
        order by score desc, s.id desc";

$and is an array of keywords....

Isn't this an attempt at selecting across tables?

Curious,