SQL JOIN Query for Retrieving Pics in Multiple Categories

[DaveC426913]

I've got 3 tables:
Pics - table of my pictures
Categories - table of categories that pictures will fall into
PicsCategories - a many-to-many table relating pictures to categories. So the primary key of this table consists of a pic field and a category field

So now I want to provide a couple of categories as params and have it return all rows from the Pics table that are in those categories.

Table Pics
id Name
1 Bob
2 Mary
3 Jill
4 Jane

Table Categories
id Name
1 Male
2 Female
3 Parent

Table PicsCategories
Pic Cat
1   1
1   3
2   2
3   2
3   3
4   2
4   3

So if I supply Category1=2 OR Category2=3 (females and/or parents)
It should return Pic rows:
2
3
4
I'm looking at JOIN but JOIN is meant to extract data from two tables. I do not need ANY data from the PicsCategories table.

This is what I want:
SELECT UNIQUE(*) FROM Pics WHERE id=(SELECT Pic FROM PicsCategories WHERE Categories=2 OR Categories=3)

 

[DaveC426913]

Ah. It's a subquery.

SELECT DISTINCT * 
FROM Pics
WHERE id
IN (
SELECT Pic
FROM tblPicsCategories
WHERE Category=2
OR Category=3
)

Thanks for all your help! Couldn't have done it without you!

 

[DavidSnider]

Ah. It's a subquery.

SELECT DISTINCT * 
FROM Pics
WHERE id
IN (
SELECT Pic
FROM tblPicsCategories
WHERE Category=2
OR Category=3
)

Thanks for all your help! Couldn't have done it without you!

I think this works as well:

select distinct p.* from pics p, tblPicsCategories pc where p.ID = pc.pic and (pc.Category = 2 or pc.Category = 3)

 

[DaveC426913]

I think this works as well:

select distinct p.* from pics p, tblPicsCategories pc where p.ID = pc.pic and (pc.Category = 2 or pc.Category = 3)

What are the p and pc?

I mean, I get that p means pic and pc means piccategories but...

select distinct tblPic.* from pics tblPic, tblPicsCategories tblPicsCategories where tblPic.ID = tblPicsCategories.pic and (tblPicsCategories.Category = 2 or tblPicsCategories.Category = 3)

 

[silverfrost]

p and pc are called aliases in this case. Sometimes written with an 'as' i.e.

...from pics as p, ...

I prefer the 'as' because I think it's clearer. There are two reasons for using them

1. It's quicker
2. You need it if the same table appears twice as a join

i.e.

select a.name, b.name
from mytrees as a, mytrees as b
where a.parent_id=b.id

Where parent_id contains the id of the row that contains its parent. You need an alias just to refer to the individual tables.