Merging 2 queries interleaving results 1

[aliashippysmom]

Hello! I have 1 table in my database with a column called NewsType. This column can take on one of two values: Workforce or UI. I want to pull data from the column based on this column and display it on the web page in a tabular format. The first column of the table must contain only data with the NewsType of Workforce. The second column must contain data with the NewsType of UI.

I am doing it like this:

<cfquery name="getWorkforce" datasource="myDSN">
select title, source, description, newsType from myTable
where newsType = 'workforce'
</cfquery>

<cfquery name="getUI" datasource="myDSN">
select title, source, description, newsType from myTable
where newsType= 'ui'
</cfquery>

<table>
<tr>
<td>
<cfoutput query="getWorkforce">
#title#<br>#source#<br>#description#<br><br>
</cfoutput>
</td>
<td>
<cfoutput query="getUI">
#title#<br>#source#<br>#desciption#<br><br>
</cfoutput>
</td>
</tr>
</table>

The problem is the description field can vary in length, so the rows and columns look askew:

My Workforce Story 1, My UI Story 1
Source 1 Source 2
Desc 1 This is the desc Desc 2 This is antohre desc
This one is longer
IT goes on here... My UI Story 2
Source 3
My Workforce Story 2 Description of this story
Source 4 More description


Etc.

How can I make it line up? Usually with just one query I do the MOD thing which works o.k.

I hope this makes sense and thanks!

 

[LyndonOHRC]

I can't really test because I don't have your database but this should be close.

<cfquery name="getWorkforce" datasource="myDSN">
select title, source, description, newsType from myTable
where newsType = 'workforce'
</cfquery>

<cfquery name="getUI" datasource="myDSN">
select title, source, description, newsType from myTable
where newsType= 'ui'
</cfquery>

<cfset myQuery = QueryNew("titleworkforce, sourceworkforce, descriptionworkforce, newsTypeworkforce,titleui, sourceui, descriptionui, newsTypeui")>
<cfset newRow  = QueryAddRow(MyQuery, #Max(getWorkforce.RecordCount,getUI.RecordCount)#)>
<cfoutput query="getWorkforce">
	<cfset temp = QuerySetCell(myQuery, "titleworkforce", "#getWorkforce.title#", #getWorkforce.CurrentRow#)>
	<cfset temp = QuerySetCell(myQuery, "sourceworkforce", "#getWorkforce.title#", #getWorkforce.CurrentRow#)>
	<cfset temp = QuerySetCell(myQuery, "descriptionworkforce", "#getWorkforce.title#", #getWorkforce.CurrentRow#)>
	<cfset temp = QuerySetCell(myQuery, "newsTypeworkforce", "#getWorkforce.title#", #getWorkforce.CurrentRow#)>
</cfquery>
<cfoutput query="getUI">
	<cfset temp = QuerySetCell(myQuery, "titleui", "#getUI.title#", #getUI.CurrentRow#)>
	<cfset temp = QuerySetCell(myQuery, "sourceui", "#getUI.title#", #getUI.CurrentRow#)>
	<cfset temp = QuerySetCell(myQuery, "descriptionui", "#getUI.title#", #getUI.CurrentRow#)>
	<cfset temp = QuerySetCell(myQuery, "newsTypeui", "#getUI.title#", #getUI.CurrentRow#)>
</cfquery>
<table>
	<tr>
		<td>title</td>
		<td>source</td>
		<td>description</td>
		<td>newsType</td>
		<td>title</td>
		<td>source</td>
		<td>description</td>
		<td>newsType</td>
	</tr>
	<cfoutput query="temp">
		<tr>
			<td valign="top">#temp.titleworkforce#</td>
			<td valign="top">#temp.sourceworkforce#</td>
			<td valign="top">#temp.descriptionworkforce#</td>
			<td valign="top">#temp.newsTypeworkforce#</td>
			<td valign="top">#temp.titleui#</td>
			<td valign="top">#temp.sourceui#</td>
			<td valign="top">#temp.descriptionui#</td>
			<td valign="top">#temp.newsTypeui#</td>
		</tr>
	</cfoutput>
</table>

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[LyndonOHRC]

ooops. your final loop should have been:

<cfoutput query="myQuery">
		<tr>
			<td valign="top">#myQuery.titleworkforce#</td>
			<td valign="top">#myQuery.sourceworkforce#</td>
			<td valign="top">#myQuery.descriptionworkforce#</td>
			<td valign="top">#myQuery.newsTypeworkforce#</td>
			<td valign="top">#myQuery.titleui#</td>
			<td valign="top">#myQuery.sourceui#</td>
			<td valign="top">#myQuery.descriptionui#</td>
			<td valign="top">#myQuery.newsTypeui#</td>
		</tr>
	</cfoutput>

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[aliashippysmom]

Thank you so much for your help. I was able to get it to work. I thought maybe a query or an array possibly would be the answer, but needed a little help visualizing it.