Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Sql join?

  1. Oct 23, 2009 #1


    User Avatar
    Gold Member

    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.

    Code (Text):

    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:
    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)
    Last edited: Oct 23, 2009
  2. jcsd
  3. Oct 23, 2009 #2


    User Avatar
    Gold Member

    Ah. It's a subquery.

    Code (Text):

    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!
  4. Oct 23, 2009 #3


    User Avatar
    Gold Member

    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)
  5. Oct 23, 2009 #4


    User Avatar
    Gold Member

    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)
  6. Oct 24, 2009 #5
    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


    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.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook