I cannot figure out a real easy query using 2 tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikeman
Forum Newbie
Posts: 18
Joined: Wed Apr 07, 2010 7:18 am

I cannot figure out a real easy query using 2 tables

Post 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...
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: I cannot figure out a real easy query using 2 tables

Post 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
mikeman
Forum Newbie
Posts: 18
Joined: Wed Apr 07, 2010 7:18 am

Re: I cannot figure out a real easy query using 2 tables

Post by mikeman »

That worked perfectly - thanks very much!

What do the i. and c. mean?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: I cannot figure out a real easy query using 2 tables

Post 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.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: I cannot figure out a real easy query using 2 tables

Post 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;
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: I cannot figure out a real easy query using 2 tables

Post 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
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: I cannot figure out a real easy query using 2 tables

Post 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!
Post Reply