I have two tables and a join table.

Product (id, name)
Category (id, name)
Content (id, product_id, category_id)

I want to select all products that have some clause, say where name begins
with A that are not already in a specified category.

Something like, (pseudoquery)

select p.* from Product as p join Content as cn on p.id=cn.product_id and
category_id != XX

I think what I need to do is something with a left join where product joins
the content table and has a clause for the category_id and then selects all
the rows that return null for the content, but this is really taxing my
small brain.

Can anyone help ?

"sks" <sks.remove.me.please (AT) magnum55 (DOT) remove11.co.uk> schrieb im
Newsbeitrag news:carr1q$qbl$1 (AT) sparta (DOT) btinternet.com...
SELECT p.*
FROM Product p
WHERE NOT EXISTS (
SELECT co.product_id
FROM Content co, Category ca
WHERE co.product_id = p.id
AND co.category_id = ca.id
AND ca.name = 'your category name'
)

robert

"Robert Klemme" <bob.news (AT) gmx (DOT) net> wrote

Ah, my bad. I completely forgot to mention I'm using Mysql and cannot use
sub selects.

"sks" <sks.remove.me.please (AT) magnum55 (DOT) remove11.co.uk> wrote

DDL statements (for example, CREATE TABLE statements) help.
Also, can a product be in only one category or can it be in multiple?

CREATE TABLE Products
(
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(20) NOT NULL
)

CREATE TABLE Categories
(
category_id INT NOT NULL PRIMARY KEY,
category_name VARCHAR(20) NOT NULL
)

CREATE TABLE ProductCategories
(
product_id INT NOT NULL REFERENCES Products (product_id),
category_id INT NOT NULL REFERENCES Categories (category_id),
PRIMARY KEY (product_id, category_id)
)

SET @category_id_specified = 10 -- for example

-- If a product can be in but one category then the following will do:
SELECT P.product_id, P.product_name
FROM Products AS P
INNER JOIN
ProductCategories AS PC
ON P.product_name LIKE 'A%' AND -- constraint on products
P.product_id = PC.product_id AND
PC.category_id <> @category_id_specified

-- If a product can be in multiple categories then try this:
SELECT P.product_id, P.product_name
FROM Products AS P
LEFT OUTER JOIN
ProductCategories AS PC
ON P.product_id = PC.product_id AND
PC.category_id = @category_id_specified
WHERE P.product_name LIKE 'A%' AND -- constraint on products
PC.category_id IS NULL

The second query can handle both cases, that is, where a product is in
a single category or multiple categories, but might be less efficient than
the first query for single-category products.

--
JAG

"sks" <sks.remove.me.please (AT) magnum55 (DOT) remove11.co.uk> wrote


select *
from A
where not exists
(select * from B where A.id = B.id)

is equivalent to

select A.*
from A
left outer join B
on A.id = B.id
where B.id is NULL

Standard technique for the MySQL encumbered :-)

Regards,
Kristian

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

We also assume that we are dealing SQL and not a file system, like
MySQL, with a few commands that mimic SQL syntax as you finally told
us later in this thread.

You also do not seem to know that there is no such thing as a
"magical, universal" id columns in RDBMS. However, there is a record
locator in file systems and some vendors expose versions of these
NON-RELATIONAL PHYSICAL LOCATORS and trap fools. Do no tbe fooled.

If you had a good data model, it might look like this:

CREATE TABLE Products
(product_id CHAR(10) NOT NULL PRIMARY KEY
CHECK (<<validation predicate >>)
product_category CHAR(20) DEFAULT '{{unassigned}}' NOT NULL
CHECK (product_category IN (..),
..);

Read ISO-11179 about data element names. There is no such thing as a
"category_id" by definition. Category is a species within a genus. A
category is an attribute and NOT an entity.


SELECT product_id
FROM Products
WHERE product_category = '{{unassigned}}'
AND product_name LIKE 'A%';

Kristian Damm Jensen wrote:

the optimizer did not recognize the equivalence. The second form performed
much better in those older versions (circa ORACLE 7.x).

So it is sometimes good to know more than one way to code a query.

--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Thanks for the reply John. I've used the second query and its done just what
I want.

Thanks for the heads up. Next time I post I'll include the DDL.

No I didn't, I just set my primary key to auto increment in MySQL and use
that.