SQL Query Help

[Dubya008]

I have 2 tables

 

Attributes

id,Attribute

1,Men's

2,Women's

3,Color

4,Size

5,Fun!!

6,Boring

7,Awesome

 

Products_Attributes

id,Product,Attribute

1,      1,        1

2,      1,        4

3,      2,        1

4,      2,        5

5,      1,        3

6,      2,        6

7,      2,        7

 

I'm trying to pull all of the data from the Attributes table next to all of the data product data for a particular product.

I've tried using left joins but am having a tough time with it.

 

So far this is what I have but it is only pull the attributes for the product.

SELECT Attributes.Attribute, Products_Attributes.Product

FROM Attributes

LEFT JOIN Products_Attributes

ON Attributes.id=Products_Attributes.Attribute

WHERE Products_Attributes.Product ='1'

 

What I am getting is:

Attribute,Product

Men's,        1

Size,          1

Color,        1

 

Anyone know how to pull all the Attributes and only one product?

 

 

[kickstart]

Hi

 

I presume what you want is a row per product with a field containing all the attributes for that product.

 

If so you can use GROUP_CONCAT.

 

SELECT Products_Attributes.Product, GROUP_CONCAT(Attributes.Attribute)
FROM Attributes
LEFT JOIN Products_Attributes
ON Attributes.id=Products_Attributes.Attribute
WHERE Products_Attributes.Product ='1'

 

All the best

 

Keith

[fenway]

Just be careful about the max_length of group-concat.

[Dubya008]

KickStart that was helpful and can be used in another spot for something else so Thank you for that. However that wasn't quite what I was looking for. I need to have the following

 

Attribute  Product

Men's              1

Women's     

Color              1

Size                1

Fun!!

Boring

Awesome

 

I don't know if that makes sense to anyone but me. I just want all of the Attriubutes on the left and on the right the specific product that we're looking at.

[mikosiko]

try this... could be what you want .. I did include your table Product as you probably will like to display the Product name  (query  no tested)

 

SELECT a.Attribute,
       pa.Product,
       pr.Product_Name
FROM Attributes a
  LEFT JOIN Products_Attributes pa ON pa.attribute = a.id
  LEFT JOIN Products pr ON pr.product_id = pa.product
WHERE pr.product_id ='1'

[kickstart]

Hi

 

Think the above is close but it will only return the rows where the product is 1, where it seems you want all rows, with those where the product is 1 having the product id.

 

This is similar but should give you what you want (difference is the product check is done before the JOIN is carried out).

 

SELECT a.Attribute,
       pa.Product,
       pr.Product_Name
FROM Attributes a
  LEFT JOIN Products_Attributes pa ON pa.attribute = a.id AND pa.Product = '1'
  LEFT JOIN Products pr ON pr.product_id = pa.product

 

All the best

 

Keith

[Dubya008]

Wow that worked like a charm. Thanks so much for the help guys. My final Query is below. I didn't need the Product Name as that will be already displayed on the screen in other places.

 

SELECT Attributes.Attribute, Products_Attributes.Product

FROM Attributes

LEFT JOIN Products_Attributes

ON Products_Attributes.Attribute = Attributes.id AND Products_Attributes.Product = '1'