Jump to content
  • Advertisement
Sign in to follow this  
Coward

[web] Joining three identical SQL tables...

This topic is 4409 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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?

Share this post


Link to post
Share on other sites
Advertisement
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 <condition> 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.

Share this post


Link to post
Share on other sites
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 timestamp
FROM
news
WHERE
rss = 1
UNION SELECT
'newsletter' as type,
id,
title,
description,
UNIX_TIMESTAMP(added) AS timestamp
FROM
newsletter
WHERE
rss = 1
UNION SELECT
'faq' as type,
id,
title,
description,
UNIX_TIMESTAMP(added) AS timestamp
FROM
faq
WHERE
rss = 1
ORDER BY
timestamp DESC
LIMIT 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.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!