Finding duplicates in two dimensional array

I've got three separate queries that I basically need to output as one query so what I did was create a two dimensional array and just added the values from the queries to the array.  This works fine but some values in the array will be duplicated.

I need to do two things here:
1 - remove the duplicate email addresses from the array
2 - create a list of email values, delimited with ";"

I am going to output the unique emails and names on the page and use the list of unique email address to create a mailto link so users can import the email addresses into their default email program.   This is all simple to do with a one dimensional array, but I can't figure out a solution when using two dimensions.


<cfset emailArray = ARRAYNEW(2)>

<cfquery name="users">
SELECT email, name
FROM users
WHERE email <> ''
</cfquery>

	<!--- add these values to the emailArray --->
	<cfif users.recordcount neq 0>
	<cfoutput query="users">
		<cfset emailArray[#users.CurrentRow#][1]="#email#">
		<cfset emailArray[#users.CurrentRow#][2]="#name#">
	</cfoutput>
	</cfif>	

<!--- Two more queries here that will append values to the array --->
...............


<!--- Remove duplicates in array --->
?????

<!--- Create a ; delimited list of email addresses from unique email array values --->
????

<!--- look through array to display on page --->
<cfoutput>	
<cfloop index="thisRow" from="1" to="#arraylen( emailArray )#">
<cfloop index="thisColumn" from="1" to="#arraylen( emailArray[thisRow] )#">
#emailArray[thisRow][thisColumn]#<br>
</cfloop>

<a href="mailto:#myUniqueEmailList#">SEND EMAIL</a>
---------<br>
</cfloop>	
</cfoutput>

Open in new window

MFredinAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
> I've got three separate queries that I basically need to output as one query

Is it possible you're making things too complex?  First, is there a reason you need 3 queries? Second, assuming you do need 3 queries ... if all of the queries contain the columns Name and Email, you can use a UNION to join them into one query. A UNION also removes duplicates ...

<cfquery name="finalQuery" dbtype="query">
     SELECT  Name, Email FROM Query1
     UNION
     SELECT  Name, Email FROM Query2
     UNION
     SELECT  Name, Email FROM Query3
</cfquery>
0
 
MFredinAuthor Commented:
_agx_, I'm probably making this complicated when I don't have to.  Using UNION would def make more sense if it's possible... here are what I have for queries...  do the table field names have to match up exactly?



<cfquery name="carriers">
SELECT email, company_name
FROM carriers 
WHERE email <> ''
AND census_num IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(censusList)#" list="yes">)
</cfquery>

<cfquery name="user">
SELECT email, company_name
FROM user_info LEFT JOIN user_company ON user_info.user_company_id = user_company.user_company_id
WHERE email <> ''
AND census_num IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(censusList)#" list="yes">)
</cfquery>

<cfquery name="fed">
SELECT emailaddress, name
FROM fed
WHERE emailaddress <> ''
AND census_num IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#TRIM(censusList)#" list="yes">)
</cfquery>

Open in new window

0
 
MFredinAuthor Commented:
Very cool.  UNION worked perfectly! Thanks!
0
 
_agx_Commented:
> do the table field names have to match up exactly?

Yes, UNION's are pretty nice. Just for future reference the names don't have to match up exactly. Only the number of columns and data types.  So this would work

<cfquery name="finalQuery" dbtype="query">
     SELECT  Name, Email FROM Query1
     UNION
     SELECT  OtherName, OtherEmail FROM Query2
     ...
</cfquery>

The names of the columns in the query are determined by the 1st SELECT statement.

0
 
_agx_Commented:
One more thing, if your database supports multiple statements you could just do a single query with a db UNION. Instead of 3 db queries plus a QoQ with a UNION :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.