我应该在这里使用什么样的JOIN？

I'm new to JOINS in MySql.

我是MySql中的JOINS新手。

I have six tables: t1, t2, t3, t4, t5, t6. And I also have one main table: main_table. TOTAL 7 TABLES!

我有六个表：t1，t2，t3，t4，t5，t6。我还有一个主表：main_table。共7个表格！

The first column of ALL tables is called classified_id.

ALL表的第一列称为classified_id。

If the user searches for "cars" then the main table will match everything in table t1 (which is the cars table) where classified_id is the same in both tables.

如果用户搜索“cars”，则主表将匹配表t1（即cars表）中的所有内容，其中classified_id在两个表中相同。

So:

所以：

   SELECT * FROM main_table, t1 WHERE main_table.classified_id=t1.classified_id

This works fine, although I am not sure this is the way to join here. Performance is an issue in my case!

这很好，虽然我不确定这是加入这里的方式。在我的情况下，性能是一个问题！

However, here is my problem. Whenever ALL CLASSIFIEDS are searched, then I need to match the main_table.classified_id to the other tables classified_id column and get every classified there is.

但是，这是我的问题。每当搜索到所有CLASSIFIEDS时，我需要将main_table.classified_id与其他表的classified_id列相匹配，并获得每个分类。

How should this query be made up?

该查询应该如何组成？

    SELECT * FROM main_table, t1, t2, t3, t4, t5, t6 // I have this so far which is not much!

If you need more input just ask and I will update this Q.

如果您需要更多输入，请询问，我将更新此Q.

Thanks

谢谢

EDIT: Table setup:

编辑：表格设置：

     main_table:              t1:
     ID(PK)                   ID (PK)
     classified_id -> 25      classified_id     ->   25
     category  -> CARS        year     ->    1997

2 个解决方案

#1

If the row would exist in all tables (i.e. every table has a row for a specific classified_id), then you would use an inner join:

如果所有表中都存在该行（即每个表都有一个特定classified_id的行），那么您将使用内部联接：

SELECT 
   m.classified_id
   ,m.category
   ,t1.year
   ,........
FROM 
   main_table m
   INNER JOIN t1 ON m.classified_id = t1.classified_id
   INNER JOIN t2 ON m.classified_id = t2.classified_id
   INNER JOIN t3 ON m.classified_id = t3.classified_id
   INNER JOIN t4 ON m.classified_id = t4.classified_id
   INNER JOIN t5 ON m.classified_id = t5.classified_id
   INNER JOIN t6 ON m.classified_id = t6.classified_id

If the row does not exist in every table, then you'd use LEFT JOINS so that rows are not dropped

如果每个表中都不存在该行，那么您将使用LEFT JOINS以便不删除行

#2

Use:

使用：

   SELECT mt.*,
          t1.*,
          t2.*,
          t3.*,
          t4.*,
          t5.*,
          t6.*
     FROM MAIN_TABLE mt
LEFT JOIN TABLE_1 t1 ON t1.classified_id = mt.classified_id
LEFT JOIN TABLE_2 t2 ON t2.classified_id = mt.classified_id
LEFT JOIN TABLE_3 t3 ON t3.classified_id = mt.classified_id
LEFT JOIN TABLE_4 t4 ON t4.classified_id = mt.classified_id
LEFT JOIN TABLE_5 t5 ON t5.classified_id = mt.classified_id
LEFT JOIN TABLE_6 t6 ON t6.classified_id = mt.classified_id

I used LEFT JOINs because if JOIN was used - records would be omitted that did not have a supporting record in at least one of the t1/2/3/4/5/6 tables. You might find this link helpful for understanding JOINs.

我使用了LEFT JOIN，因为如果使用了JOIN - 将省略在至少一个t1 / 2/3/4/5/6表中没有支持记录的记录。您可能会发现此链接有助于理解JOIN。

#1