Two select statements in one cfquery

Hello,
Is there any way to have 2 select statements inbetween one cfquery tag besides using say a union join

something like this

<cfquery datasource="FICUS" name="docs">
SELECT Docs.*, Section.Section_Section
FROM Docs, Section
WHERE Docs.Docs_DocumentID = Section.Section_DocumentID AND (Docs.Docs_Featured = yes) AND (Section.Section_Section = '#section.section_section#')

SELECT Orgs.*, Section.Section_Section
FROM Orgs, Section
WHERE Orgs.Org_OrgID = Section.Section_OrgID AND (Orgs.Org_featured = yes) AND (Section.Section_Section = '#section.section_section#')
</cfquery>

I want to do this so that I can have one output to order my data. I get a syntax error now. Any help would be great
Thanks
Kevin
KevinKFLAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bigbadbCommented:
Can you give a little more detail.  Exactly what are you trying to do
0
deepchandaCommented:
I dont think so u can write 2 queries within one <CFQUERY> tag and its allowed by CF. Doing this would cause CF to error out and there would be no possible way of accesing any of the queried columns in the tables. You might want to separate the 2 selects. Can u talk little more about the issue here

DC
0
KevinKFLAAuthor Commented:
I'm trying to select data from my database and having trouble with the output. I want to select only the organizations and documents from the org and docs tables where featured is yes. I also have a third table which contains sections that these docs and orgs can fall under they are linked with a docid and orgid number to the sections table. So what I want to do is pull just those docs and orgs from their tables where the section equals one of the sections in the sections table

I have tried this but it pulls nothing because in the sections table the docid and orgid might not be on the same row so it wont pull anything



section.section_section is pulled through the URL of the website. There are 5 sections that any doc or org can fall under
flora and fauna
public policy
sustainable developments.
water resources
your florida community
so really the code could read

'flora and fauna' instead of '#section.section_section#'


the tables are

Docs
------
docs_docid
docs_iconname
docs_pubdate
docs_title
Docs_Description
docs_author
docs_count
docs_URL
docs_publisher
docs_dateentered
docs_featured
docs_editor
docs_lastupdated


Orgs
-------
Org_Orgid
Org_iconfile
Org_name
Org_description
Org_mission
Org_contact
Org_address
Org_state
Org_city
Org_zip
Org_phone
Org_fax
Org_email
Org_url
Org_datejoined
Org_active
Org_featured

Section
---------
Section_ID
section_documentid
Section_orgid
Section_section


So all I wanna do is pull all documents and all organizations where they are checked as featured and also where the section is say flora and fauna and be able to pull them so that I can have one output. Right now I have 2 queries on the site but I want just one output so that I can sort the data and order it.

Here is the link to the site

http://131.247.163.103/ficus2/features.cfm?Section.Section_Section=Flora%20and%20Fauna
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

deepchandaCommented:
Your SELECT statement would look like this

