Help iqof188

[ma701sd]

Hi,

Ive come across another problem that I'm hoping someone might be able to help me with??

Im trying to select a unique record from a users table which contains a primary key (username) and a table called userspecific which also contains a field called username which acts as a foriegn key to the user table.

My SQL query is as follows:

<!--- Make sure the right user is being referenced via my application.cfm--->
<CFOUTPUT>
#Client.UserName#
</cfoutput>


<!--- If the variable UserName (person logged in) is not defined then re-direct to Login.cfm else carry on--->
<CFIF NOT IsDefined(&quot;Client.UserName&quot>
<CFLOCATION URL = Login.cfm>
<CFELSE>

<!--- Query my database to select userName from (my only) two tables &quot;Users&quot; and &quot;userSpecifc&quot; where
username logged in matches both a row in the users table and the userspecific table --->
<CFQUERY DATASOURCE = &quot;ONLINE&quot; NAME = &quot;GetUser&quot;>
SELECT distinct Users.UserName, UserSpecific.UserName
FROM Users INNER JOIN USERSPECIFIC ON Users.UserName = UserSpecific.UserName
</CFQUERY>
</CFIF>

Currently, this query returns two records when only one should be received

On the same page, I am writing a query to insert the data captured via the form into a DB, and it is inserting into two rows instead of only 1?? ( i know that if my query above is not correct then the upadate will not be correct)
The update SQL is as follows:

<CFIF #GetUser.RecordCount# EQ 1>
</CFIF>
<CFIF Not IsDefined(&quot;FORM.Title&quot>
Variable not passed
<CFELSE>
<CFQUERY NAME = &quot;DataAdded&quot; DATASOURCE = &quot;Online&quot; >
UPDATE UserSpecific
SET
Title = '#form.title#'
</CFQUERY>




Based on this information, I want to output details from the table, but if the query to update is not cprrect I cant extract the correct data.

Help would be greatly appreciated!!

Cheers

Sam

 

[iqof188]

Hi Sam,

Will this help:

<CFQUERY DATASOURCE = &quot;ONLINE&quot; NAME = &quot;GetUser&quot;>
SELECT Distinct UserName
FROM UserSpecific
WHERE Username='#Client.UserName#'
</CFQUERY>

I can't see why you should query both tables, no need to do that.

Then, check the recordcount and update (watch it, you forgot the WHERE clause there!!):

<CFIF #GetUser.RecordCount# EQ 1>
<CFIF Not IsDefined(&quot;FORM.Title&quot>
Variable not passed
<CFELSE>
<CFQUERY NAME = &quot;DataAdded&quot; DATASOURCE = &quot;Online&quot; >
UPDATE UserSpecific
SET
Title = '#form.title#'
WHERE UserName='#Client.UserName#'
</CFQUERY>
</CFIF>

Hope this helps...






<webguru>iqof188</webguru>

 

[ma701sd]

Cheers Iqof1888 for your valuable help.

It works!!!!!

So a join should be done when I need to pull data from multiple tables...I C now..

Thanks once again!!
Sam