0

I have a pretty basic question that I imagine would require a join to pull off. However I am very new to mysql! Here is what I need to pull off:

I have 3 tables:

Products, attributes, and products_to_attributes

What I need to do is select the attributes which are associated to the product I want to query, based off the products_to_attributes table.

So lets say my product id=1, I need to grab all the rows in the attributes tables based off the products_to_attributes table which simply holds the pid to aid.

Greg Smith
  • 5
  • 1

2 Answers2

1
SELECT a.*
FROM attributes a
INNER JOIN products_to_attributes pa ON a.aid = pa.aid
WHERE pa.pid = 1
tofutim
  • 22,664
  • 20
  • 87
  • 148
  • this has me confused. what is a, and pa? – Greg Smith Jun 12 '11 at 21:21
  • Aliases for the table, to not have to type the entire tablename each time. The syntax is `SELECT a.ID from attributes AS a`, but you can omit the `AS`. – Konerak Jun 12 '11 at 21:22
  • @Greg, and if you want to access the columns inside `products` just add another `INNER JOIN products p ON (pa.pid = p.pid)` after the first join criterion. – Johan Jun 12 '11 at 21:40
-1
SELECT a.*
FROM attributes a, products_to_attributes  b
WHERE a.aid = b.aid
AND b.pid = 1

it's basically the same answer as tofutim, but without using the INNER JOIN syntax.

Community
  • 1
  • 1
gion_13
  • 41,171
  • 10
  • 96
  • 108
  • The INNER JOIN syntax is preferred over the ANSI-89 style. – Konerak Jun 12 '11 at 21:23
  • And using implicit `where` join will get you downvotes on SO. – Johan Jun 12 '11 at 21:24
  • http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins i donno, it feels that it's somewhat of a preference – gion_13 Jun 12 '11 at 21:31
  • it is **not** something of a preference. Implicit `where` joins are confusing and error-prone because you do not separate the `join` conditions from the filters in the `where` condition. On big joins with multiple tables and complex join criteria this quickly gets out of hand. – Johan Jun 12 '11 at 21:38