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!

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.

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

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

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?

Given this statement, I do not believe owner_id would be a foreign key in the car's table, as it would be redundant.

yes apologies. mine was oracle specific.