Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Location
    Denmark
    Posts
    5

    Question Unanswered: Simple question regarding SQL

    Hi,

    Imagine the following case:

    We have 2 tables:

    cars: Contains a list of cars
    car_owners: Contains a list of car owners

    Every car owner is capable of owning one car and in the 'car_owners' table we have foreign key (fk_car_id) to the owners car in the 'cars' table.

    I hope you're able to keep up so far!

    Now, I can simply fetch a list of car owners ordered by their priority with this SQL:

    SELECT * FROM car_owners ORDER BY priority

    Easy! But what I really, really want, is to fetch a list of cars ordered by the priority of their owners!

    The following doesn't work:

    SELECT * FROM cars WHERE id IN ( SELECT fk_car_id FROM car_owners ORDER BY priority )

    Because the IN-clause does guarantee this! The cars are just returned in any order if their id is part of the subquery!

    What am I missing here???

    I Hope somebody can help me out of my confusion about this!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SELECT cars.*
    FROM cars
    LEFT OUTER JOIN car_owners on cars.id = fk_car_id
    order by car_owners.priority

    The left join is only necessary if you want to include cars without owners. Otherwise an inner join will do.

    One of the most frequent SQL mistakes is connecting datasets with WHERE clauses. Use joins whenever possible.

    blindman

  3. #3
    Join Date
    Jul 2002
    Location
    Denmark
    Posts
    5
    Originally posted by blindman
    SELECT cars.*
    FROM cars
    LEFT OUTER JOIN car_owners on cars.id = fk_car_id
    order by car_owners.priority

    The left join is only necessary if you want to include cars without owners. Otherwise an inner join will do.

    One of the most frequent SQL mistakes is connecting datasets with WHERE clauses. Use joins whenever possible.

    blindman
    Thanx! But I simplified the example a bit to make it easier to discuss, and now I can't quite make your answer fit in my solution...

    If, for instance, I'd only want to the cars of the 3 top most important car owners som a certain area... my (error prone) SQL would look like this:

    SELECT *
    FROM cars
    WHERE (id IN
    (SELECT TOP 3 fk_car_id
    FROM car_owners
    WHERE zipcode = '0000'
    ORDER BY priority))


    How should I translate that into using an INNER JOIN?

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    SELECT top 3 cars.*,car_owners.*
    FROM cars
    LEFT OUTER JOIN car_owners on cars.id = fk_car_id
    where car_owners.zip_code = '23434'
    order by car_owners.priority
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jul 2002
    Location
    Denmark
    Posts
    5
    Originally posted by Enigma
    SELECT top 3 cars.*,car_owners.*
    FROM cars
    LEFT OUTER JOIN car_owners on cars.id = fk_car_id
    where car_owners.zip_code = '23434'
    order by car_owners.priority
    Uh, uh, uh! This baby seems to do the trick!

    So simple, that I should've figured it out!

    Thanx!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by Enigma
    SELECT top 3 cars.*,car_owners.*
    FROM cars
    LEFT OUTER JOIN car_owners on cars.id = fk_car_id
    where car_owners.zip_code = '23434'
    order by car_owners.priority
    if you put a condition on the right table (in this case car_owners) into the WHERE clause, then you will never get unmatched rows, so LEFT OUTER is unnecessary, you might as well code INNER

    if you want all cars and any owners in zip 23434 then you have to keep LEFT OUTER but move that condition from WHERE to ON

    however, enigma's question, "I'd only want to the cars of the 3 top most important car owners from a certain area" does obviously require an inner join, so the above works, but it's certainly not how you would "figure it out"



    rudy
    http://r937.com/

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    good thinking rudy !!!

    obviously I ve had a long day today ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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