hi-

i want to find master records where that master doesn't have a
presence in the join table.  for example, if i were doing a restaurant
review web site, and i have users, reviews, and restaurants.  i want
to find all restaurants for which a specific user has NOT written a
review.

i tried this:

select distinct restaurants.id  from restaurants left join reviews on
restaurants.id = reviews.restaurant_id where (reviews.user_id <> 5 or
review_users is NULL)

but this doesn't work because it finds restaurants where user 5 AND
someone else have reviewed and only eliminates the user 5 row.  i want
to find only restaurants that user 5  has not reviewed.

thanks for any help
dino

In article <455bf818-b0e6-4e65-80db-11a840ae2185@v7g2000vbd.googlegroups.com>, "dino d." <dinodorroco@yahoo.com> wrote:
>hi-
>
>i want to find master records where that master doesn't have a
>presence in the join table.  for example, if i were doing a restaurant
>review web site, and i have users, reviews, and restaurants.  i want
>to find all restaurants for which a specific user has NOT written a
>review.
>
>i tried this:
>
>select distinct restaurants.id  from restaurants left join reviews on
>restaurants.id = reviews.restaurant_id where (reviews.user_id <> 5 or
>review_users is NULL)
>
>but this doesn't work because it finds restaurants where user 5 AND
>someone else have reviewed and only eliminates the user 5 row. 

Right, because you're asking for restaurants that have been reviewed by 
somebody other than user 5, or have been reviewed by nobody. That's not the 
same as asking for restaurants that have not been reviewed by user 5.

> i want
>to find only restaurants that user 5  has not reviewed.

Then you need to ask for restaurants that user 5 has not reviewed. Change your 
WHERE clause to

WHERE restaurant.id NOT IN 
(SELECT restaurant_id FROM reviews WHERE user_id = 5)

thanks for the reply- is there any way to do this without a subquery?
this will get very expensive very quickly, right?

On Jan 31, 12:15=A0pm, spamb...@milmac.com (Doug Miller) wrote:
> In article <455bf818-b0e6-4e65-80db-11a840ae2...@v7g2000vbd.googlegroups.=
com>, "dino d." <dinodorr...@yahoo.com> wrote:
>
>
>
> >hi-
>
> >i want to find master records where that master doesn't have a
> >presence in the join table. =A0for example, if i were doing a restaurant
> >review web site, and i have users, reviews, and restaurants. =A0i want
> >to find all restaurants for which a specific user has NOT written a
> >review.
>
> >i tried this:
>
> >select distinct restaurants.id =A0from restaurants left join reviews on
> >restaurants.id =3D reviews.restaurant_id where (reviews.user_id <> 5 or
> >review_users is NULL)
>
> >but this doesn't work because it finds restaurants where user 5 AND
> >someone else have reviewed and only eliminates the user 5 row.
>
> Right, because you're asking for restaurants that have been reviewed by
> somebody other than user 5, or have been reviewed by nobody. That's not t=
he
> same as asking for restaurants that have not been reviewed by user 5.
>
> > i want
> >to find only restaurants that user 5 =A0has not reviewed.
>
> Then you need to ask for restaurants that user 5 has not reviewed. Change=
 your
> WHERE clause to
>
> WHERE restaurant.id NOT IN
> (SELECT restaurant_id FROM reviews WHERE user_id =3D 5)

In article <9b85347f-c52e-4352-a3bc-a809b7f2db74@f11g2000yqm.googlegroups.com>, "dino d." <dinodorroco@yahoo.com> wrote:

