www.webdeveloper.com
Results 1 to 3 of 3

Thread: To Join or not to Join

Hybrid View

  1. #1
    Join Date
    Feb 2012
    Location
    Tallahassee, FL
    Posts
    280

    To Join or not to Join

    Ok, so I have two tables. We shall call them dudes and dudes_cars. What I need is to get all the dudes, and if a dude is lucky enough to have a car, get his car too.
    Example, say the tables are like this:
    |----------dudes----------------|
    |----dude_id---|---dude_name---|
    |--------1------|----Bob--------|
    |--------2------|----Joe---------|
    |--------3------|----Sam--------|
    |--------------------------------|

    |-----------dudes_cars-----------------------|
    |-car_id-|owner_dude_id|--car_name--|-year-|
    |----1---|-----1--------|--zoomer----|-2012-|
    |----2---|-----3--------|--pinto------|-2000-|
    |--------------------------------------------|

    Ok, we want to get ALL the 'dudes' no matter what. But we only want to see their car i it was made in 2012. I am currently using a query much like this:
    Code:
    SELECT dudes.dude_id, dudes.dude_name, dudes_cars.car_name FROM dudes LEFT JOIN dudes_cars ON (dudes_cars.owner_dude_id = dudes.dude_id AND dudes_cars.year = 2012);
    This gives me all the dudes, even if they dont have a car, but it doesnt seem to check the year.

    Also, I apologize for the over use of the word dude, but it was the first work to pop in my head and didnt realize it was as annoying as it is until I was halfway done setting up the example.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,247
    Not positive, but I think if you move the year check into the where clause instead of the join's on() clause, you'll get what you're looking for.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Your query...

    Code:
    SELECT 
      dudes.dude_id, 
      dudes.dude_name, 
      dudes_cars.car_name 
      FROM dudes 
      LEFT JOIN dudes_cars 
        ON (dudes_cars.owner_dude_id = dudes.dude_id AND dudes_cars.year = 2012);

    As NogDog said, the 2012 part goes in the WHERE clause

    Code:
    SELECT 
      dude_name,
      car_name,
    FROM dudes LEFT JOIN dudes_cars 
      ON (owner_dude_id = dude_id)
    WHERE `year` = 2012;
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles