mysql - JOIN (table name)

Question

I have database with a table called sites (which has a few fields, including a site_ID field), and a set of tables for each site_ID entry from the sites table.

so the db looks something like:

- sites [site_ID, blah]
- site_1_articles [title, content]
- site_1_comments [author, comment]
- site_2_articles [title, content]
- site_2_comments [author, comment]
- site_3_articles [title, content]
- site_3_comments [author, comment]
etc...

I have a select query which pulls out all the fields from the sites table:

SELECT * FROM sites
WHERE ....

How can I also get the title field from the appropriate site_[site_ID]_articles table ? I thinking I need this in the same query using JOIN because the number of tables is quite large (over 1000), and doing 1000 selects is probably not the way to do this :)

Answer 1

The best is to redesign your database. a table articles with a id column as primary key and a table comments with an article_id column. Then you join on that.

Answer 2

No, you cannot reference tables dynamically (unless using a stored proc to create prepared statements)

You should design your database, to hole a single site_articles table, which will store a site_id column, then in your query:

SELECT *
FROM sites s JOIN site_articles sa ON s.site_id = sa.site_id
WHERE ....

Answer 3

SELECT blah, some_id, second_table.some_field FROM first_table
LEFT JOIN second_table
ON second_table.some_field = some_id
WHERE ....

Is that what you wany?

Answer 4

You can join on multiple conditions, or add it to the WHERE clause

...
ON (blah = boo) AND (some_id = matching_field_in_second_table)
...

or

...
WHERE (some_id = matching_field_in_second_table)