Re: Excluding records from repeat region in PHP
- From: coolsti <coo@xxxxxxxxxx>
- Date: Fri, 29 Apr 2005 12:52:00 +0200
On Fri, 29 Apr 2005 02:41:46 -0700, duncan.lovett wrote:
> PHP 4
> MySQL 4.0.20
> DW MW 2004 7.01
>
> This problem is giving me a bit of a headache, and I'm getting nowhere
> with it so thought I'd open it up to all, any help would be greatly
> appreciated.
A problem with subselects is that people forget or never realize that many
things can be performed without subselects. In fact, the problem you
describe, if I understand it correctly, is one for a left join.
Consider two tables:
create table products (productid int unsigned not null primary key);
create table products_not_allowed (userid int unsigned not null,
productid int unsigned not null);
And here I have the product id in the products table as the field
products, and in the products_not_allowed table, each row identifies a
user by its user id and the product id that this user is not allowed to
see.
The query which will produce what you then want, all the products that a
particular user is not allowed to see, is this:
select products.productid from products left join products_not_allowed on
products.productid = products_not_allowed.productid and userid =
$user where products_not_allowed.productid is null
Here, $user is the user id if the user for this query.
The left join produces a table where there is a NULL in the rows for both
the user field and the products_not_allowed.productid field whenever there
is not a match of product id's, that is whenever the product id is not in
the products_not_allowed table for user = $user. So what you then want is
all the rows where either userid or products_not_allowed.productid is
null. I used the latter in the above query.
This will work even if there are many more fields in your products table
that you also wish to show.
- steve
.
- Follow-Ups:
- Re: Excluding records from repeat region in PHP
- From: coolsti
- Re: Excluding records from repeat region in PHP
- Prev by Date: Re: Excluding records from repeat region in PHP
- Next by Date: Re: Excluding records from repeat region in PHP
- Previous by thread: Re: Excluding records from repeat region in PHP
- Next by thread: Re: Excluding records from repeat region in PHP
- Index(es):
Relevant Pages
|
