query a query

Forum|Forum|15 years ago
July 9, 2010
2 replies
753 views

I am having a nightmare with this query. I am trying to use the results from 1 query and then use it in the second but it will only give me 1 result when the it should give me 5. The first query returns 5 ids so the second should give me the same

<cfquery name="getquote" datasource="conveyancingshoppers">
select solicitorsid from UUID
where UUID='#UUID#'
</cfquery>
<cfoutput query="getquote">

<CFQUERY name="getname" datasource="conveyancingshoppers">
SELECT *
FROM solicitors
WHERE solicitorsid = #getquote.solicitorsid#

</CFQUERY>
</cfoutput>

I have tried so many different ways and cannot get it to work. In the second query i want to be able to select solicitorsid from the tables solicitors and fees.

Please help me fix this

Advanced techniques

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

Ian gave you the best available answer for situations where the two tables have the same datasource. If they don't, you might still need the book Ian mentioned. Instead of the equal sign, you use the sql keyword "in".

The coldfusion valuelist() function will also come in handy.

C

cybertek23Author

Inspiring

Thanks that works for the 2 tables but i need to add another I have tried

<cfquery name="getquote1" datasource="conveyancingshoppers">
     SELECT *
     FROM solicitors INNER JOIN uuid ON (solicitors.solicitorsid = uuid.solicitorsid)
INNER JOIN uuid ON (fees.solicitorsid = uuid.solicitorsid)
     WHERE UUID='#UUID#'
</cfquery>

but it gives me the error

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(solicitors.solicitorsid = uuid.solicitorsid) INNER JOIN uuid ON (fees.solicitorsid = uuid.solicitorsid)'.

Is there something else i need

ilssac

Inspiring

<cfquery name="getquote1" datasource="conveyancingshoppers">
   SELECT * 
   FROM solicitors INNER JOIN
        uuid ON (solicitors.solicitorsid = uuid.solicitorsid)INNER JOIN
        fees ON (fees.solicitorsid = uuid.solicitorsid)
   WHERE UUID='#UUID#'
</cfquery>

You had linked the uuid table twice and not linked the fees table in your FROM clause.

ilssac

Inspiring

I hear great things about books like "Teach yourself SQL in 10 minutes".

Did you try

<cfquery name="getquote" datasource="conveyancingshoppers">
     SELECT * 'this is a very bad habit
     FROM solicitors INNER JOIN uuid ON (solicitors.solicitorsid = uuid.solicitorsid)
     WHERE UUID='#UUID#'
</cfquery>
<cfdump var="#getQuote#">

Error Executing Database Query.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded