Combining SQL Queries

Report · Aug 07, 2011

I am having trouble getting my queries to work and hope someone can help. Here's my problem.

I have two tables (tableA and tableB). TableA has the following fields (ID, user, record, firstname, lastname, middlename) and tableB has these fields (ID, user, record, birthdate, graduationdate, GEDdate).

The two tables will have unique ID's, user's and records. I need to be able to merge the data and then query for an exact row, that matches the ID, user and record or either query for the exact data and then merge it together.

I need to be able to do a cfoutput query and get all the fields from both tables for one person that has a unique ID, user and record.

When I try this:

<cfquery name="lddata" datasource="#xxxxx#">
    SELECT ID, user, record, firstname, lastname, middlename, birthdate, graduationdate, GEDdate
     FROM tableA, tableB
     WHERE tableA.ID = tableB.ID
    AND tableA.record = tableB.record
    AND tableA.user = tableB.user
</cfquery>

When I do this I get all the data, but am not able to select the exact row I need. For example, I need to be able to say.

WHERE ID=xxxx and user=xxxx and ID=xxxx

Any help would be greatly appreciated.

Thanks.

Andre

Report · Aug 07, 2011

Where does xxxx come from?

Report · Aug 07, 2011

Hi Dan

sorry about that. WHERE ID = #session.ID# and user = #session.user# and record = #session.record#

I put the xxxx's there temporarly as I was cutting and pasting.

Andre

Report · Aug 08, 2011

Hi Andre Martinez,

It seems that you want join the tables. Please look at the following query it may helps you.

<cfquery name="lddata" datasource="#xxxxx#">
    SELECT ID, user, record, firstname, lastname, middlename, birthdate, graduationdate, GEDdate
     FROM tableA, tableB
     WHERE tableA.ID = tableB.ID
    AND tableA.record = tableB.record
    AND tableA.user = tableB.user

AND tableA.ID=xxxx AND tableA.user=xxxx AND tableA.record=xxxx

</cfquery>

Thanks

Saurav

Report · Aug 09, 2011

Hi Saurav,

Thank you, thank you, thank you! It works perfect. I had tried a version of

that but for some reason I kept receiving an error.

I greatly appreciate your help.

Andre