The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

MySQL join syntax..

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..

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)

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..

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)

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.

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.