Left Outer Join with clause

[sks]

I have a Product table, a Categories table and a join table that contains
product to category mappings (each product can be in many categories)

CREATE TABLE categories (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
site_id bigint(20) NOT NULL default '0',
PRIMARY KEY (id),
) TYPE=MyISAM;
CREATE TABLE categories_contents (
id bigint(20) unsigned NOT NULL auto_increment,
product_id bigint(20) NOT NULL default '0',
category_id bigint(20) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE product (
id int(10) unsigned NOT NULL auto_increment,
name varchar(150) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
A category has a column called site_id which is a key to another table
called Site (the content of which is irrelevant here) which determines which
'web-site' the category appears on (the database supports multiple front
ends for the same set of products).

I am wanting to select all products that are not visible at all on a given
site (site_id=x). I can select all products not in any category at all using

select p.* from product as p left outer join categories_contents as co on
p.id=co.product_id where co.id is null

However, I cannot seem to get my query right when I need to expand this to
include the categories table so that I can add a clause on the site_id
column. I did the following:

select p.* from product as p left outer join categories_contents as co on
p.id=co.product_id left outer join categories as c on co.category_id=c.id
and (c.id is null || c.site_id!=?)

which is almost right but not quite. Can anyone help ?

[Siemel Naran]

"sks"
<sk*@magnum55.remove.andreplacewith.themostpowerfu lhandgunintheworld.com>
wrote in message

CREATE TABLE categories (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
site_id bigint(20) NOT NULL default '0',
PRIMARY KEY (id),
) TYPE=MyISAM;
CREATE TABLE categories_contents (
id bigint(20) unsigned NOT NULL auto_increment,
product_id bigint(20) NOT NULL default '0',
category_id bigint(20) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE product (
id int(10) unsigned NOT NULL auto_increment,
name varchar(150) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM; However, I cannot seem to get my query right when I need to expand this to
include the categories table so that I can add a clause on the site_id
column. I did the following: select p.* from product as p left outer join categories_contents as co on
p.id=co.product_id left outer join categories as c on co.category_id=c.id
and (c.id is null || c.site_id!=?)

which is almost right but not quite. Can anyone help ?

In SQL the || means concatenate. If you replace that with OR, does it work?
Just a guess, as I haven't tried it out actually.

As an aside, what if c.site_id is not NULL, but the site it points to does
not exist (say you didn't have a cascade delete or restrict in place)? Do
you need to check for this too?

[sks]

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message
news:JL********************@bgtnsc04-news.ops.worldnet.att.net...

"sks"
<sk*@magnum55.remove.andreplacewith.themostpowerfu lhandgunintheworld.com>
wrote in message

CREATE TABLE categories (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
site_id bigint(20) NOT NULL default '0',
PRIMARY KEY (id),
) TYPE=MyISAM;
CREATE TABLE categories_contents (
id bigint(20) unsigned NOT NULL auto_increment,
product_id bigint(20) NOT NULL default '0',
category_id bigint(20) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE product (
id int(10) unsigned NOT NULL auto_increment,
name varchar(150) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

However, I cannot seem to get my query right when I need to expand this to include the categories table so that I can add a clause on the site_id
column. I did the following:

select p.* from product as p left outer join categories_contents as co on p.id=co.product_id left outer join categories as c on co.category_id=c.id and (c.id is null || c.site_id!=?)

which is almost right but not quite. Can anyone help ?

In SQL the || means concatenate. If you replace that with OR, does it

work? Just a guess, as I haven't tried it out actually.
I'll try it.
As an aside, what if c.site_id is not NULL, but the site it points to does
not exist (say you didn't have a cascade delete or restrict in place)? Do
you need to check for this too?

I need to check that site_id does not equal the value I specify. So other
site_id values and null should be treated together if you see what I mean.

[Bill Karwin]

Siemel Naran wrote:

In SQL the || means concatenate. If you replace that with OR, does it work?
Just a guess, as I haven't tried it out actually.

The ANSI/ISO SQL-92 standard defines || as a string concatenation
operator, but MySQL uses CONCAT(string1, string2, ...) for
concatenation. MySQL uses || as a synonym for OR.

This is an unfortunate departure from the standard. But in this case,
conforming to the standard has little value, because several other RDBMS
implementations fail to match the SQL standard as well. The bottom line
is that string concatenation expressions are highly unlikely to be
portable between RDBMS vendors.

Bill K.