CODEWITHSUNDEEP
mysqlsql
Naruto77
Naruto77

Reputation: 33

mysql join multiples

Using the products table as the master, I want to list all products. If the product has a description in table prod_desc, I want to pull it in. Also, if the product has a manufacturer description, I want to pull it in. I want the product row to display once, whether it has a prod description, manufacturer description, one, both or neither.

A left join works well for one external lookup, but the second one messes it up.

SELECT * FROM t1
LEFT JOIN t3 ON t1.a=t3.a
LEFT JOIN t2 ON t1.b=t2.b

Upvotes: 0

Views: 80

Answers (2)

Thomas
Thomas

Reputation: 64635

You will need to "roll up" the descriptions in case there are multiple product descriptions for the same product or multiple manufacturer descriptions for the same product.

Select P.Id, P...
    , Group_Concat( PD.desc ) As DescriptionList
    , Group_Concat( PMD.desc ) As MfrDescriptionList
From products As P
    Left Join prod_desc As PD
        On PD.product_id = P.id
    Left Join prod_mfr_desc As PMD
        On PMD.product_id = P.id
Group By P.id

Upvotes: 1

MJB
MJB

Reputation: 7686

It looks to me like your "select *" might be the issue. but I cannot tell for sure what you mean by "mess it up." Try to select only what you want, and see if that solves it.

For example,

SELECT a, b, c, d, e
FROM t1
  LEFT JOIN t3 
    ON t1.a = t3.a
  LEFT JOIN t2
    ON t1.b = t2.b

Upvotes: 0

Related Questions