[Top-posting corrected -- please don't do that]
>
>On Jan 31, 12:15=A0pm, spamb...@milmac.com (Doug Miller) wrote:
>> In article <455bf818-b0e6-4e65-80db-11a840ae2...@v7g2000vbd.googlegroups.=
>com>, "dino d." <dinodorr...@yahoo.com> wrote:
>> >i want to find master records where that master doesn't have a
>> >presence in the join table. =A0for example, if i were doing a restaurant
>> >review web site, and i have users, reviews, and restaurants. =A0i want
>> >to find all restaurants for which a specific user has NOT written a
>> >review.
>>
>> >i tried this:
>>
>> >select distinct restaurants.id =A0from restaurants left join reviews on
>> >restaurants.id =3D reviews.restaurant_id where (reviews.user_id <> 5 or
>> >review_users is NULL)
>>
>> >but this doesn't work because it finds restaurants where user 5 AND
>> >someone else have reviewed and only eliminates the user 5 row.
>>
>> Right, because you're asking for restaurants that have been reviewed by
>> somebody other than user 5, or have been reviewed by nobody. That's not the
>> same as asking for restaurants that have not been reviewed by user 5.
>>
>> > i want
>> >to find only restaurants that user 5 has not reviewed.
>>
>> Then you need to ask for restaurants that user 5 has not reviewed. Change your
>> WHERE clause to
>>
>> WHERE restaurant.id NOT IN
>> (SELECT restaurant_id FROM reviews WHERE user_id = 5)
>
>thanks for the reply- is there any way to do this without a subquery?

No.

>this will get very expensive very quickly, right?

No, not if you have your tables indexed properly. With indexes on 
reviews.user_id, reviews.restaurant_id, and restaurant.id, this will be very 
fast.

Doug Miller:

>In article
><9b85347f-c52e-4352-a3bc-a809b7f2db74@f11g2000yqm.googlegroups.com>,
>"dino d." <dinodorroco@yahoo.com> wrote:

>>> > i want
>>> >to find only restaurants that user 5 has not reviewed.
>>>
>>> Then you need to ask for restaurants that user 5 has not
>>>reviewed. Change your  WHERE clause to
>>>
>>> WHERE restaurant.id NOT IN
>>> (SELECT restaurant_id FROM reviews WHERE user_id = 5)
>>
>>thanks for the reply- is there any way to do this without a
>>subquery?
>
>No.


I'll give it a try anyway: would this give the same result?

select 	restaurants.id  from restaurants 
left join 	reviews 
       on 	restaurants.id = reviews.restaurant_id 
      and 	reviews.user_id = 5
group by 	restaurants.id 
having 	count(reviews.id)=0

-- 
Erick

On Jan 31, 5:15=A0pm, spamb...@milmac.com (Doug Miller) wrote:
> In article <455bf818-b0e6-4e65-80db-11a840ae2...@v7g2000vbd.googlegroups.=
com>, "dino d." <dinodorr...@yahoo.com> wrote:
>
>
>
>
>
> >hi-
>
> >i want to find master records where that master doesn't have a
> >presence in the join table. =A0for example, if i were doing a restaurant
> >review web site, and i have users, reviews, and restaurants. =A0i want
> >to find all restaurants for which a specific user has NOT written a
> >review.
>
> >i tried this:
>
> >select distinct restaurants.id =A0from restaurants left join reviews on
> >restaurants.id =3D reviews.restaurant_id where (reviews.user_id <> 5 or
> >review_users is NULL)
>
> >but this doesn't work because it finds restaurants where user 5 AND
> >someone else have reviewed and only eliminates the user 5 row.
>
> Right, because you're asking for restaurants that have been reviewed by
> somebody other than user 5, or have been reviewed by nobody. That's not t=
he
> same as asking for restaurants that have not been reviewed by user 5.
>
> > i want
> >to find only restaurants that user 5 =A0has not reviewed.
>
> Then you need to ask for restaurants that user 5 has not reviewed. Change=
 your
> WHERE clause to
>
> WHERE restaurant.id NOT IN
> (SELECT restaurant_id FROM reviews WHERE user_id =3D 5)

This is a good candidate for the EXISTS predicate:

WHERE NOT EXISTS (SELECT * FROM reviews WHERE restaurant.id =3D
reviews.restaurant_id AND reviews.user_id =3D 5)

It can also be done using a self join on the reviews table.

Captain Paralytic:

>On Jan 31, 5:15�pm, spamb...@milmac.com (Doug Miller) wrote:
>> In article

>> WHERE restaurant.id NOT IN
>> (SELECT restaurant_id FROM reviews WHERE user_id = 5)
>
>This is a good candidate for the EXISTS predicate:
>
>WHERE NOT EXISTS (SELECT * FROM reviews WHERE restaurant.id =
>reviews.restaurant_id AND reviews.user_id = 5)

Sounds good. 
Is this "NOT EXISTS" more or less an equivalent of "NOT IN" as per
Doug's example?


>
>It can also be done using a self join on the reviews table.

It can. But as soon as there are more reviews than restaurants (which
may be assumed) this appears to become a bit slower than my proposal,
because the (self) JOIN would result in a larger intermediate result
set.
Is that correct?



-- 
Erick

On 1 Feb, 07:03, "Erick T. Barkhuis" <erick.use-...@ardane.c.o.m>
wrote:
> Captain Paralytic:
>
> >On Jan 31, 5:15=A0pm, spamb...@milmac.com (Doug Miller) wrote:
> >> In article
> >> WHERE restaurant.id NOT IN
> >> (SELECT restaurant_id FROM reviews WHERE user_id =3D 5)
>
> >This is a good candidate for the EXISTS predicate:
>
> >WHERE NOT EXISTS (SELECT * FROM reviews WHERE restaurant.id =3D
> >reviews.restaurant_id AND reviews.user_id =3D 5)
>
> Sounds good.
> Is this "NOT EXISTS" more or less an equivalent of "NOT IN" as per
> Doug's example?
I don't think it is. It really depends on how MySQL creates the list
of IDs for use in the IN clause.

If there is a composite index on reviews.restaurant_id +
reviews.user_id (in either order), then this index can be used to get
an instant answer as to whether the instance exists.

However, I would not necessarily expect MySQL to build the list of IDs
for the IN clause as an index (although it may do), hence the only way
to be sure that the ID is not in the list would be to scan the
complete list each time.

> >It can also be done using a self join on the reviews table.
>
> It can. But as soon as there are more reviews than restaurants (which
> may be assumed) this appears to become a bit slower than my proposal,
> because the (self) JOIN would result in a larger intermediate result
> set.
> Is that correct?
Well, it depends on what indexes are available and precisely how the
joins are written. Rememeber that, as with the NOT EXISTS, there will
not necessarily be an "intermediate result set". If I was coding this
using VSAM or ISAM, I would look up each item in the index to see if
it existed and I am pretty sure that that is what MySQL will do.

Not everything is done with an "intermediate result set". The "IN
(SELECT ...)", certainly will produce one, but when indexes are
available for lookups, then there is no need to do anything apart from
look to see if the item exists in the index.

On Jan 31, 7:17=A0pm, "Erick T. Barkhuis" <erick.use-...@ardane.c.o.m>
wrote:
[...]
> I'll give it a try anyway: would this give the same result?
>
> select =A0restaurants.id =A0from restaurants
> left join =A0 =A0 =A0 reviews
> =A0 =A0 =A0 =A0on =A0 =A0 =A0 restaurants.id =3D reviews.restaurant_id
> =A0 =A0 =A0 and =A0 =A0 =A0 reviews.user_id =3D 5
> group by =A0 =A0 =A0 =A0restaurants.id
> having =A0count(reviews.id)=3D0
>

Another variant is:

select  restaurants.id
from restaurants
left join       reviews
    on       restaurants.id =3D reviews.restaurant_id
    and       reviews.user_id =3D 5
where reviews.id is null


/Lennart