Hi,

Instead of having multiple queries (see below), can I combine them into one big query?

I need to see if a record for each user exists in each table.

Hope you can help

------------------------------------------------------
<cfquery datasource="#datasource#" name="getpd">
SELECT * FROM personaldetails WHERE pdemail = '#GetAuthUser()#';
</cfquery>
<cfquery datasource="#datasource#" name="geted">
SELECT * FROM education WHERE EDemail = '#GetAuthUser()#';
</cfquery>
<cfquery datasource="#datasource#" name="getjd">
SELECT * FROM employment WHERE Eemail = '#GetAuthUser()#';
</cfquery>
<cfquery datasource="#datasource#" name="gets">
SELECT * FROM skills WHERE semail = '#GetAuthUser()#';
</cfquery>
<cfquery datasource="#datasource#" name="ptdetails">
SELECT * FROM ptdetails WHERE ptemail = '#GetAuthUser()#';
</cfquery>

--------------------------------------------------------------------------------

I don't think so because you are having different tables with different column names..not sure about your database structure..may be you should try to normalize it in such a way you have to look for e-mail address in one column only.

OK thanks

Would I be able to do it if I have an column called email in each table?

one of the way you can combine results in the query is using UNION operator..not sure you can use in your situation ..

SELECT emailfield FROM TABLE1
UNION
SELECT diffEmailfield From TABLE2

please provide us more details about your tables..
are these tables related in any way..then use INNER JOIN to join this tables..

If you only need to see that record for each user exists in each table.you can create your own structure for UNION operation.

SELECT UserName='#GetAuthUser()#',TableName='personaldeta ils' FROM personaldetails WHERE pdemail = '#GetAuthUser()#'
UNION
SELECT UserName='#GetAuthUser()#',TableName='education' FROM education WHERE EDemail = '#GetAuthUser()#'

See if it help's



Cheers

Sunil

HI,

You need to use inner join.

Select A.* from A Inner Join B on A.EmailID=B.EmailID inner join C on B.EmailID=C.EmailID inner join D on ............

In this way. I am assuming that the email ID column in kept Unique in each table.

Hope u got my point.

B. Anant

Hi,

Thanks for all your help.

I am designing an online job application system, where users can create an online CV.

To make the data more managable, I decided to split up all the different info into seperate tables. The primary key for each table I will be using will be the users email address.

I name all the columns in each table with the initials of the table name first ie columns in the skills table with be prefixes with an 'S', colunms in the personal details table will be prefixed with 'PD'. So each column has a unique name.

So am I right in thinking I cannot use INNER JOIN with this structure?

The fields can have different names, but they must have the same data types and lengths. So you can use inner join.

Hopes you are looking for this...

<cfquery datasource="#datasource#" name="getpd">
SELECT 'PersonalDetails' TableName, count(*) NumberOfRecords
FROM personaldetails WHERE pdemail = '#GetAuthUser()#'
UNION
SELECT 'education', count(*)
FROM education WHERE EDemail = '#GetAuthUser()#'
UNION
SELECT 'employment', count(*)
FROM employment WHERE Eemail = '#GetAuthUser()#'
UNION
SELECT 'skills', count(*)
FROM skills WHERE semail = '#GetAuthUser()#'
UNION
SELECT 'ptdetails', count(*)
FROM ptdetails WHERE ptemail = '#GetAuthUser()#'
</cfquery>

Cheers,
Pooja Falor

how can i learn sql server 2000
and how can i start learning sql server 2000
plz help me ...
[email protected]

Mohammad Qasim