Joining multiple tables with a single query

showman

I've got a simple question that I could answer myself if I hadn't lost all my PHP / MySql reference manuals in a house fire last week.

If someone could give me a quick answer I would certainly appreciate it..

Basically I need to know if I am doing a join of all fields from 4 different tables in a select query, do each of the tables have to have exactly the same layout, number and names of fields?

$query = "
SELECT * from current, mine, sponsor, toplist 
WHERE current.catagory = '$catagoryname', 
mine.catagory = '$catagoryname', 
sponsor.catagory = '$catagoryname' 
toplist.catagory = '$catagoryname' 
";

I'm thinking that the answer is going to be that they need to be the same, or I won't be able to know what data is in what field, but I'm just a bit confused with this one, so I figured the quickest way to a definitive answer was to bring it here..

thanks in advance
doug
showman

sijis

do you mean something like this:

$query = "SELECT * from current, mine, sponsor, toplist
WHERE current.catagory = mine.category AND
mine.catagory = sponsor.category AND
sponsor.catagory = toplist.category WHERE
toplist.catagory = '$catagoryname' ";

I believe there is a mysql command called 'Join' but I don't have a clue how to use it.

PHPThorsten

A Join would look like

$query = "SELECT * FROM current 
LEFT JOIN mine ON (mine.category = current.category) 
LEFT JOIN sponsor ON (sponsor.category = current.category)
LEFT JOIN toplist ON (toplist.category = current.category)
WHERE current.category = '$category'";

showman

Actually, what I need to do is to get every row from each of the 4 tables where the selection criteria is met

catagory = '$catagoryname'

Each table has the field name catagory, and I need to extract all records in a single select from all 4 tables where that match is made.. Maybe Join was the incorrect terminology to use..

What the question is, is when doing the select from the 4 tables, selecting *, do the 4 tables need to be structured exactly the same?

thanks for your input

doug

sijis

nope. It doesn't matter how how the table is constructed. If 1 table has 15 fields and another table as 3 fields, it just get all that info.

now if you are referring to table type, like MyISAM, ISAM, Berkley DB.... I don't know for sure, but I would assume using SELECT would still work.