query a query

[cybertek23]

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

[ilssac]

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#">

[Dan_Bracuk]

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.

[cybertek23]

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]

<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]

This should not affect the results at all, but since the UUID table seems to be the driving table of this query, I would probably right it this way to be a little more clear.

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

[cybertek23]

I have worked out how to completed it

<cfquery name="getcombined" datasource="conveyancingshoppers">
SELECT s.*,f.*,u.*
FROM solicitors s, fees f , UUID u
WHERE u.UUID='#UUID#'
AND u.solicitorsid = s.solicitorsid
AND s.solicitorsid = f.solicitorsid

</cfquery>

thanks for you help