Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: Conditional join

    Code:
    Parts
    --------
    ID	PartID	PartType
    1	1	Car
    2	1	Bike
    3	2	Car
    4	2	Bike
    
    CarParts
    --------
    ID	Price
    1	13.50
    2	7.50
    
    BikeParts
    ---------
    ID	Price
    1	4.50
    2	45.50
    I wanna Select PartID, PartType and Price. But can't figure out how to join conditionally.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select a.id,b.price
    from parts a,carparts b
    where a.partid = b.id
    and parttype = 'Car'
    union all
    select a.id,b.price
    from parts a,bikeparts b
    where a.partid = b.id
    and parttype = 'Bike'
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2011
    Posts
    75
    Quote Originally Posted by gunbilegt View Post
    Code:
    Parts
    --------
    ID	PartID	PartType
    1	1	Car
    2	1	Bike
    3	2	Car
    4	2	Bike
    
    CarParts
    --------
    ID	Price
    1	13.50
    2	7.50
    
    BikeParts
    ---------
    ID	Price
    1	4.50
    2	45.50
    I wanna Select PartID, PartType and Price. But can't figure out how to join conditionally.
    Hello,

    Can you tell me the relation ship between these tables?

  4. #4
    Join Date
    Nov 2011
    Posts
    1
    I think Id in second(carpart) and third(bikepart) table acts as foreign key for the main table that is parts table.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gunbilegt View Post
    Code:
    Parts
    --------
    ID	PartID	PartType
    1	1	Car
    2	1	Bike
    3	2	Car
    4	2	Bike
    this is poor design

    change it to this --
    Code:
    Parts
    --------
    ID   CarPartID   BikePartID
    1       1           NULL
    2      NULL          1
    3       2           NULL
    4      NULL          2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2008
    Posts
    40
    Thanks guys

Posting Permissions

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