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

    Question Unanswered: Simple question about ORDER BY

    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 doesn't 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
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select cars.*
    from cars INNER JOIN car_owners ON cars.car_id = car_owners.car_id
    order by car_owners.pr;

    Did you try the query using IN, as I don't think ORDER BY is permitted within a subquery of an IN.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Posts
    74
    THIS SHOULD WORK?

    SELECT CA.CAR_NAME,CA.PRIORTY FROM CARS CA, CAR_OWNERS CO
    WHERE
    CA.OWNER_ID = CO.ID
    ORDER BY
    CO.PRIORTY

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    select cars.*
    from cars INNER JOIN car_owners ON cars.car_id = car_owners.car_id
    order by car_owners.pr;
    That is the best way to do it.

    The only problem you may run into here is if you have a car that nobody currently owns. In that case you will want to use an outer join. Something to the effect of:

    SELECT cars.*
    FROM cars LEFT OUTER JOIN owners ON cars.car_id = car_owners.car_id
    ORDER BY car_owners.priority

  5. #5
    Join Date
    Dec 2003
    Posts
    74
    WOULD THAT NOT BE THE SAME AS

    SELECT CA.CAR_NAME,CA.PRIORTY
    FROM CARS CA, CAR_OWNERS CO
    WHERE
    CA.OWNER_ID(+) = CO.ID
    ORDER BYCO.PRIORTY

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by edwinjames
    WOULD THAT NOT BE THE SAME AS

    SELECT CA.CAR_NAME,CA.PRIORTY
    FROM CARS CA, CAR_OWNERS CO
    WHERE
    CA.OWNER_ID(+) = CO.ID
    ORDER BYCO.PRIORTY
    depends on the DB. I believe yours is Oracle specific?

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Given this statement, I do not believe owner_id would be a foreign key in the car's table, as it would be redundant.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Dec 2003
    Posts
    74
    Originally posted by Teddy
    depends on the DB. I believe yours is Oracle specific?
    yes apologies. mine was oracle specific.

Posting Permissions

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