MySQL join syntax..

[ronnie]

I have 4 tables I want to query at once and also use WHERE. The WHERE is the same for all tables, same field name and I'm looking for the same value for all 4. I'm not getting it to work. I've tried these two statments and the first wont query and second gives me no reults. Of course there is data in the tables..

 
 
$sql = "SELECT * FROM table1, table2, table3, table4 WHERE catagory = '$catagoryname'";
 
$sql = "SELECT * FROM table1, table2, table3, table4 WHERE table1.catagory = '$catagoryname' AND table2.catagory = '$catagoryname' AND table3.catagory = '$catagoryname' AND table4.catagory = '$catagoryname'";

Not sure how to do it. Any help would be greatly appreciated.
Thanks.

ronnie

[CeleronXL]

Run it as 4 separate queries.. easier.

[ronnie]

But I will still need to join the arrays wont I? I need the info from all 4 tables to write to a file.

ronnie

[CeleronXL]

Combine all of the arrays together in the end..?

array($array1, $array2, $array3, $array$)

[Egghead]

You could try the following.

<?php
$sql =  "SELECT * FROM table1, table2, table3, table4" .
        "WHERE table1.catagory = '$catagoryname', " .
        "table2.catagory = '$catagoryname', " .
        "table3.catagory = '$catagoryname', " .
        "table4.catagory = '$catagoryname'" .
?>

(I'm not sure if you need to seperate the conditionals with a comma - as I have done - or you need to put in an "OR" or even "||" whatever.)

... table2.catagory = '$catagoryname' || table3.catagory = '$catagoryname' || table...

[r937]

if the tables are so similar that they have the same columns, why aren't all the rows in just one table, with perhaps an identifying column to indicate what type of row it is (instead of which table it came from)

you will thank me the more complex your queries get...

rudy

[ronnie]

Thanks for the idea's. I was just wondering also, though there is data in the tables, there maybe some tables that might not have a matching value for "catagory", and my using AND, if one of them does not have a match, the whole statement would be false right? I am thinking I should be using OR instead.

The tables are very similar in data, with slight differences. The reason I have as many tables as i do, is because I perform regular tasks on certain data. I thought it would be faster and less load to go though a much smaller table than one big one with everything in it. This part I am working on now, will get much less use. Course I am not a mysql guru, so maybe it could be done better..

Thanks again!

ronnie

[r937]

the query to return data from table1 is

select foo1, bar1 from table1
where catagory = value

the query to return data from table2 is

select foo2, bar2 from table2
where catagory = value

if you want rows from both tables, you want UNION, not JOIN

ANDs/ORs does not come into it

when you do maintenance to table1 and not table2, it is just as efficient to do maintenance to one big table where rowtype='1'

[Egghead]

Hi Ronnie,
r937 has some straight forward points there and you should try re-designing your database to amalgamate the tables (and then use normalisation) in order to ease the strain on your queries later. Also, you can easily work with seperate queries to solve this problem and combine the results in PHP.
In the meantime though, keeping it all in one query... (By the way, if you are a real sucker for punishment then look at this sick individuals incredible query).
The UNION syntax is implemented in MySQL 4 onwards, so if your server is still 3.xx then consider upgrading to use this. If you are stuck on the versions of MySQL which will not handle UNIONs then there are a couple of options open to you:

• Use the OR conditional as I mentioned before (but use the revised code below)
• Create a temporary table on the MySQL server to hold the query results from several queries and then return them (effectively a UNION but without having to rely on the function being available)

Option 1

<?
$sql =  "SELECT * FROM " .
        "table1.catagory AS table1_cat, " .
        "table2.catagory AS table2_cat, " .
        "table3.catagory AS table3_cat, " .
        "table4.catagory AS table4_cat, " .
        "WHERE " .
        "table1.catagory = '$catagoryname' OR " .
        "table2.catagory = '$catagoryname' OR " .
        "table3.catagory = '$catagoryname' OR " .
        "table4.catagory = '$catagoryname'"
?>

Option 2

CREATE TEMPORARY TABLE tmp
SELECT * FROM table1 WHERE catagory = '$catagoryname';
INSERT INTO tmp
SELECT * FROM table2 WHERE catagory = '$catagoryname';
INSERT INTO tmp
SELECT * FROM table3 WHERE catagory = '$catagoryname';
INSERT INTO tmp
SELECT * FROM table4 WHERE catagory = '$catagoryname';
SELECT * from tmp;
DROP TABLE tmp;

[r937]

option 1 is a great big honking humungous cross-join

all combinations of rows from all four tables will be returned, as long as only one of them is in the right category

in other words, all rows of table 1, whether or not they are in the correct category, will be matched with the rows of table 2 that are in the right category, and so on...

UNION is what you want

option 2 is the best practice method for performing the union without, um, actually using a UNION

[ronnie]

Okay, maybe this is a dumb time to ask, but maybe my DB design is not the best? You all have me thinking now, I am sure anot a sql guru, just thought I had a good design.

Basically I am building a links script. Though there are many out there, as the saying goes, the best is the one you do yourself. So you can make it to your needs and you will know how it works. Plus I've never seen one that does what I wanted.

Anyways, I split my links in to the following tables:
pending (pending review)
current ( current active links)
mine ( my sites)
sponsor ( my sponsor/affiliate links)
toplist ( my links for the toplists I belong to)
banned ( of course people who cant submit anymore)

I made seperate tables because I would be preforming different tasks on different sets of links. I figured it would be easier to preform the tasks on small amounts of data. Go through one smaller table rather than one huge one. For example, when I would check current links(404, linkback), I would only need to check the "current" links table, not all the links in one table. Of course the other links, sponsor, mine, toplist, linkback and 404's would not have to be checked.

Now, where the problem comes in, the link pages are just include files, one for each catagory, included into the proper catagory page. Thats where the select comes in, to build the pages, I need to collect links from all link tables.

I also wonder if I have another problem, something that might be causing my select not to work. For all 4 tables, they do not have the same fields. Like current links have a field for link backs, where the rest dont. Or sponsor has a field for mouseovers, but the rest do not.

Maybe it's a dumb question, but since the tables in question do not all have the exact same fields, will this cause a problem with one select for all?

Maybe the whole set up is strange, but this is what I believe will work best for me. But maybe I should just have everything in one table? Just seems like a lot of extra, un-needed sql load? And this is the last part to make the script functional, so everything else works fine.

ronnie