SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    More records returned than exist in database

    I have the following query pulling records from a database where the main documents column has 2078 rows.

    Code:
    SELECT 	  search_fhwa.id
    	, search_fhwa.document_title
            , search_fhwa.link
            , search_fhwa.date_issued_text
            , search_fhwa_order.category_name
            , search_offices.office_name
            , search_offices.office_acronym
            , search_fhwa_disc.adiscipline
            , search_fhwa_sub.bsubdiscipline
    FROM ( ( ( ( ( search_fhwa
    LEFT JOIN search_offices
    	ON search_offices.office_id = search_fhwa.office_id
            )
    INNER JOIN search_fhwa_order
    	ON search_fhwa_order.cat_id = search_fhwa.cat_id
            )
    LEFT JOIN search_sub 
    	ON search_sub.doc_id = search_fhwa.id
            )
    LEFT JOIN search_fhwa_sub  
    	ON search_fhwa_sub.sub_id = search_sub.sub_id
    	)
    LEFT JOIN search_fhwa_disc 
    	ON search_fhwa_disc.disc_id = search_fhwa_sub.parent_id
            )
    ORDER BY search_fhwa.id, search_fhwa.office_id
    Where the results display, I am grouping the results by ID (in ColdFusion, this is <cfoutput query="ListDocs" group="id">)

    Unfortunately, I get 2224 rows, and not the 2078 like I should. Something is getting pulled out twice, but I'll be darned if I can see where. I'm hoping some extra eyes will help.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You're joining tables, so if the join criteria result in a 1-n relationship, rows will be duplicated. But I can't tell you where that is happening, because I don't know the relationships between these tables.

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yeah, what I need to do is find one of the duplicates so I can see why it would be showing twice. That said, I just added some code that would flag a dupe, and I got no dupes.

    So I added a row counter that increments with each row. I got 2078.

    So the new question is why queryname.recordcount returns more records than my counter. I'll assume that the ones stripped by the group="" attribute of cfoutput are counted in the recordset even when they don't show up. Problem solved I think.
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    <cfoutput query="ListDocs" group="id">
    i love the coldfusion GROUP= option

    but tell me, why are you skipping over some rows?

    and have you tracked down where the "extra" rows are coming from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    but tell me, why are you skipping over some rows?

    and have you tracked down where the "extra" rows are coming from?
    Indeed I have!

    When I remove the group= attribute, I get some records listed twice. Since a record can be listed under more than one subdiscipline, it appears twice, and the group= condenses them into one. But since the queryname.recordcount counts the rows from the query independent of the group= output, it counts all the rows returned.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Since a record can be listed under more than one subdiscipline, it appears twice, and the group= condenses them into one.
    well, that doesn't seem right to me

    a record can belong to two subdisciplines, but you show only one of them.... i would think that'd be misleading
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Well, not really. This is a back-end tool to use to edit the database. There is only one record, and the table it lives in is joined with the subdiscipline table. I only want to show the record once, because this is where you click to edit it. When you do, you get a web form that's populated with all the details about that record, and, including all the subdisciplines, which you can add and remove.

    Having the same record appear for each discipline. subdiscipline, etc it's assigned to would be confusing.
    <cfset myblog = "http://cydewaze.org/">

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I only want to show the record once, because this is where you click to edit it.
    so why show any subdiscipline at all? if the user sees a record with subdiscipline X on this page and she knows it should also be subdiscipline Y, so she clicks on it, and gets sent to a page which then shows both X and Y... if that were me, i'd be pissed off and would trust the entire app less

    but it's your app
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I think I'm just not explaining it very well.

    The page in question just shows the document title and a few details, along with a link. When you click the link, you get a form with all the details that you can edit. But on the page in question it's just:

    doc 4321 - document title - date - office

    If I hadn't grouped, it would be:

    doc 4321 - document title - date - office
    doc 4321 - document title - date - office

    The subdisciplines don't appear until you're in the edit form. They don't appear on the index page, which is why I had a hard time figuring out why I had duplicates.
    <cfset myblog = "http://cydewaze.org/">

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I think I'm just not explaining it very well.
    you can say that again!!!

    why the heck would your query join to subdisciplines at all if "The subdisciplines don't appear until you're in the edit form"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why the heck would your query join to subdisciplines at all if "The subdisciplines don't appear until you're in the edit form"
    Because on the index page there's a form up top to pare results down by office, discipline, etc. so you don't have to dig through all 2078 records to find the one you want to edit.
    <cfset myblog = "http://cydewaze.org/">

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Because on the index page there's a form up top to pare results down by office, discipline, etc. so you don't have to dig through all 2078 records to find the one you want to edit.
    okay, so a record has subdisciplines X and Y, but your query only shows X, and somebody using your pare-down form to look under subdiscipline Y doesn't find it...

    is still say it's b0rked
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    rudy!

    In the end, it works fine, you can find and edit any record.
    <cfset myblog = "http://cydewaze.org/">


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
  •