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.


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.

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.

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.

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?

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.

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

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.

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

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.

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”

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! :smash:

In the end, it works fine, you can find and edit any record.