Page 1 of 1
I cannot figure out a real easy query using 2 tables
Posted: Fri Jun 11, 2010 9:28 am
by mikeman
I have 2 tables:
product_image (contains columns = product_image_id, product_id, image)
and
product_to_category (contains columns = product_id, category_id)
All I want to do is select the image field in the first table for every record that has a category id of 35 in the second table.
Thanks...
Re: I cannot figure out a real easy query using 2 tables
Posted: Mon Jun 14, 2010 11:54 am
by andyhoneycutt
This should do the trick...
Code: Select all
SELECT i.image FROM product_image i
JOIN product_to_category c ON i.product_id = c.product_id
WHERE c.category_id = 35;
-Andy
Re: I cannot figure out a real easy query using 2 tables
Posted: Tue Jun 15, 2010 6:47 am
by mikeman
That worked perfectly - thanks very much!
What do the i. and c. mean?
Re: I cannot figure out a real easy query using 2 tables
Posted: Tue Jun 15, 2010 9:43 am
by Weirdan
mikeman wrote:What do the i. and c. mean?
Aliases for table names - you could also use full table names instead: product_image. and product_to_category.
Re: I cannot figure out a real easy query using 2 tables
Posted: Tue Jun 15, 2010 9:44 am
by andyhoneycutt
If you look at the query, you can see that I'm selecting from product_image
i and joining on product_to_category
c. The "i" and "c" are just aliases, or short-hand notation, for the tables that we're looking at. In this case, I used "i" for "image" and "c" for "category". Does that make sense?
Code: Select all
SELECT i.image FROM product_image i
JOIN product_to_category c ON i.product_id = c.product_id
WHERE c.category_id = 35;
Re: I cannot figure out a real easy query using 2 tables
Posted: Tue Jun 15, 2010 3:08 pm
by Eran
to make it clearer, common practice is to use 'AS' between the table/column and its alias.
Code: Select all
product_image AS i ...
product_to_category AS c
Re: I cannot figure out a real easy query using 2 tables
Posted: Tue Jun 15, 2010 3:21 pm
by andyhoneycutt
pytrin wrote:to make it clearer, common practice is to use 'AS' between the table/column and its alias.
Code: Select all
product_image AS i ...
product_to_category AS c
Good call. I'm too lazy sometimes!