Help with q of q

[al_gunner]

I have an application that needs to query a number of tables that reside on two
different servers so I'm using Q of Q but the problem is I need to do a LEFT
OUTER JOIN in the Q of Q to get the results I need. The is for a scheduler
which displays 12 time slots where appointments can be scheduled.

So I want to bring back appointments that are scheduled in the time slots as
well as the time slots that are blank. Therefore does anyone know a work
around to do a LEFT OUTER JOIN in a Q of Q.

The original query when the two database resided on the same server:

<cfquery datasource="#client.datasource#" name="exhibitorschedule"
blockfactor="11">
SELECT
esc.exhibitor_id,
esc.slot_id,
esc.total_slots,
esc.slots_used,
attsc.attendee_id,
ELAUsers.CO_ID,
Name.FULL_NAME AS name,
Name.COMPANY,
ELA.dbo.conf_reg.contactinfo,
attsc.Status,
ELA.dbo.conf_reg.Status AS constatus,
ELA.dbo.conf_reg.attendee_id AS theattid
FROM
Name
INNER JOIN
ELAUsers
ON
Name.ID = ELAUsers.ID
INNER JOIN
ELA.dbo.attendeeschedule attsc
ON
attsc.attendee_id = ELAUsers.ATTENDEE_ID
LEFT OUTER JOIN
ELA.dbo.conf_reg
ON
attsc.attendee_id = ELA.dbo.conf_reg.attendee_id
FULL OUTER JOIN
ELA.dbo.exhibitorschedule esc
ON
attsc.[day] = esc.[day]
AND
attsc.exhibitor_id = esc.exhibitor_id
AND
esc.slot_id = attsc.slot_id
WHERE esc.day=#val(day)#
<cfif isdefined('attributes.exhibitor_id') AND Len(attributes.exhibitor_id)>
AND esc.exhibitor_id=#attributes.exhibitor_id#
<cfelse>
AND esc.exhibitor_id=#session.exhibitor_id#
</cfif>
order by esc.slot_id, attsc.attendee_id desc
</cfquery>


Thanks in advance for the help.

Alan

[Dan Bracuk]

Q of Q does not support left or right joins. You could scrunch the two queries into a 2D array.

[al_gunner]

Hi Dan,

This is what I have for Q of Q:

<!--- Need to first get all the attendee_id's --->
<cfquery datasource="#application.datasource#" name="get_attendeeIDs">
SELECT
esc.exhibitor_id,
esc.slot_id,
esc.total_slots,
esc.slots_used,
attsc.attendee_id,
cr.contactinfo,
attsc.Status,
cr.Status AS constatus,
cr.attendee_id AS theattid
FROM
attendeeschedule attsc
LEFT OUTER JOIN
conf_reg cr
ON
attsc.attendee_id = cr.attendee_id
FULL OUTER JOIN
exhibitorschedule esc
ON
attsc.[day] = esc.[day]
AND
attsc.exhibitor_id = esc.exhibitor_id
AND
esc.slot_id = attsc.slot_id
WHERE
esc.day=#val(day)#
<cfif isdefined('attributes.exhibitor_id') AND Len(attributes.exhibitor_id)>
AND esc.exhibitor_id=#attributes.exhibitor_id#
<cfelse>
AND esc.exhibitor_id=#session.exhibitor_id#
</cfif>
</cfquery>

<!--- Set attendees to a list --->
<cfset theAttendees = "">
<cfloop query="get_attendeeIDs">
<cfif theattid NEQ "">
<cfset theAttendees = listAppend(theAttendees, theattid)>
</cfif>
</cfloop>

