SQL join

[sikhar]

I am trying to join three acess tables in a recordset and
trying to acess their their contents. for ex . if we have
a customer table having fields id,name and another table
Payment having fields id,amount.

now if rst is the recordset
then rst!id,rst!name,rst!amount does not work
then how can I acess those values to put in some variables

[Anith Sen]

Sikhar,

Please post access questions in Access Newsgroups. You have better chances
of getting better & accurate answers there.

--
- Anith
( Please reply to newsgroups only )

[Nikhil Patel]

try rst.fields("id").value


"sikhar" <sikharsaikia@yahoo.co.in> wrote in message
news:524a01c3418e$466dfa80$a401280a@phx.gbl...

> I am trying to join three acess tables in a recordset and
> trying to acess their their contents. for ex . if we have
> a customer table having fields id,name and another table
> Payment having fields id,amount.
>
> now if rst is the recordset
> then rst!id,rst!name,rst!amount does not work
> then how can I acess those values to put in some variables

[Mattastic]

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>

[paross1]

Do you actually need to select and use the data from each table, or are you just interested in whether or not a record exists?

Phil

[Mattastic]

Hi,

I just need to see if the record exists.

Thanks for your help

[paross1]

One way to do this would be as in the attached. You have two columns returned,
type and type_count, and you will get 5 rows returned. If the type_count is 0
for a row, then there are no records for that type, etc.

Phil



<cfquery datasource="#datasource#" name="getstuff">
SELECT 'getpd' AS type,
count(*) AS type_count
FROM personaldetails
WHERE pdemail = '#GetAuthUser()#'
GROUP BY get_type
UNION
SELECT 'geted' AS type,
count(*) AS type_count
FROM education
WHERE EDemail = '#GetAuthUser()#'
GROUP BY get_type
UNION
SELECT 'getjd' AS type,
count(*) AS type_count
FROM employment
WHERE Eemail = '#GetAuthUser()#'
GROUP BY get_type
UNION
SELECT 'gets' AS type,
count(*) AS type_count
FROM skills
WHERE semail = '#GetAuthUser()#'
GROUP BY get_type
UNION
SELECT 'ptdetails' AS type,
count(*) AS type_count
FROM ptdetails
WHERE ptemail = '#GetAuthUser()#'
GROUP BY type
</cfquery>

[MarkWright]

Is your database normalized? Why do you need to search every field for the
same data? The user's name should only be stored once in the database.
Database structure is VERY important. I would first normalize your database
before you go too far. I can recommend a few sources for you if you need some
help.

[Tom CF]

<cfquery datasource="#datasource#" name="alldetails">
SELECT
*
FROM
personaldetails PD, education ED, employment EM, skills SKS, ptdetails PT
WHERE
PD.pdemail = ED.EDemail
AND
ED.EDemail = EM.Eemail
AND
EM.Eemail = SKS.semail
AND
SKS.semail = PT.ptemail
</cfquery>


Anyone, Can this be the solution?

[talon1976]

Originally posted by: MarkWright
Is your database normalized? Why do you need to search every field for the
same data? The user's name should only be stored once in the database.
Database structure is VERY important. I would first normalize your database
before you go too far. I can recommend a few sources for you if you need some
help.

I'd say it is normalised cause they only seem to be ooking for the user in
each table... so only and id would be stored in each table for that user.

If the user is in one table are they goning to be in all or is it random for
what tables they are in???
Inner Join is no good for you as it will only return if the user is in all
tables... one small amendment forthe ultiple sql statements i would make is
rather than "SELECT *" change it to "SELECT [specific criteria]" so as to
reduce amount of resources used by the statements.

[Mattastic]

Thanks for all your replies. I am creating a system where users can apply for
jobs online, so I have split up all the different criteria into different
tables. ie personal details, education details, etc. I am using their email
address as the primary key in each table. However by doing it this way I have
alot of queries on my pages. Am I doing this wrong? Thanks for all your help

[MarkWright]

I agree with Phil. What a great post! PK fields should never be directly
related to data (in this case email, but sometimes db programmers use unique
numbers like SSN). This is ALWAYS a bad idea. Data changes. A PK should be
completely separate of the data.

I find it helpfull to draw a huge chart of the tables, the fields they
contain, what the fields is for, and how they relate. I refer to it often when
writing queries. In the long run, this speeds up the process immensly.

[Mattastic]

Thats great thanks for all your help. I have changed my DB structure, so that
when a user logs in, a unique ID is generated for a primary key and used in
each table. Would it be good practice, to set the name attribute of the
cfloginuser tag, to the unique ID, then use the function #GetAuthUser()#, in
queries for data? Thanks for your help

[paross1]

I would use a different value for logon name than userID, so that the name
could be changed in the future, if necessary. You would use a query to retrive
the userID, and password etc., from the database, for the authentication
process, then set whatever client, cookie, or session variables that are
necessary for you to track logon state.

Phil

[Mattastic]

Thanks again. Is the cfuserlogin tag the same as a session variable? Is it
secure enough to do : <cfloginuser name =
'#check_user.email#,#check_user.loginID#' password ='#check_user.password#'
roles = 'user'> Therefore I can use #listrest(GetAuthUser())# to get the
loginid for each user, it saves having to code a new session variable.

[paross1]

Sorry, since I initially developed all of my applications under CF 4.5, I have
never personally used cfloginuser, cflogin, or GetAuthUser. I ended up having
to develop my own application authentication.

Phil