🔒 Locked

[web] Joining three identical SQL tables...

Started by Coward Sep 19, 2006 at 6:26 PM 1 replies 1.2k views

Original Post

Sep 19, 2006 at 6:26 PM

An old rushed design descission, on a forum I run, was to seperate different forums into different tables and just read them seperately. These days around I've realised how insanely stupid that was and I'm looking to join the 3 tables into one. I've been fidling with MySQL the last couple of hours trying to get it to work, since I'm positive there must be a simpler way to do it than parsing it all through PHP. So far I've just been trying to SELECT everything, getting it to work proberly. After starting with "SELECT * FROM 03_forum, 04_forum", which didn't work, I stumpled upon this, which seems to be what I'm looking for. Unfortunately I'm a SQL newbie, can't make sence of the documentation, and need some help from you good people. So far I've got: "SELECT * FROM 03_forum JOIN 04_forum" - This produces the same result as above: all the data in one sheet, but instead of just adding all the rows, it's just created 7 new fields and added the results with same IDs together and dublicated where nescessary. Then I fell in love with NATURAL JOIN which seems to do a task akin to what I need: "SELECT 03_forum NATURAL JOIN 04_forum" - This returns nothing. I figure I need to add the USING part, but everytime I do this, MySQL complains about syntax errors: "SELECT * FROM 03_forum NATURAL JOIN 04_forum USING bruger" - Gives me this:

Quote:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM 03_forum JOIN 04_forum USING bruger

Any insights?

lemurion

Sep 19, 2006 at 6:38 PM

I think what you are looking for is the UNION ALL keyword. This allow to make multiple queries and get back only one result set as long as all your columns have the same type in each query.

ex (assuming 03_forum and 04_forum are identical:

select * from 03_forum where <condition> union all select * from 04_forum where <condition>

only replace the to a where clause and it will work.
note: the condition doesn't need to be the same on all tables.

hope that helps

edit:
If you don't want to have duplicates between tables, don't use the ALL keyword for the union. It will only returns the rows that are different from the ones already returned.

Mathachew

Sep 19, 2006 at 7:10 PM

It sounds like you need a SELECT statement with a UNION in it. It helps if you are only needing specific information and that the field names from the tables are matching, to cause less confusion.

Here's a query I run to dynamically build an RSS feed when called:

SELECT	'news' as type,	id,	title,	description,	UNIX_TIMESTAMP(added) AS timestampFROM	newsWHERE	rss = 1UNION SELECT	'newsletter' as type,	id,	title,	description,	UNIX_TIMESTAMP(added) AS timestampFROM	newsletterWHERE	rss = 1UNION SELECT	'faq' as type,	id,	title,	description,	UNIX_TIMESTAMP(added) AS timestampFROM	faqWHERE	rss = 1ORDER BY	timestamp DESCLIMIT 10;

The first column, 'blah' as type, helps identify which table this data is being pulled from, so my results in PHP show $array['type'] as either news, newsletter, or faq. The next three columns function the same in all three tables, the unique ID (for that table), the title row, and the description of that row. UNIX_TIMESTAMP converts the TIMESTAMP field into a Unix timestamp so that in my PHP I can get whatever date/time data I need where it is applicable.

I hope this helps clear up what you're trying to do. I could be mistaken about what you're trying to accomplish, but I got the impression that this would assist you.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.