select Docs_Description, Org_description
from orgs, docs, section
where section_documentid = docs.docs_docid and
section_orgid = orgs.Org_Orgid and
Org_featured = 'yes' and
docs_featured = 'yes' and
Section_section in (#section.section_section#)

But before you can run this SELECT you need to have the variable section.section_section in comma separated list like ('flora and fauna', 'public policy', 'sustainable developments' .....)

let me know if this does not work for u

DC
0
KevinKFLAAuthor Commented:
Yeah I tried that and that didn't work. I tried something like that earlier and it returns just empty columns

SELECT      Docs.*, Orgs.*, Section.Section_Section
FROM         Docs, Orgs, Section
WHERE       Docs.Docs_DocumentID = Section.Section_DocumentID AND
                   Orgs.Org_OrgID = Section.Section_OrgID AND
                   (Docs.Docs_Featured = yes) AND (Orgs.Org_featured = yes) AND (Section.Section_Section = '#section.section_section#')




0
KevinKFLAAuthor Commented:
I don't think the way my database is setup that it can be done with one select statement. I have been working on getting one select statement to work for about a week with no luck. What I would like to do is just have to seperate queries on one page and have it output to one output if that is possible.
0
KevinKFLAAuthor Commented:
Adjusted points to 200
0
KevinKFLAAuthor Commented:
Here is what I have now. All I wanna do is see if this can be made into one output.

<cfquery datasource="FICUS" name="docs">
SELECT Docs.*, Section.Section_Section
FROM Docs, Section
WHERE Docs.Docs_DocumentID = Section.Section_DocumentID AND (Docs.Docs_Featured = yes) AND (Section.Section_Section = '#section.section_section#')
</cfquery>
<cfquery datasource="FICUS" name="orgs">
SELECT Orgs.*, Section.Section_Section
FROM Orgs, Section
WHERE Orgs.Org_OrgID = Section.Section_OrgID AND (Orgs.Org_featured = yes) AND (Section.Section_Section = '#section.section_section#')
</cfquery>
0
KevinKFLAAuthor Commented:
Adjusted points to 300
0
deepchandaCommented:
First to access the values from the query in a cfoutput, use the keyword AS in the SELECT lile "SELECT Section.Section_Section AS section ..... " and then u can access the var as docs.section. If this is the only way its working then u might need the help of CF arrays to store results from query 1 and then use them in conjunction with query2

DC
0
bigbadbCommented:
This is impossible to accomplish unless you have seem key in the docs and orgs table you can match on.  The way CF works is one complete sql statement per <cfquery tag>  That is the just the way it is.  SO you are limited to either two <cfquery tags> or creating some way to link the tables together.  Currently your 2 sql statements are not dependent on one another they separate statements.

Sorry

0
KevinKFLAAuthor Commented:
Ok,
So if I do seperate statements can I output to one output statement?
0
bigbadbCommented:
No but you might be able to get away with a nested loop statement

Maybe an outquery with one statement

then a loop in that of the other statment

Then you can have some cfif that match what ever you are looking for.  Then cancatenate the 2 values.  You can save this value in an array.  Then you will have the output in cancatenated in an array which you can read back to another page if necessary
0
KevinKFLAAuthor Commented:
I'm fairly new to CF about 3 - 4 months and at a loss at how to run a loop statement or outquery an help would be appreciated

0
FRehmanCommented:
I think the best way is that you make two cfquery tag
0
benjaFLCommented:
Your answer lies in making a good database query not in cold fusion code.

The best solution to your problem would be to use a join in your select statement.  Your query would look something like the following.  You should add appropriate fields to the select list that I have not included because I don't know everything you want selected.

SELECT Docs.docs_dociD, Orgs.Org_OrgID, Section.section_ID, Section.section_section
FROM Orgs INNER JOIN (Docs INNER JOIN [Section] ON Docs.docs_dociD = Section.section_documentID) ON Orgs.Org_OrgID = Section.section_orgID
WHERE (((Section.section_section)="flora and fauna") AND ((Orgs.Org_featured)= 'yes');

That should give you all of the information for the particular section that a person is viewing in one select statment.

0
bigbadbCommented:
I agree but kevingkfla specifically said that he did not want to use a join.  So that is why it has been so difficult to come up with a alternative answer.  That is where the multiple loop querys come into play
0
benjaFLCommented:
It would be hard to give a specific example of nested loops without seeing the code you are using to layout the page.  The structure would look something like the below.

<cfquery datasource="FICUS" name="docs">
SELECT Docs.*, Section.Section_Section
FROM Docs, Section
WHERE Docs.Docs_DocumentID = Section.Section_DocumentID AND (Docs.Docs_Featured = yes) AND (Section.Section_Section = '#section.section_section#')
</CFQUERY>

<cfquery datasource="FICUS" name="Orgs">
SELECT Orgs.*, Section.Section_Section
FROM Orgs, Section
WHERE Orgs.Org_OrgID = Section.Section_OrgID AND (Orgs.Org_featured = yes) AND (Section.Section_Section = '#section.section_section#')
</cfquery>

<CFLOOP QUERY="docs">
<CFOUTPUT>
code to display docs here
<CFOUTPUT>
<CFLOOP QUERY="Orgs">
<CFOUTPUT>
code to display organizations here
</CFOUTPUT>
</CFLOOP>
</CFLOOP>

The above layout would output all of the organizations from the query for every book in the outer query.  Probably not the results you desire but an example of nested loops.  Again it is hard to give a specific example without knowing how you want the page laid out.

It just seems a lot less painstaking to me to get the information in one query as in my previous comment.
0
KevinKFLAAuthor Commented:
I tried to make it one query for the past week and posted a question in the SQL section and couldn't get a query to work. That is why I am trying 2 queries instead of one. I will try the one query that was posted and see if that works. The reason I was asking for one output from 2 queries was because I couldn't get the database to pull the right data from one query. What it was doing was pulling multiple rows of the same info.
0
KevinKFLAAuthor Commented:
benjaFL,

I tried that query you sent me and it just pulls empty columns. I think the problem is in the design of the databse it's self. I was not the designer of the database and am really just trying to work with the structure that it has. The best thing would be to restructure the database but I'm not really sure on how to link the orgs and docs table to pull the data I need to pull
0
benjaFLCommented:
Try this query.  I setup an access database exactly as you specified the tables were in your database and the following query returned results with information in all of the columns.

SELECT Docs.docs_dociD, Orgs.Org_OrgID, Orgs.Org_Featured, Section.section_ID, Section.section_section
FROM Orgs INNER JOIN (Docs INNER JOIN [Section] ON Docs.docs_dociD = Section.section_documentID) ON Orgs.Org_OrgID = Section.section_orgID
WHERE (((Orgs.Org_Featured)="Y") AND ((Section.section_section)="flora and fauna"));

You may need to change the values in the WHERE clause to reflect actual data in your database.  Also, I did not include every field in the DOCS and ORGS tables so you may need to modify the SELECT list.
0
KevinKFLAAuthor Commented:
I tried what you sent me and I get empty columns like I did before. Not sure why it is doing this. I can send you the database that I have if you would like to see that data that it holds
0
benjaFLCommented:
Send the database to ben@electronet.net and I will build the query for you.
0
KevinKFLAAuthor Commented:
ok it's on it way thanks
0
benjaFLCommented:
Try this query

SELECT Section.Section_ID, Section.Section_Section, Docs.Docs_DocumentID, Orgs.Org_OrgID, Orgs.Org_featured
FROM Orgs RIGHT JOIN (Docs RIGHT JOIN [Section] ON Docs.Docs_DocumentID = Section.Section_DocumentID) ON Orgs.Org_OrgID = Section.Section_OrgID
WHERE (((Section.Section_Section)="flora and fauna") AND ((Orgs.Org_featured)=Yes));
0
benjaFLCommented:
Try this query

SELECT Section.Section_ID, Section.Section_Section, Docs.Docs_DocumentID, Orgs.Org_OrgID, Orgs.Org_featured
FROM Orgs RIGHT JOIN (Docs RIGHT JOIN [Section] ON Docs.Docs_DocumentID = Section.Section_DocumentID) ON Orgs.Org_OrgID = Section.Section_OrgID
WHERE (((Section.Section_Section)="flora and fauna") AND ((Orgs.Org_featured)=Yes));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinKFLAAuthor Commented:
I ended up going about it a different way. I setup a stored procedure that joins the 2 tables into one table with just he info I need to pull on the page. This way I can do one query. But thanks for all the help and will accept your comment as the answer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.