query help needed

[thoresson]

Hi,

I'm building a planning system for a newspaper. Article ideas are defined as articles with no deadline, no publication date or no reporter assigned. I wan't to show all entries from table un_article that matches those three criterias.

I have three tables:

CREATE TABLE un_article
(
a_id INT PRIMARY KEY AUTO_INCREMENT,
a_header VARCHAR(20),
a_editor INT,
a_section INT,
a_deadline DATE DEFAULT "0000-00-00",
a_issue DATE DEFAULT "0000-00-00",
a_ready ENUM("Y","N") DEFAULT "N",
a_picture ENUM("N","B","G","R","F") DEFAULT "N",
a_desc MEDIUMTEXT
);

CREATE TABLE un_section
(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(40),
UNIQUE (s_name)
);

CREATE TABLE un_article_writer
(
a_id INT NOT NULL,
u_id INT NOT NULL,
PRIMARY KEY (a_id, u_id)
);

I want to get un_article.a_id, un_article.a_header and un_section.s_name for the entries in un_article for which un_section.s_id = un_article.a_section, un_article.a_deadline = '0000-00-00' and un_article.a_issue = '0000-00-00' and also only for entries in un_article whose un_article.a_id doesn't match a un_article_writer.a_id.

But I'm not able to build this query. I have tried various variants of the following, without any success.

"SELECT un_article.a_id, un_article.a_header, un_section.s_name FROM un_article, un_section, un_article_writer WHERE un_article.a_id != un_article_writer.a_id AND un_section.s_id = un_article.a_section AND un_article.a_deadline = '0000-00-00' AND un_article.a_issue = '0000-00-00' ORDER BY un_section.s_name ASC, un_article.a_header ASC"

Help appreciated!

//Anders

[jofa]

1) Don't use WHERE ... AND ... for join conditions, use ...ON table1.col1 = table2.col2 ...

2) un_article_writer isn't needed in the join, because there's "no reporter assigned" (your own words )

How about
SELECT ...
FROM un_section s
INNER JOIN un_article a
ON s.s_id = a.a_section
WHERE a.a_deadline = '0000-00-00'
AND a.a_issue = '0000-00-00'
ORDER BY s.s_name, a.a_header

[jofa]

OK, maybe un_article_writer is needed in the join, if there's a possibility that some articles can have a reporter assigned but deadline & issue not set

Add
... LEFT JOIN un_article_writer w
ON a.a_id = w.a_id ...

and also (in the where clause)
...AND w.a_id IS NULL

...since rows with no writer will have null values for the writer id when you left join the table

[jofa]

Finally: Why not let deadline and issue be null if it's not set (and not default "0000-00-00") ?

[thoresson]

OK, maybe un_article_writer is needed in the join, if there's a possibility that some articles can have a reporter assigned but deadline & issue not set

So is the case

While waiting for input, I've continued to read my MySQL books and come up with two queries that together does what I want. But I've not yet found out how to combine them to one. I'll compare my following two queries with yours now.

SELECT 
    un_article.a_id
FROM
    un_article
LEFT JOIN
    un_article_writer
USING
    (a_id)
WHERE
    un_article_writer.a_id is NULL

    
    
SELECT 
    un_article.a_desc, un_article.a_id, un_article.a_header, un_section.s_name 
FROM 
    un_article, un_section
WHERE 
    un_section.s_id = un_article.a_section AND un_article.a_deadline = '0000-00-00' AND un_article.a_issue = '0000-00-00' 
ORDER BY 
    un_section.s_name ASC, un_article.a_header ASC 

[thoresson]

Finally: Why not let deadline and issue be null if it's not set (and not default "0000-00-00") ?

Because I check that every entered date field against a date pattern. And that I didn't though of not checking empty ones. And therefore, to have empty form fields match my pattern I default them to 0000-00-00. Are there a reason this is not a good thing to do?

[thoresson]

Add
... LEFT JOIN un_article_writer w
ON a.a_id = w.a_id ...

and also (in the where clause)
...AND w.a_id IS NULL

Like this?

SELECT
    un_article.a_id, un_article.a_desc, un_article.a_id, un_article.a_header, un_section.s_name
FROM
    un_section, un_article
LEFT JOIN
    un_article_writer
ON
    un_article_writer.a_id=un_article.a_id
WHERE
    un_article_writer.a_id is NULL AND un_section.s_id = un_article.a_section AND un_article.a_deadline = '0000-00-00' AND un_article.a_issue = '0000-00-00'
ORDER BY
    un_section.s_name ASC, un_article.a_header ASC 

[jofa]

"Why is everyone so afraid of null?"
Read r937's posts in this thread:
http://www.sitepointforums.com/showt...211#post788211

[jofa]

Like this?

Nope, more like this:
SELECT ...
FROM un_section s
INNER JOIN un_article a
ON s.s_id = a.a_section
LEFT JOIN un_article_writer w
ON a.a_id = w.a_id
WHERE a.a_deadline = '0000-00-00'
AND a.a_issue = '0000-00-00'
AND w.a_id IS NULL
ORDER BY s.s_name, a.a_header


(Could be some minor syntax error in the query, haven't tested it)

Lycka till!