A Simple Record Count Question 1

[monoone]

I know, if it is simple whay are you asking?

Here is my Query:

------------------------------------------
<CFQUERY NAME="GetContacts" DBTYPE="ODBC" DATASOURCE="#request.datasource#">

SELECT ContactType.*, Contact.*

FROM Contact, ContactType

WHERE
ContactType.ContactType = 'Web Sign-Up' AND
Contact.AccountID = #session.AccountID#

</CFQUERY>
---------------------------------------

Here is my output:

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

<CFOUTPUT QUERY="GetContacts">You Have #RecordCount# Subscribers</CFOUTPUT>

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

The problem is that my output is giving me the right number of records but it is reapeating it the 13 times (the amount of records in DB)

Please help.

Thanks,

Eric

 

[r937]

the 'Web Sign-Up' row from the ContactType table is being joined to every row in the COntact table for that account

this is called a cross join effect

you've omitted your join condition

you probably only want the single contact from that account that is 'Web Sign-Up' type?

rudy

http://r937.com/

SQL Consulting

 

[monoone]

That is correct - so the SQL is wrong?

 

[r937]

the SQL is wrong only insofar as it's missing the join condition

how are the two tables related, i.e. by what columns?

rudy

http://r937.com/

SQL Consulting

 

[Wullie]

<CFOUTPUT QUERY="GetContacts">You Have #RecordCount# Subscribers</CFOUTPUT>

You are asking Coldfusion to repeat for every row returned. Try the following instead:

<CFOUTPUT>You Have #Getcontacts.RecordCount# Subscribers</CFOUTPUT>

Hope this helps

Wullie

Fresh Look - Quality Coldfusion Hosting

http://www.tutorial-world.com

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[r937]

wullie, well spotted!

rudy

http://r937.com/

SQL Consulting

 

[monoone]

I am at work - I will try tonight - Than you.

-Eric

 

[monoone]

This still duplicates. Could it be the session???

-Eric

 

[r937]

unless you've added a join condition, you are probably still getting a cross join effect in your query

can you please show your query? as well, show the table layouts and indicate how the tables relate

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[monoone]

Here are the Tables:

---------
Account
----------
AccountID
AccountName
Password
----------


----------
Contact
----------
ContactID
ContactTypeID
AccountID
FirstName
LastName
Email
----------


-------------
ContactType
------------
ContactTypeID
ContactType
AccountID
-------------

Here is the Query:

<CFQUERY NAME="GetContacts" DBTYPE="ODBC" DATASOURCE="#request.datasource#">

SELECT ContactType.*, Contact.*

FROM Contact, ContactType

WHERE
ContactType.ContactType = 'Web Sign-Up' AND
Contact.AccountID = #session.AccountID#

</CFQUERY>
-----------------


Thanks,

Eric

 

[r937]

select ContactType.*
     , Contact.*
  from Contact
inner
  join ContactType 
    on Contact.AccountID
     = ContactType.AccountID
   and ContactType.ContactType 
     = 'Web Sign-Up' 
 where Contact.AccountID 
     = #session.AccountID#

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[monoone]

I guess I was being lazy. I did a join, the inner join this gave me an error so I did this and it seems to work:

<CFQUERY NAME="GetContacts" DBTYPE="ODBC" DATASOURCE="#request.datasource#">

SELECT ContactType.*,
Contact.*,
Account.*

FROM ContactType,
Contact,
Account

WHERE ContactType.AccountID = Account.AccountID AND
Contact.AccountID = Account.AccountID AND
Account.AccountID = #session.AccountID# AND
ContactType.ContactType = 'Web Sign-Up'

</CFQUERY>

Thanks for the help.

-Eric