<!--- Check if any attendees have scheduled yet --->
<cfif theAttendees NEQ "">
<!--- get attendees info from iMIS --->
<cfquery datasource="#request.memberDatasource#" name="get_NameJoinData">
SELECT
e.CO_ID,
e.ATTENDEE_ID,
n.FULL_NAME AS name,
n.COMPANY
FROM
Name n
INNER JOIN
ELAUsers e
ON
n.ID = e.ID
WHERE
e.ATTENDEE_ID IN (#theAttendees#)
</cfquery>

<!--- get info from ELA db --->
<cfquery datasource="#application.datasource#" name="get_scheduleData">
SELECT
esc.exhibitor_id,
esc.slot_id,
esc.total_slots,
esc.slots_used,
attsc.attendee_id,
cr.contactinfo,
attsc.Status,
cr.Status AS constatus,
cr.attendee_id AS theattid
FROM
attendeeschedule attsc
LEFT OUTER JOIN
conf_reg cr
ON
attsc.attendee_id = cr.attendee_id
FULL OUTER JOIN
exhibitorschedule esc
ON
attsc.[day] = esc.[day]
AND
attsc.exhibitor_id = esc.exhibitor_id
AND
esc.slot_id = attsc.slot_id
WHERE
esc.day=#val(day)#
<cfif isdefined('attributes.exhibitor_id') AND Len(attributes.exhibitor_id)>
AND esc.exhibitor_id=#attributes.exhibitor_id#
<cfelse>
AND esc.exhibitor_id=#session.exhibitor_id#
</cfif>
</cfquery>

<!--- Join the 2 together --->
<cfquery name="exhibitorschedule" dbtype="query">
SELECT
get_scheduleData.exhibitor_id,
get_scheduleData.slot_id,
get_scheduleData.total_slots,
get_scheduleData.slots_used,
get_scheduleData.attendee_id,
get_scheduleData.contactinfo,
get_scheduleData.Status,
get_scheduleData.constatus,
get_scheduleData.theattid,
get_NameJoinData.CO_ID,
get_NameJoinData.ATTENDEE_ID,
get_NameJoinData.name,
get_NameJoinData.COMPANY
FROM
get_scheduleData, get_NameJoinData
WHERE
get_scheduleData.attendee_id = get_NameJoinData.ATTENDEE_ID
ORDER BY
get_scheduleData.slot_id, get_scheduleData.attendee_id DESC
</cfquery>

If Q of Q allowed Left Joins I could just use:

<cfquery name="exhibitorschedule" dbtype="query">
SELECT
get_scheduleData.exhibitor_id,
get_scheduleData.slot_id,
get_scheduleData.total_slots,
get_scheduleData.slots_used,
get_scheduleData.attendee_id,
get_scheduleData.contactinfo,
get_scheduleData.Status,
get_scheduleData.constatus,
get_scheduleData.theattid,
get_NameJoinData.CO_ID,
get_NameJoinData.ATTENDEE_ID,
get_NameJoinData.name,
get_NameJoinData.COMPANY
FROM
get_scheduleData
LEFT OUTER JOIN

get_NameJoinData
ON
get_scheduleData.attendee_id = get_NameJoinData.ATTENDEE_ID
ORDER BY
get_scheduleData.slot_id, get_scheduleData.attendee_id DESC
</cfquery>

How would I go about scrunching the results I need into a 2D Array?

I really appreciate the help.

Thx,
Alan

[Dan Bracuk]

You do it without a QofQ. And, just so you know, I didn't read your queries,
my attention span is much to short. So let's say you have two queries, q1 and
q2, and you want to do a left join, with q1 on the left. Do something like
this, but without any syntax errors I give you.

<cfscript>
x=arraynew();
leftfields = "f1,f2,etc"; // columns from q1
rigthtfields = "f1,f2,etc"; // columns from q2

leftcols = listlength(leftfields);
rightcols = listlength(rightfields);

leftrow =1;
rightrow =1;
/*
manually set the first row of your array to your column headers.
I'm going to skip that
*/
// now the data
for (ii = 2; ii lte q2.recordcount +1; ii = ii +1) {
// always populate the left hand side
for ( j = 1, j lte listlength(leftcols); j = j + 1) {
x[ii][j] = q1.listgetat(leftfields, j)[leftrow];
if (something from q1 is something from q2) {

for ( jj = 1, j lte listlength(rightcols); jj = jj + 1) {
x[ii][jj] = q1.listgetat(rightfields, j)[rightrow];
rightrow = rightrow + 1;
}
leftrow = leftrow + 1;
}
// make sure you have enough closing brackets, I probably don't

</cfscript>

[al_gunner]

Hi Dan,

Thanks for all your help, I got some ideas from what you posted to come up with my own work around.

Thx again,

Alan