Query of Queries - Combining Two Datasources

[storserver]

Problem: Query needs to combine fields from one datasource with those of
another, matching on a common ID.

Example:
<CFQUERY DATASOURCE="Source1"
SELECT * from Contacts
</CFQUERY>

join that to:
<CFQUERY DATASOURCE="Source2"
SELECT * from Companies
</CFQUERY>

Combine the data on a common CompanyID in each table (and different
datasource) into records that show these fields:
Name from Query 1
Phone from Query 1
Manager from Query 1
Company Name from Query 2
Company Address from Query 2

IN ADDITION, the query needs to draw selection criteria from form fields such
as:
Where Manager = form field results (lives in first datasource)
and Company name = other form field results (lives in first datasource)

Thanks in advance for your help!

[Dan Bracuk]

First, this might not work with select * in your original queries. You might
have to type out the field names.

the q of q is

select q1.name, q1,phone,.. q2.companyname,...
from q1, q2
where q1.companyid = q2.companyid.

To restrict your original queries to what you are getting from the form, use a
where clause.

[DixieGal]

to add to what Dan mentioned... If you
want all items in q1 to be included, padding nulls in where
q2 doesn't have a matching company use a LEFT OUTER join

select q1.name, q1,phone,.. q2.companyname,...
from q1 left outer join q2
on q1.companyid = q2.companyid.

If you want only the items in q1 that are also in q2,
use an INNER or RIGHT INNER join (also may be
a Natural Join)

select q1.name, q1,phone,.. q2.companyname,...
from q1 inner join q2
on q1.companyid = q2.companyid.

Check your db documentation, these joins are
the same in theory, but SQL Server differs
on the JOIN keyword than Oracle.... and such.

--
Tami
aka DixieGal

**************************
So it is that the gods to dno give all men gifts of grace - neither good
looks not intelligence nor eloquence...
--Homer, The Odyssey
**************************

"Dan Bracuk" <webforumsuser@macromedia.com> wrote in message
news:dj3his$fc5$1@forums.macromedia.com...
| First, this might not work with select * in your original queries. You
might
| have to type out the field names.
|
| the q of q is
|
| select q1.name, q1,phone,.. q2.companyname,...
| from q1, q2
| where q1.companyid = q2.companyid.
|
| To restrict your original queries to what you are getting from the form,
use a
| where clause.
|

[storserver]

Thank you for your help. I'd just add that I still got errors until I remembered to change
<CFQUERY NAME="JoingTwoQueries" DATASOURCE="ABC">
to:
<CFQUERY NAME="JoingTwoQueries" DBTYPE="QUERY">

[storserver]

I was wrong. One more snag. Can you tell me why the Left Outer Join is erroring
out as follows:

Query Of Queries syntax error.
Encountered "Left.

Does the join work differenty when there's a DBTYPE="QUERY"?

<CFQUERY NAME="JoinTwoQueries" DBTYPE="QUERY">
SELECT Q1.CID, Q1.Company, Q2.Customer_ID, Q2.RCID
FROM Q1 Left Outer Join Q2 on Q1.CID=Q2.Customer_ID
</CFQUERY>

[storserver]

I searched Query of Queries in the help menu in Dreamweaver. It seems to say
Left Outer Joins aren't possible with Queries of Queries. It says,
Using joins
A join operation uses a single SELECT statement to return a result set from
multiple tables. The re are two main types of JOIN operations:

INNER JOIN includes in the result set only records that are present in both
tables
OUTER JOIN includes in the result set all records in one of the tables.
ColdFusion does not support OUTER JOINs, nor does it support the INNER JOIN
syntax, as the following example shows:

SELECT Dog_ID, Breed_ID,
FROM Dogs INNER JOIN Breed
ON Dogs.Dog_ID = Breed.Dog_ID;
ColdFusion supports INNER JOINs between two tables, as the following example
shows. This operation is the most common type of join.

SELECT Dog_ID, Breed_ID
FROM Dogs, Breed
WHERE Dogs.Dog_ID = Breed.Dog_ID;


Is there a way of doing left outer joins on QofQs?

[mxstu]

I don't know that QofQ's support the JOIN syntax.

[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/using_39.htm#wp1175636[/url]

[Dan Bracuk]

Why are you attempting a left join anyway? Your original requirement
was"Problem: Query needs to combine fields from one datasource with those of
another, matching on a common ID." You don't need a left join to do that.

[storserver]

You are right, Dan, my original request was only about Queries of Queries, not
joins within them. DixieGal first mentioned the possibility of doing a left
outer join and I realized that would give me the most flexibility. mxstu's
response, "I don't know that QofQ's support the JOIN syntax." appears to be
correct.

Thanks for your help, everyone!

[Dan Bracuk]

It is possible to produce a left join query using query functions, loops and
ifs. It takes takes a bit more time, effort, thought, and keypounding than Q
of Q.

Originally posted by: storserver
You are right, Dan, my original request was only about Queries of Queries, not
joins within them. DixieGal first mentioned the possibility of doing a left
outer join and I realized that would give me the most flexibility. mxstu's
response, "I don't know that QofQ's support the JOIN syntax." appears to be
correct.

Thanks for your help, everyone!

[DMM_ENT]

Originally posted by: storserver
Problem: Query needs to combine fields from one datasource with those of
another, matching on a common ID.

Example:
<CFQUERY DATASOURCE="Source1"
SELECT * from Contacts
</CFQUERY>

join that to:
<CFQUERY DATASOURCE="Source2"
SELECT * from Companies
</CFQUERY>

Combine the data on a common CompanyID in each table (and different
datasource) into records that show these fields:
Name from Query 1
Phone from Query 1
Manager from Query 1
Company Name from Query 2
Company Address from Query 2

IN ADDITION, the query needs to draw selection criteria from form fields such
as:
Where Manager = form field results (lives in first datasource)
and Company name = other form field results (lives in first datasource)

Thanks in advance for your help!

[DMM_ENT]

Originally posted by: storserver
Problem: Query needs to combine fields from one datasource with those of
another, matching on a common ID.

Example:
<CFQUERY DATASOURCE="Source1"
SELECT * from Contacts
</CFQUERY>

join that to:
<CFQUERY DATASOURCE="Source2"
SELECT * from Companies
</CFQUERY>

Combine the data on a common CompanyID in each table (and different
datasource) into records that show these fields:
Name from Query 1
Phone from Query 1
Manager from Query 1
Company Name from Query 2
Company Address from Query 2

IN ADDITION, the query needs to draw selection criteria from form fields such
as:
Where Manager = form field results (lives in first datasource)
and Company name = other form field results (lives in first datasource)

Thanks in advance for your help!


HI, there!

I have one question, yet it is off the subject matter. I simply need some
advise.
I am developing one company presentation that will be run from a CD. This
presentation will be completely done in Flash.
Because it will contain a lot of written info, I want to connect it with some
database.
I don't know how I can create a Flash presentation that communicates with a
database, run search queries, and updates data and still be fully operational
from a normal CD (without the need to use a web server).