SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query help needed

    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

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Finally: Why not let deadline and issue be null if it's not set (and not default "0000-00-00") ?

  5. #5
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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
    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.

    PHP Code:
    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_descun_article.a_idun_article.a_headerun_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 ASCun_article.a_header ASC 

  6. #6
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jofa
    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?

  7. #7
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jofa
    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?

    PHP Code:
    SELECT
        un_article
    .a_idun_article.a_descun_article.a_idun_article.a_headerun_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 ASCun_article.a_header ASC 

  8. #8
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "Why is everyone so afraid of null?"
    Read r937's posts in this thread:
    http://www.sitepointforums.com/showt...211#post788211

  9. #9
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thoresson
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •