CODEWITHSUNDEEP
sqlms-access
sigil
sigil

Reputation: 9546

How to left join a Cartesian product on another table?

Using Access 2010. Suppose I have three tables: dogs, cats, and catChases. My goal is to create a query that will tell me, for each dog and each cat, whether or not that dog has chased that cat.

I thought I'd have to use a Cartesian product of dogs and cats, because I want the status for all possible combinations, and then left join catChases, as follows:

select
dog,cat,chase
from
dogs,cats
left join
catChases
on
dogs.dog=catChases.dog

but that just gives me an error message: Syntax error in JOIN operation.

So how do I left join another table to a Cartesian product?

Upvotes: 0

Views: 2844

Answers (1)

Icarus
Icarus

Reputation: 63962

Try this:

select x.dog, x.cat,cs.chase 
from 
     (select dog
            ,cat  
     from dogs, cats) x
left join catChases cs on cs.dog=x.dog and x.cat=cs.cat

Upvotes: 5

Related Questions