Hi,

Can I do filtering based on Table 2 where I have only a select statement on Table 1, by using the WHERE part of the statement? E.g. I have a framework which creates a view of People, but you can pass the WHERE clause. So filtering for attributes that are on the People table is OK.

   SELECT * FROM `people` WHERE (cars_id IS NOT NULL) ________A

BUT what I really want to do is get a filtered list of People for which they have a registered car. This would normally be possible by:

    SELECT * FROM `people` LEFT OUTER JOIN `cars` ON `cars`.id = `people`.cars_id WHERE (cars.registered = 1); ________B

However I only have access to the WHERE cause. So is there anyway to modify the SQL for the WHERE clause in the first SQL statement above (A) such that I can do such filtering?

Any ideas?

thanks

Why do you think you need a LEFT JOIN?

The LEFT JOIN will get *all* people, no matter whether they are associated with any CAR or not.

Besides, you said that you "only have access to the WHERE cause," so how would you be able to specify which kind of JOIN is used???

Anyway, yes, I think you could do this with *ONLY* a WHERE clause.

whereClause := cars_id IN ( SELECT id FROM cars WHERE registered = 1 )

That is, your full query would become


But since I'm pretty sure this isn't your *real* problem, you may need to show real code instead of fake code to get a real answer.

thats exactly what I was fishing for Old Pendant. thanks!

the background is working around the constraint of active scaffold, for Ruby on Rails. http://activescaffold.com/

thanks again