Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
  1. #1
    JR
    JR is offline
    Senior Member JR's Avatar
    Join Date
    Nov 2002
    Location
    UK
    Posts
    4,354
    Member #
    257
    Is it possible to create a join between a field in one table and two fields in another?

    Example:

    A database contains two tables: Fruits and Fruit_Bowls.

    Fruits contains Fruit_ID (primary key, numerical) and Fruit_Name.

    Fruit_Bowls contains Fruit_Bowl_ID (primary key), Fruit_1_ID (relates to Fruit_ID in Fruits table), Fruit_2_ID (as before).

    Code:
    FRUITS
    Fruit_ID | Fruit_Name
    ---------------------
    1        | Apple
    2        | Orange
    3        | Pear
    
    FRUIT_BOWLS
    Fruit_Bowl_ID | Fruit_1_ID | Fruit_2_ID
    ---------------------------------------
    1             | 1          | 2
    So how would I query the database to see what is inside fruit bowl #1 and return Apple (ID#1) and Orange (ID#2)?

    Cheers all
    JR

  2.  

  3. #2
    Senior Member james's Avatar
    Join Date
    May 2003
    Location
    Melbourne, AUSTRALIA
    Posts
    364
    Member #
    1352
    In my limited experience, normally this data would be laid out in three tables...

    Code:
    FRUITS
    Fruit_ID | Fruit_Name
    ---------------------
    1        | Apple
    2        | Orange
    3        | Pear
    
    FRUIT_BOWLS
    Fruit_Bowl_ID  
    -------------
    1
    
    FRUIT_BOWL_CONTENTS
    Fruit_Bowl_Contents_ID | Fruit_Bowl_ID | Fruit_ID
    -------------------------------------------------
    1                      | 1             | 1
    2                      | 1             | 2
    It's still an interesting problem, I'm just providing perspective.
    James H
    Home Page · Mars Page · www.fihsf1.net (formerly www·fihs·net)

  4. #3
    JR
    JR is offline
    Senior Member JR's Avatar
    Join Date
    Nov 2002
    Location
    UK
    Posts
    4,354
    Member #
    257
    After a bit of research, I have found the solution, so nevermind.

    For reference:

    Code:
    CREATE TEMPORARY TABLE `Fruits_tmp`
    SELECT `Fruit_ID`, `Fruit_Name` FROM `Fruits`;
    
    SELECT Fruits.Fruit_Name, Fruits_tmp.Fruits FROM
    Fruits, Fruits_tmp, Fruit_Bowls WHERE
    (Fruit_Bowls.Fruit_1_ID = Fruits.Fruit_ID) AND
    (Fruit_Bowls.Fruit_2_ID = Fruits_tmp.Fruit_ID) AND
    Fruit_Bowls.Fruit_Bowl_ID = 1;
    Edit: Thanks James, but that would present the same problem wouldn't it?

    I was thinking about something along them lines as it is a many-to-many relationship, but storing two tables containing fruits and using fruit_bowls as a link table would be a waste of space.
    JR

  5. #4
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    select a.fruit_name,
    b.fruit_name
    from fruits b, fruits a, fruit_bowls x
    where x.fruit_bowl_id = 1
    and a.fruit_id = x.fruit_1_id
    and b.fruit_id = x.fruit_2_id;

    Sean

  6. #5
    JR
    JR is offline
    Senior Member JR's Avatar
    Join Date
    Nov 2002
    Location
    UK
    Posts
    4,354
    Member #
    257
    I didn't think of trying that, nice one
    JR


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 11:54 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com