The multi-part identifier "DL.uid" could not be bound.

[vchris]

I'm trying to create a query but I got an error and I'm not sure how to solve this problem I have.SQL Query

SELECT I4.Description_E I4_Desc_E, I4.ISIC4_CodeFROM Document_Link DL, ISIC4 I4INNER JOIN 	(SELECT GD_uid, Description_E GD_Desc_E	FROM GuidanceDocument	WHERE Deleted = 0) GDON DL.uid = GD.uidWHERE I4.ISIC4_Code = DL.CodeGROUP BY I4.ISIC4_Code, I4.Description_EORDER BY I4.ISIC4_Code

Output<ul> <li>I4_Desc_E (ISIC4_Code) <ul> <li>GD_Desc_E (GD_uid)</li> </ul> </li></ul>In one I4_Desc_E there could be many GD_Desc_E/GD_uid.Let me know if there is a better way of coding this query.

[justsomeguy]

One thing I want to mention is to use the AS keyword when you're defining aliases. If you don't use AS, it will actually make a temporary copy of the table as use that, so it adds a bunch of overhead. Using AS just references the original table with a new name.

SELECT I4.Description_E AS I4_Desc_E, I4.ISIC4_CodeFROM Document_Link AS DL, ISIC4 AS I4INNER JOIN 	(SELECT GD_uid, Description_E AS GD_Desc_E	FROM GuidanceDocument	WHERE Deleted = 0) AS GD

[vchris]

Thanks for letting me know about that. I didn't think it made any difference.

[vchris]

So can anyone help me?

[justsomeguy]

What is DL.uid? The error message is calling it a multi-part identifier, is it a field?

[vchris]

Yes it's the field uid in DL (Document_Link)

[justsomeguy]

Hmm.. is it a foreign key? How is it multi-part?

[vchris]

Hmmm no idea... I don't know what multi-part is and it's not a key so...

[justsomeguy]

Is there anything else that DL.uid could refer to? Is there a view or stored procedure or anything with the same name?It might be an issue with the join. I see that you refer to GD_uid and GD.uid, I'm not sure if those are supposed to be the same or not. Try this version:

SELECT I4.Description_E AS I4_Desc_E, I4.ISIC4_Code, GD.uid, GD.Description_E AS GD_Desc_EFROM Document_Link AS DL, ISIC4 AS I4, GuidanceDocument AS GDWHERE I4.ISIC4_Code = DL.Code AND DL.uid = GD.uidGROUP BY I4.ISIC4_Code, I4.Description_EORDER BY I4.ISIC4_Code

[vchris]

I get an error with the query which is "Column 'GuidanceDocument.uid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."If I add GD.uid, GD.Description_E it works but not grouped the way I want to.Here is part of the data I get: * Other animal farming; production of animal products n.e.c. (0122) o This Manual describes the procedures and recommended approaches for estimating emissions from facilities engaged in rubber product manufacturing. (217) * Other animal farming; production of animal products n.e.c. (0122) o This Manual describes the procedures and recommended approaches for estimating emissions from incineration operations. (220)I want them grouped under the same category, like so: * Other animal farming; production of animal products n.e.c. (0122) (category is I4.Description_E and I4.ISIC4_Code) o This Manual describes the procedures and recommended approaches for estimating emissions from facilities engaged in rubber product manufacturing. (217) (GD.Description_E and GD.uid) o This Manual describes the procedures and recommended approaches for estimating emissions from incineration operations. (220)

[justsomeguy]

It looks like they are grouped under the same category. Each record from the database is it's own record, you can't have one record that has the category title and then 10 records "under" that record. You need 10 records, each with the same category, with the details for each record. You can keep track of which category you are on and any time it changes just write a new bullet out.

$catname = "";while ($row = mysql_fetch_assoc($result)){  if ($catname != $row['category'])  {	if ($catname != "") //end the previous list	  echo "</ul></ul>";	$catname = $row['category'];	echo "<ul><li>{$catname}</li><ul>";  }  echo "<li>" . /* whatever else */ . "</li>";}

I'm not sure if you're using PHP but hopefully you get the idea.

[vchris]

I got it working in CF. Here is the CF equivalent:

<cfset catcode = "">	<cfoutput query="Links">		<cfif catcode NEQ ISIC4_Code>			<cfif catcode NEQ "">				</ul>				</li>			</ul>			</cfif>						<cfset catcode = ISIC4_Code>			<ul>				<li>#I4_Desc_E# (<strong>#catcode#</strong>)				<ul>		</cfif>		<li>#GD_Desc_E# (#uid#)</li>	</cfoutput>

I only 1 small problem. The last sub <li> is not closed with a </ul></li></ul>.

[justsomeguy]

Yeah, after the loop is finished you'll have to check if the loop ran and if it did then you can write the extra closing tags.

[vchris]

Done! Thanks you very much for the script