* Unnar
> I have a problem with this query
>
> Version MySQL 4.0.15-nt
>
> The query gets all articles written by author but excludes those articles
> which are related to products
>
> SELECT DISTINCT
> post.post_id,
> post.title
> FROM
> c_posts post,
> c_post_product prod,
> authors_relation rel,
> c_post_properties prop
> WHERE
> rel.post_id != '$nPostId' // This is the post_id of the post which
> is currently being viewed ( works )
> AND
> rel.author_id = '$nAuthorId' // The author_id of the Author
> AND
> post.post_id = rel.post_id // To connect the two tables
> AND
> post.post_id = prop.post_id // The properties table
> AND
> post.post_id <> prod.post_id // This is where the problem is. I've
> also tried != . Details below**
> AND
> prop.state != 3
>
> ** I don't get any errors but the query just doesn't Exclude
> those post_id's
> which are in the c_post_product table which should be the case.
Try a LEFT JOIN, and check if prod.post_id is NULL:
SELECT post.post_id, post.title
FROM c_posts post
authors_relation rel,
c_post_properties prop
LEFT JOIN c_post_product prod ON
post.post_id = prod.post_id
WHERE
rel.post_id != '$nPostId' // This is the post_id of the post which
is currently being viewed ( works )
AND
rel.author_id = '$nAuthorId' // The author_id of the Author
AND
post.post_id = rel.post_id // To connect the two tables
AND
post.post_id = prop.post_id // The properties table
AND
prod.post_id IS NULL
AND
prop.state != 3
<URL: http://www.mysql.com/doc/en/JOIN.html >
> ** I've also tried the NOT IN "function", but I get errors when
> trying that. Seems like it's not supported by my version ??
Well... the IN and NOT IN operators are available, but the operand is a
list, you can't use sub-queries in 4.0:
<URL: http://www.mysql.com/doc/en/Comparison_Operators.html#IDX1177 >
<URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
