Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: Inner joins on a table with two foreign keys

    I've got a problem that I just can't seem to work out, any help would be much appreciated. The DB server is MySQL. I have the following three tables:

    Categories
    -----------
    cid (pk)
    category


    Merchants
    -----------
    mid (pk)
    merchant


    Products
    -----------
    pid (pk)
    cid (fk)
    mid (fk)
    product


    Basically, I want to search for all products whose category is television, the following SQL works fine for that:

    SELECT p.product
    FROM products AS p
    INNER JOIN categories AS c USING (cid)
    WHERE c.categories = 'television'

    However, I would like to also display the merchant who is selling the television, so I added another inner join like so:

    SELECT p.product
    FROM products AS p
    INNER JOIN categories AS c USING (cid)
    INNER JOIN merchants AS m USING (mid)
    WHERE c.categories = 'television'

    but unfortunately I get the following error message:

    "MySQL said: Unknown column 'temp.c.mid' in 'on clause'"

    I think the problem is that I can't do two inner joins on a table if it has got two foreign keys on it, is there a way around this.

    Many thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I'm not familiar with MySQL but your statement looks strange (I'm not saying it's wrong). What's strange to me is you specify what column to use for join, but not what table this column belongs to. Then if you have 2 tables with the same column it can't work. Hopfully ANSI SQL is supported by MySQL then one of the following could work:
    Code:
    SELECT p.product 
    FROM products AS p
    INNER JOIN categories AS c ON c.cid = p.cid
    INNER JOIN merchants AS m ON m.mid = p.mid 
    WHERE c.categories = 'television'
    Code:
    SELECT p.product 
    FROM products AS p, categories AS c, merchants AS m
    WHERE c.cid = p.cid
    AND m.mid = p.mid 
    AND c.categories = 'television'

  3. #3
    Join Date
    Oct 2005
    Posts
    26
    Thanks, you were right. It was because I was using "USING (cid)" instead of "ON c.cid = p.cid", although it worked fine for a table with one foreign key.

    Both of your examples worked ok.

    Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •