MYSQL使用2个关系表加入2个表

Hi I need to get data from a database but I can't figure out how do do it as is...

嗨,我需要从数据库中获取数据,但我无法弄清楚它是如何做到的......

They have "boutiques" tables and "boutiques_categories" and a relation table between them name "boutiques_categories_categories"

他们有“精品店”桌子和“boutiques_categories”,他们之间的关系表名称为“boutiques_categories_categories”

here how they are setup :

这里是如何设置的:

boutiques
id    name
1     X_boutiques2_name
2     Y_boutiques_name

Boutique2
id   name
1    X_boutiques2_name
2    Y_boutiques2_name

boutiques_categories
id   name
1    X_categorie_name
2    Y_categorie_name

boutiques_categories_categories
boutique_id              categorie_id
X_boutique_id            X_categorie_id
Y_boutique_id            Y_categorie_id

I want to create a SQL to merge and print a CSV of the data like so :

我想创建一个SQL来合并和打印数据的CSV,如下所示:

export
boutiques_id    boutique_name     boutiques_categories_categorie_name
boutiques2_id   boutique2_name    boutiques_categories_categorie_name

I trying UNION those 2 table

我试着UNION那两张桌子

SELECT A.* 
FROM boutiques A 
UNION select B.* FROM boutiques2 B

And it's working but now I need to Join the categorie name to it and I can't figure out how to do it. Tried :

它正在工作,但现在我需要加入类别名称,我无法弄清楚如何做到这一点。试过:

SELECT A.* 
FROM boutiques A 
UNION select B.* 
FROM boutiques2 B 
 left join 
  boutiques_categories BC ON BC.id = 
   (
     SELECT BCC.categorie_id 
     FORM 
      boutiques_categories_categories BCC 
     WHERE BCC.boutique_id = BC.id
   )

But I get MYSQL error that I can't resolve.

但我得到MYSQL错误,我无法解决。

#1242 - Subquery returns more than 1 row

Thank for your time.

谢谢你的时间。

1 个解决方案

#1

You can union (or union all, depending on what data you want) boutiques and Boutique2 into a derived table, and then join that to boutiques_categories through boutiques_categories_categories.

您可以将精品店和精品店2联合(或联合所有,具体取决于您想要的数据)到派生表中,然后通过boutiques_categories_categories将其加入boutiques_categories。

The table data that you included in your question doesn't seem completely accurate though. Specifically: boutiques_categories_categories containing boutique_id and categorie_id values that don't match the id columns of the associated tables. I'm just going to assume that boutiques_categories_categories have int ID values. If this is not the case, please clarify in the comments, and I can adjust the query appropriately.

您在问题中包含的表格数据似乎并不完全准确。具体来说:boutiques_categories_categories包含与相关表的id列不匹配的boutique_id和categorie_id值。我只是假设boutiques_categories_categories有int ID值。如果不是这种情况,请在评论中说明,我可以适当调整查询。

Give this query a try, and see if it returns the data that you expect:

尝试使用此查询,看看它是否返回您期望的数据:

select 
    b.id, b.name, c.name
from
    -- Get the boutiques rows from the unioned tables
    (
        select id, name from boutiques
        union
        select id, name from Boutique2
    ) as b
    -- Join in boutiques_cateogires_categories
    join boutiques_categories_categories cc
        on (b.id=cc.boutique_id)
    -- Join in boutiques_categories
    join boutiques_categories c
        on (cc.categorie_id=c.id)

#1