Professional Web Applications Themes

Help with a query - MySQL

I am using MySQL - 4.1.21-standard when I post the query below I get the same result-article-repeated four times. Where am I making my mistake with the code? SELECT pageid, title, section.name, category.name, subcat.name, division.name, subdiv.name FROM page RIGHT JOIN (author, section, category, subcat, division, subdiv) ON (section.sectionid = page.sectionid AND category.categoryid = page.categoryid AND subcat.subcatid = page.subcatid AND division.divisionid = page.divisionid AND subdiv.subdivid = page.subdivid) WHERE page.authorid=$seq Thanks for any help...

  1. #1

    Default Help with a query

    I am using MySQL - 4.1.21-standard when I post the query below I get
    the same result-article-repeated four times. Where am I making my
    mistake with the code?

    SELECT pageid, title,
    section.name, category.name,
    subcat.name, division.name,
    subdiv.name
    FROM page
    RIGHT JOIN (author, section, category, subcat,
    division, subdiv)
    ON (section.sectionid = page.sectionid AND
    category.categoryid = page.categoryid AND
    subcat.subcatid = page.subcatid AND
    division.divisionid = page.divisionid AND
    subdiv.subdivid = page.subdivid)
    WHERE page.authorid=$seq

    Thanks for any help

    Nancy Guest

  2. #2

    Default Re: Help with a query

    Nancy wrote: 

    Can you describe why you are using a RIGHT JOIN instead of an INNER
    JOIN? I think you may be misunderstanding what RIGHT JOIN is for.

    You have no conditions relating any of the tables on the right side of
    the join. So they are effectively forming a cross-join, or cartesian
    product. That's why you're getting repeated rows.

    In addition to that problem, you're also comparing a column of page to
    the $seq value in the WHERE clause, which means you're spoiling the
    outer join anyway. You might as well be using an inner join if you're
    doing that.

    Regards,
    Bill K.
    Bill Guest

  3. #3

    Default Re: Help with a query

    "Nancy" <com> wrote: 

    IMHO you misinterpret SQL syntax here. I guess you meant

    SELECT ...
    FROM page
    LEFT JOIN author ON page.authorid = author.authorid
    LEFT JOIN section ON page.sectionid = section.sectionid
    ...
    LEFT JOIN subdiv ON page.subdivid = subdic.subdivid

    but then you grouped all the right hand side tables into this list
    (author, section, ..., subdiv).

    Unfortunately this does not work because MySQL interprets the comma
    (",") between table names as INNER JOIN. So in fact your query reads:

    SELECT ...
    FROM page
    RIGHT JOIN (
    author
    INNER JOIN section
    ...
    INNER JOIN subdiv
    )

    This query is completely different.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  4. #4

    Default Re: Help with a query


    Nancy wrote: 

    Another useful thing to remember, is that if you only want a single
    result back from the database, you should use the LIMIT clause, and
    limit the output to a single result.

    Daz Guest

  5. #5

    Default Help with a query

    Hi,

    can anyone help me with a query?

    Let's say I have two tables:
    - books (id INT, name VARCHAR, publisher_id INT)
    - publishers (id INT, name VARCHAR)

    And I'd like to have a query to select and sort records in 'books' by
    the publishers.name that corresponds with books.publisher_id

    I'm a newbie, so excuse the newbie question.



    Thanks

    hjsb85@gmail.com Guest

  6. #6

    Default Re: Help with a query

    I forgot to add:
    The important thing is that the fields have same names in both tables
    and they can't be changed.

    hjsb85@gmail.com Guest

  7. #7

    Default Re: Help with a query

    com wrote: 

    To deal with duplicate field names, you simply need to prefix the name
    of the table to the name of the field: books.id, publishers.id.

    Cheers,
    Nicholas Sherlock

    --
    http://www.sherlocksoftware.org
    Nicholas Guest

  8. #8

    Default Re: Help with a query

    I did it with
    SELECT * FROM books LEFT JOIN publishers ON publisher_id =
    publishers.id

    only since the tables have the same column names, now the array the
    record is in doesn't have associative values for the duplicate names. I
    instead get like:

    array(8) {
    [0]=>string(1) "3"
    ["id"]=> string(1) "1"
    [1]=> string(11) "The Big Dork (Unabridged)"
    ["name"]=>string(11) "Penguin Books"
    [2]=>string(1) "1"
    ["publisher_id"]=>string(1) "1"
    [3]=>string(1) "1"
    [4]=>string(11) "Penguin Books"
    }

    How do I fix the query so I assign different column names without
    changing the table itself?


    Thanks

    hjsb85@gmail.com Guest

  9. #9

    Default Re: Help with a query

    On 7 Dec 2006 03:08:56 -0800, com wrote:
     

    SELECT
    B.id AS book_id
    ,B.name AS book_name
    ,publisher_id
    ,P.name as publisher_name
    FROM books AS B
    LEFT JOIN publishers AS P
    ON publisher_id = P.id
    ORDER BY P.name;

    (untested)
    Note this will list all books, even when they don't have a
    publisher, and it doesn't list publishers which didn't publish
    any book.
     

    YW
    --
    ( Kees
    )
    c[_] Graduate school is where you learn to call a spade a leveraged
    tactile-feedback geomass delivery system. (Martha Koester) (#190)
    Kees Guest

  10. #10

    Default Re: Help with a query

    A bit complicated but I finally got it. Thanks :)

    Kees Nuyt wrote: 
    >
    > SELECT
    > B.id AS book_id
    > ,B.name AS book_name
    > ,publisher_id
    > ,P.name as publisher_name
    > FROM books AS B
    > LEFT JOIN publishers AS P
    > ON publisher_id = P.id
    > ORDER BY P.name;
    >
    > (untested)
    > Note this will list all books, even when they don't have a
    > publisher, and it doesn't list publishers which didn't publish
    > any book.

    >
    > YW
    > --
    > ( Kees
    > )
    > c[_] Graduate school is where you learn to call a spade a leveraged
    > tactile-feedback geomass delivery system. (Martha Koester) (#190)[/ref]

    hjsb85@gmail.com Guest

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Query of Queries with constructed query using QueryNew()and QueryAddRow()
    By LeadFoot in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 4th, 02:00 PM
  3. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  4. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139