Start Free Trial

Electriciansnet

asked on

Return only 1 row in email column from tbl members accompanied by every row that matched from tbl users

I have found a way (with experts help) to combine my queries for cfmail but with the query below, every member gets a seperate email for every row in tbl_users that
matches their memberID. I have to find a way to return only 1 Contact_Email from tbl_members to accompanied by every row from tbl_users that matches .

SELECT
companyName, memberID, contact_Email, member_since,
users.City ,ZipCode, U_Name, D_Phone, C_Phone, Email,
Service , comments , hit_date, qMemberID_1 , qMemberID_2, qMemberID_3
from members, users
where (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
or qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
or qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2))

AND users.qMemberID_1 = members.memberID
OR users.qMemberID_2 = members.memberID
OR users.qMemberID_3 = members.memberID
AND users.hit_date >= dateAdd(dd,-30,getDate())

I should think this would result in the set you need:
SELECT DISTINCT(memberID), companyName, contact_Email, member_since,
users.City ,ZipCode, U_Name, D_Phone, C_Phone, Email,
Service , comments , hit_date, qMemberID_1 , qMemberID_2, qMemberID_3
from members, users
where (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
or qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
or qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2))

AND users.qMemberID_1 = members.memberID
OR users.qMemberID_2 = members.memberID
OR users.qMemberID_3 = members.memberID
AND users.hit_date >= dateAdd(dd,-30,getDate())

Electriciansnet

ASKER

Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:16, Msg 421, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.

Try removing the parens from around memberID.

Electriciansnet

ASKER

However memberID is of type: INT autonumber. Anyway, and you probably know something I dont, I need everything in tbl_members to be distinct accompanied by every row in tbl_users that matches

Which table is contact_email in?

I think I understand now. Will have a new one in a few minutes...

Electriciansnet

ASKER

contact_email is in members

Electriciansnet

ASKER

Also tried this which is not error but still returns all rows.
SELECT
memberID, contact_Email, member_since,
users.City ,ZipCode, U_Name, D_Phone, C_Phone, Email,
Service , comments , hit_date, qMemberID_1 , qMemberID_2, qMemberID_3
from users right join members on users.qmemberID_1 = members.memberID or users.qmemberID_2 = members.memberID or users.qmemberID_3 = members.memberID
where (qMemberID_1 IN (Select <b>distinct</b> (memberID) From Members WHERE datepart(day, member_since) = datepart(day, getdate())-3 )
or qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
or qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2))

AND users.qMemberID_1 = members.memberID
OR users.qMemberID_2 = members.memberID
OR users.qMemberID_3 = members.memberID
AND users.hit_date >= dateAdd(dd,-30,getDate())
You may want to have a look at previous post to see what I'm trying to do:

multiple values throughout cfm...

Sorry - I am having trouble. Can you please post the DDL for members and users, a sample of the content of each, and an example of what you want for output. I'm still not clear on what you mean by:
"I need everything in tbl_members to be distinct accompanied by every row in tbl_users that matches "

With samples, I can build someething that works here instead of posting back and forth so much.

Thanks,

Jim

Thanks - I'm looking through the previous post now...

Electriciansnet

ASKER

Sorry, I'm not sure what DDL is but in a nutshell cfschedule points to 30daysmail.cfm. If members.member_since(enroll date) hits on the same day of the month as the daily cfschedule then a status update goes out to the member with a report containing every user that hit on their zip code that month. The problem is that cfmail's query attribut only allows for 1 query (i.e. I cannot put a seperate <cfoutput query="getUsers...."> within the body so I have to figur a way to make 1 query just right- to only return 1 #members.companyName# and 1 #members.Contact_Email# with many #users.results#.
A user can only return up to 3 members with their query and when they submit their form to the member all their data is inserted into users along with qmemberID_1 which matches the queried member's memberID if they got 1 result or qmemberID1 and qmemberID2 if 2 results and qmemberID1, qmemberID2, qmemberID3 if 3 results. I hope this helps. Go to www.electriciansnet.com and use 99599 as your model and enter bogus info to see what I mean. You have sent a mail that might be the answer. Thanks, Andy

Sorry - DDL is an acronym for Data Definition Language in SQL-speak. It means the script that defines the table or other object.

I tried your link and got a blank page.

Electriciansnet

ASKER

OH NO! Let me check that out. I'll be right back I think it's a scheduled maintenance thing.

Electriciansnet

ASKER

OK its up now. A contractor that has been helping me had this in my Application.cfm. Obviously there is a bug!

Electriciansnet

ASKER

I think this is what you mean:
SELECT [memberID]
,[companyName]
,[streetNumber]
,[City]
,[State]
,[Contact_First]
,[Contact_Last]
,[Contact_title]
,[Contact_Phone]
,[Contact_Email]
,[PassWord]
,[Contact_URL]
,[License]
,[CellNumber]
,[NetworkID]
,[Slogan]
,[member_Since]
,[logo]
FROM [enetdb1SQL].[dbo].[members]

SELECT [uID]
,[City]
,[ZipCode]
,[U_Name]
,[D_Phone]
,[C_Phone]
,[Email]
,[Service]
,[comments]
,[hit_date]
,[qMemberID_1]
,[qMemberID_2]
,[qMemberID_3]
FROM [enetdb1SQL].[dbo].[users]

There is also memberzip which is the pivot table that contains the many zip codes for each member.
Your message just came to my cell phone
"Jim Bra.., Experts Exchange, MN, Day Phone 800-555-1212 Message: Need: sol

I ran through your page, and have an idea what you are doing, but am still a bit confused about the select. When you say this:
"need everything in tbl_members to be distinct accompanied by every row in tbl_users that matches "

do you mean you want a dataset returned like:
member.memberID, member.contact_Email, member.etc, user1.*
null, null, null, user2.*
null, null, null, user3.*
etc?

Electriciansnet

ASKER

Now you will get thank you mail if you entered good email but not the same as what we are working on. That one was a sinch. Your data is all in table useres including"nedd solar power- I'm off the grid (this may be a chalenge!) but you also inserted 165 qMemberID_1 since you only got 1 result and 99599 is one of member 165's zip codes.

Sorry - I didn't mean to give you a call in the middle of the night.

Electriciansnet

ASKER

To try and answer your last question-
member 165 has zip 99599 plus up to 100 more zip codes under his memberID in tbl_memberzip.
On every 30 day anerversery for member 165 (lets say day 15)he could have had many hits within that month. Also there could be many other members that enrolled on day 15. When cfschedule hits that page on a daily basis what needs to be returned is 1 email and company name for 165 and every row from users that hit on 165 and the same for 166 if that is his day of the month. The mail looks like this:

condensed version:
<cfmail to = "#Contact_Email#"
from = "member_services@electriciansnet.com"
Subject = "30 Day Status Report"
query="getUsers"
type="HTML">

Hello #getUsers.CompanyName#! You have now been a member for #DateDiff("d", member_since, Now())# months.<br />
#getUsers.RecordCount# Consumers Have Submitted A Request To Your Company This Month:
The results were: <td>#getUsers.U_Name#</td>
<td>#getUsers.City#</td>
<td>#getUsers.ZipCode#</td> etc, etc.
</cfmail>

What is currently happening is: If member 165 has 3 results in that mont he gets 3 emails with 1 result in each because member@hisEmail is being returned 3 times fro the query.
So I tried using this tag and WHAT A MESS!!
<cfset EmailList = ValueList(getUsers.Contact_Email , ";")>
Every member gets every members results with a seperate email for each result which is all wrong.
I hope this info gives you the feel of what I'm trying to do. To put it simply: I'm trying to send my members their results every month so they can't say they have not been getting any.

Electriciansnet

ASKER

The call is no problem I knew I would get it- I greatly appreciate your help, Jim!

Electriciansnet

ASKER

Sorry to keep blowing up your in box but you may be thrown by the"getdate())-2)" in the query. This is only to bring me results now for production purposes. After we get it working it will read:
"where (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate()))" to hit on that day only

Don't worry about the posts - this is the only way to get stuff through my thick head some times. Is what you want then more like this:
member1.Stuff, user1-1.stuff, user1-2.stuff, user1-3.stuff
member2.Stuff, user2-1.stuff, user2-2.stuff, user2-3.stuff
etc.

So the whole set for each member comes back in one row?

Andy - It's past my bedtime. I'll check back here in the morning.

Jim

I would just not use the query attribute on the cfmail tag. instead loop through the results of the first query, and do the second query within the loop like

<cfquery name="FirstQuery" datasource="MyDsn">
SELECT ...
</cfquery>

<cfloop query="FirstQuery">
      <cfquery name="SecondQuery" datasource="MyDSN">
      SELECT ...
      </cfquery>
      <cfmail to="#FirstQuery.EmailAddress#" from="MyAddress@MyDomain.com" subject="MySubject" type="HTML">
            blah blah blah
            <cfloop query="SecondQuery">
            #SecondQuery.Columns#
      </cfloop>
      </cfmail>
</cfloop>

Electriciansnet

ASKER

Jim:
Yes that is exactly right as long as we dont get member1.email or companyName more than 1 time.
This is the cfdump from the old "ValueList" tag:
rowehl@aol.com;member_services@electriciansnet.com;member_services@electriciansnet.com
do you see how it lised memberservices twice? This is where I was saying that every user got every query 1 per email.

Now that your on the right track, can we do it the way SBennett recomended? Because I have had pretty good results in terms of accuracy with cfloop in the past.
Also, dgrafx: has posted a new query back on the old question "multiple values throughout cfmail/ multiple recipients in to="" line as a reult of cfschedule: that may be worth looking at. Wednesdays are my night to take the kids out to eat but I'm hyped about getting this taken care of so I'll be back ASAP

I know SQL, but have never worked with ColdFusion.

Jim

Electriciansnet

ASKER

dgrafx: SAYS
try this
<cfquery name="getMembers" datasource="#enet#">
SELECT 
members.companyName, members.memberID, members.contact_Email
from users
inner join members on
(users.qMemberID_1 = members.memberID
or users.qMemberID_2 = members.memberID
or users.qMemberID_3 = members.memberID)
Where users.hit_date >= dateAdd(dd,-30,getDate())
and members.member_since >= dateAdd(dd,-2,getDate())
and memberID IN
(SELECT a.memberID
FROM members a, members b
WHERE a.memberID != b.memberID AND a.contact_Email = b.contact_Email)
</cfquery>

<CFMAIL FROM="#avalidemailaddress#"
TO="#contact_Email#"
SUBJECT="#yoursubject#"
SERVER="#mailserver#"
query="getMembers"
Type="html">

<cfquery name="getUsers" datasource="#enet#">
Select users.City ,users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email,
users.Service , users.comments , users.hit_date, users.qMemberID_1 , users.qMemberID_2, users.qMemberID_3
from users
where users.qMemberID_1 = #getMembers.MemberID#
or users.qMemberID_2 = #getMembers.MemberID#
or users.qMemberID_3 = #getMembers.MemberID#
</cfquery>
<cfloop query="getUsers">
#stuff#
</cfloop>
</cfmail>

Electriciansnet

ASKER

Hi Jim, if your around please look at his last line in getMembers:

WHERE a.memberID != 'b.memberID' AND 'a.contact_Email' = 'b.contact_Email')

I know != means not equal but did he mean to use != instead of = on right side as well?
Because it is not returning any rows.

If so, the != format does not work with datatype text.

I'm here - try <> in place of !=, and I'll try to figure out what it is doing.

This bit of the where clause:
memberID IN
(SELECT a.memberID
FROM members a, members b
WHERE a.memberID != b.memberID AND a.contact_Email = b.contact_Email)

Will, I think, select all members that share an equal value in contact_Email.

If you change it to:
memberID IN
(SELECT a.memberID
FROM members a, members b
WHERE a.memberID <> b.memberID AND a.contact_Email <> b.contact_Email)

It will return all members who do not share a contact_Email value with any other member.

Electriciansnet

ASKER

OK but we are looking to eliminate duplicate emails from same member. On his query if I change the right side of that line from "AND a.contact_Email = b.contact_Email)" to "AND a.contact_Email != b.contact_Email)"
it returns several rows of repeating email address but if I leave as is it returns no rows. I will try your way.

Electriciansnet

ASKER

Hey Jim I have an idea- Go to http://www.electriciansnet.com/loop_test1.cfm to see what the below returns.

<cfquery name="getMembers" datasource="#enet#">
SELECT
members.companyName, members.memberID, members.contact_Email
from users
inner join members on
(users.qMemberID_1 = members.memberID
or users.qMemberID_2 = members.memberID
or users.qMemberID_3 = members.memberID)
Where users.hit_date >= dateAdd(dd,-30,getDate())
and members.member_since >= dateAdd(dd,-6,getDate())
and memberID IN
(SELECT a.memberID
FROM members a, members b)
and memberID IN
(SELECT a.memberID
FROM members a, members b
WHERE a.memberID <> b.memberID)
AND 'a.contact_Email' <> 'b.contact_Email'
</cfquery>



<cfquery name="getUsers" datasource="#enet#">
Select users.City ,users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email,
users.Service , users.comments , users.hit_date, users.qMemberID_1 , users.qMemberID_2, users.qMemberID_3
from users
where users.qMemberID_1 = '#getMembers.MemberID#'
or users.qMemberID_2 = '#getMembers.MemberID#'
or users.qMemberID_3 = '#getMembers.MemberID#'
</cfquery>






<cfoutput query="getMembers">
<table border="3" cellpadding="1" cellspacing="1" cols="10" align="center">
<td>#getMembers.memberID#</td>
<td>#getMembers.CompanyName#</td>
<td>#getMembers.Contact_Email#</td>
              </table>
              </cfoutput>
<cfloop query="getUsers">

<table border="3" cellpadding="1" cellspacing="1" cols="10" align="center">
<tr>
<td>Name</td>
<td>City</td>
<td>Zip</td>

<td>Day Phone</td>
<td>Evening/Cell Phone</td>
<td>Email</td>
<td>Service</td>
<td>comments</td>
<td>Hit Date</td>
              <td>Member ID</td>
              <td>Company Name</td>
              <td>Contact Email</td>
              <td>Current Row</td>
              <td>OF</td>

</tr>

<tr>
<td>#getUsers.U_Name#</td>
<td>#getUsers.City#</td>
<td>#getUsers.ZipCode#</td>

<td>#getUsers.D_Phone#</td>
<td>#getUsers.C_Phone#</td>
<td>#getUsers.Email#</td>
<td>#getUsers.Service#</td>
<td>#getUsers.comments#</td>
<td>#getUsers.hit_date#</td>

<td>#getUsers.currentRow#</td>
              <td>#RecordCount#</td>
</tr>

<tr>

</tr>

</table>
</div>

      <img src="http://www.electriciansnet.com/picts/Fireworks/Spacers/chrome_w_1.png" alt="spc1" height="32" width="100%" />
 </cfloop>
<cfdump var="#getUsers#"><cfdump var="#getMembers#">

Electriciansnet

ASKER

If you need me to, I can send you a backup of the tables so that you can adjust accordingly

Electriciansnet

ASKER

I just thought of something- Your query might just work because, if I'm not mistaken, MSQuery is going to put an email address in every row no matter what when a cfoutput tag might not. Maybe we can put your query in a loop like his

Electriciansnet

ASKER

Now its my bed time but I'm thinking maybe I could do a top query like this:

<cfquery name="getMemberIDs" datasource="#enet#">
SELECT DISTINCT memberID FROM members
WHERE datepart(day, member_since) = datepart(day, getdate())
</cfoutput>
Thin do the other query based on:
WHERE memberID = getMemberIDs.memberID

Could this work to return only 1 email per member?

Andy - I got distracted for a while. Had to take my granddaughter to Urgent Care - ended up to be nothing serious, but ate a lot oc clock cycles.

I built two simple test tables - left out the dates, because thay are a noisy distraction for the current main issue. Here are the tables:
Create table members
(
memberid number(5) constraint nnmem1 NOT NULL,
company_name varchar2(40),
contact_email varchar2(40),
CONSTRAINT PKMEMBERS PRIMARY KEY (memberid)
);

Create table testusers
(
u_id number(5) constraint nntu1 NOT NULL,
u_name varchar2(40),
email varchar2(40),
qmemberid_1 number(5),
qmemberid_2 number(5),
qmemberid_3 number(5),
CONSTRAINT PKTUSERS PRIMARY KEY(u_id)
);

And here is the test data I entered:
insert into members values( 1, 'AAA Electric', 'larry@aaaelectric.com');
insert into members values( 2, 'BBB Electric', 'moe@bbbelectric.com');
insert into members values( 3, 'CCC Electric', 'curley@cccelectric.com');
insert into members values( 4, 'DDD Electric', 'shep@dddelectric.com');
commit;

insert into testusers VALUES( 1, 'Albert', 'Albert@home.com', 1, 2, 0 );
insert into testusers VALUES( 2, 'Bob', 'Bob@home.com', 1, 3, 0 );
insert into testusers VALUES( 3, 'Charles', 'Charles@home.com', 2, 3, 4 );
insert into testusers VALUES( 4, 'Dilbert', 'Dilbert@home.com', 1, 2, 3 );
insert into testusers VALUES( 5, 'Ed', 'Ed@home.com', 3, 4, 0 );
insert into testusers VALUES( 6, 'Frank', 'Frank@home.com', 2, 3, 0 );
insert into testusers VALUES( 7, 'George', 'George@home.com', 1, 4, 0 );
insert into testusers VALUES( 8, 'Harold', 'Harold@home.com', 2, 4, 0 );
insert into testusers VALUES( 9, 'Ike', 'Ike@home.com', 4, 0, 0 );
commit;

Now, using this select:
Select memberid, company_name, contact_email, u_id, u_name, email, sortcol
FROM( Select memberid, company_name, contact_email, null u_id, null u_name, null email, 1 as sortcol
FROM members a )
UNION ( Select memberid, null company_name, null contact_email, u_id, u_name, email, 2 as sortcol
FROM members b INNER JOIN testusers
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3)
ORDER BY memberid, sortcol, u_id;

I got these rows returned:
MEMBERID COMPANY_NAME CONTACT_EMAIL U_ID U_NAME EMAIL SORTCOL
-------- ------------ --------------------- ---- ------- --------------- -------
1 AAA Electric larry@aaaelectric.com 1
1 1 Albert Albert@home.com 2
1 2 Bob Bob@home.com 2
1 4 Dilbert Dilbert@home.com 2
1 7 George George@home.com 2
2 BBB Electric moe@bbbelectric.com 1
2 1 Albert Albert@home.com 2
2 3 Charles Charles@home.com 2
2 4 Dilbert Dilbert@home.com 2
2 6 Frank Frank@home.com 2
2 8 Harold Harold@home.com 2
3 CCC Electric curley@cccelectric.co 1
3 2 Bob Bob@home.com 2
3 3 Charles Charles@home.com 2
3 4 Dilbert Dilbert@home.com 2
3 5 Ed Ed@home.com 2
3 6 Frank Frank@home.com 2
4 DDD Electric shep@dddelectric.com 1
4 3 Charles Charles@home.com 2
4 5 Ed Ed@home.com 2
4 7 George George@home.com 2
4 8 Harold Harold@home.com 2
4 9 Ike Ike@home.com 2

Copy the results and paste into a simple text editor like TextPad or NotePad so the data lines up with the column headers, and I think we may finally have something close to what you need. If that's it, just add the date filters into where clauses for the two selects that feed the union, and I think we could be there.

Jim

In response to your previous post, yes. If you are allowed to use two queries instead of one, it's easy.

Jim

Electriciansnet

ASKER

Excellent-Excellent-Excellent Jim! I'll have to say that you must be an SQL Genius!!! Thank You and I hope your granddaughter is ok!
Now just a little more cookies on the bottom shelf before I start copying and pasting:
I obviously don't need to create table members but do I need to alter it in any way?
Do I need to create testUsers (Probably not)?
If I do have to alter tables, will it affect my main main queries that you saw in action when you visited my site?
I got the error below when I pasted SELECT statement into MSQuery and replaced testusers with users
but thats probably because there are columns in testusers that arent in users. Thanks again, Andy

Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:15, Msg 156, State 1, Line 4
Incorrect syntax near the keyword 'UNION'.

No need to alter any of your tables, just alter this select to match your tables. I just made small tables for testing.
Mine was tested with Oracle - that's what I use the most. You may need an alias after the selects; i.e.
Select memberid, company_name, contact_email, u_id, u_name, email, sortcol
FROM( Select memberid, company_name, contact_email, null u_id, null u_name, null email, 1 as sortcol
FROM members a ) memberRow
UNION ( Select memberid, null company_name, null contact_email, u_id, u_name, email, 2 as sortcol
FROM members b INNER JOIN testusers
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3) userRow
ORDER BY memberid, sortcol, u_id;

Electriciansnet

ASKER

I know this error is something I'm taking too literally but I have seen you use column names that I did not have and it worked berfore. Anyway, here is the error from the above SELECT now:

Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:15, Msg 102, State 1, Line 6
Incorrect syntax near 'userRow'.

Electriciansnet

ASKER

Here is what I have now:

Select memberid, companyname, contact_email, uid, u_name, email, sortcol
FROM( Select memberid, companyname, contact_email, null uid, null u_name, null email, 1 as sortcol
FROM members a ) memberRow
UNION ( Select memberid, null companyname, null contact_email, uid, u_name, email, 2 as sortcol
FROM members b INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3)
ORDER BY memberid, sortcol, u_id;

And here is error:

Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:16, Msg 421, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.

I see no DISTINCT in that statement?

You might try this:
Select memberid, companyname, contact_email, uid, u_name, email, sortcol
FROM( Select memberid, companyname, contact_email, null AS uid, null AS u_name, null AS email, 1 as sortcol
FROM members a ) AS memberRow
UNION ( Select memberid, null AS companyname, null AS contact_email, uid, u_name, email, 2 as sortcol
FROM members b INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3)
ORDER BY memberid, sortcol, u_id;

Andy - I just rebuilt my testbed in SQL Server 2005. This select worked.

Select memberid, company_name, contact_email, u_id, u_name, email, sortcol
FROM( Select memberid, company_name, contact_email, null AS u_id, null AS u_name, null AS email, 1 as sortcol
FROM members a ) AS memberrow
UNION ( Select memberid, null AS company_name, null AS contact_email, u_id, u_name, email, 2 as sortcol
FROM members b INNER JOIN testusers
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3)
ORDER BY memberid, sortcol, u_id

Jim

Obviously, you will need to translate the column names to match yours.

Jim

Electriciansnet

ASKER

Hi Jim, I just cannot figure out why I keep getting the same error
"Class:16, Msg 421, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable."
So I was thinking it might be a difference in data types. Here is the table structure of users, members respectively.

USE [enetdb1SQL]
GO
/****** Object: Table [dbo].[users] Script Date: 08/10/2007 01:20:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
      [uID] [int] IDENTITY(1,1) NOT NULL,
      [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ZipCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [U_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [D_Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [C_Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Email] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Service] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [comments] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [hit_date] [datetime] NULL,
      [qMemberID_1] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [qMemberID_2] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [qMemberID_3] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

___________________________________________________________________
USE [enetdb1SQL]
GO
/****** Object: Table [dbo].[members] Script Date: 08/10/2007 01:23:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[members](
      [memberID] [int] IDENTITY(1,1) NOT NULL,
      [companyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [streetNumber] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [City] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [State] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_First] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_Last] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_Phone] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_Email] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PassWord] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Contact_URL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [License] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CellNumber] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [NetworkID] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Slogan] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [member_Since] [datetime] NULL,
      [logo] [image] NULL,
CONSTRAINT [PK__members__4EA8A765] PRIMARY KEY CLUSTERED
(
      [memberID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

The DISTINCT is certainly a mystery, since it doesn't show up in the SQL. It may be some kind of bug surrounding TEXT columns.

Do not use TEXT data types unless it is forced on you. It is deprecated in SQL Server2005. All the columns you have defined as Text would be relatively small, based on looking at the names of the columns. Instead, you could use VARCHAR(MAX) or just varchar(256) for the larger ones and 40 or so for things like name and title. I ran into another odd bit of behavior surrounding text columns earlier today in a different question. We solved it, but it took a long time. Had a number in one column and the number converted to a string in a text column, and had a devil of a time getting the compare to work.

Jim

Electriciansnet

ASKER

Alright Jim, I changed the column's data types to various size varchar and after clearing up 1 minor error, viola! But, I need to add these columns to the SELECT so that I can show the member all of their hits:

UNION ( Select memberid, null AS companyname, null AS contact_email, uid, u_name, users.City, zipCode, D_Phone, C_Phone, email, service, comments, hit_date, 2 as sortcol

I added " users.City, zipCode, D_Phone, C_Phone, email, service, comments, hit_date," as you can see but I also need to add them above the UNION according to CF error and I don't know which ones need null.

Plus I'm not too sure where the two time constraints go. We need "datepart(day, member_since) = datepart(day, getdate())" and "users.hit_date >= dateAdd(dd,-30,getDate()". The easiest way is to probably give you your SELECT back so, if its not too much trouble-

Select memberid, companyname, contact_email, uid, u_name, email, sortcol
FROM( Select memberid, companyname, contact_email, null AS uid, null AS u_name, null AS email, 1 as sortcol
FROM members a ) AS memberrow
UNION ( Select memberid, null AS companyname, null AS contact_email, uid, u_name, email, 2 as sortcol
FROM members b INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3)
ORDER BY memberid, sortcol, uid

As you can see- some minor changes have been made to match column names.
WE'RE ALMOST THERE JIM!!! I have to go troubleshoot a network problem for the church but I'll check back as soon as I get back. Thanks again, Andy

Andy - I think this will do it.

Jim

SELECT companyName, memberID, contact_Email, member_since, City,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, qMemberID_1, qMemberID_2, qMemberID_3, sortcol
FROM( SELECT companyname, memberid, contact_email, member_since, null city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, null qMemberID_1, null qMemberID_2, null qMemberID_3, 1 as sortcol
FROM members INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2))
AND users.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT null companyname, memberid, null contact_email, null member_since, city,
ZipCode, U_Name, D_Phone, C_Phone, email, Service, comments,
hit_date, qMemberID_1, qMemberID_2, qMemberID_3, 2 as sortcol
FROM members INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2)
OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-2))
AND users.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

Electriciansnet

ASKER

Yes yes yes, I love it!
I had to change column city to u_city because there was an ambiguous error and SQL did not like it when I prefix like: users.city but I would like to show the values of all columns in users except uid to the members so I started taking the null away (null u_city, null zipCode). Is this okay or does it create inaccuracies in the statement? In MS Query all user columns are null.

Null in the two select statements are placeholders, and cannot be removed. Both selects that contribute to the union have to have the same number of columns. Null just means to return null instead of the real value because we do not want it. The first select has null for all user columns, as it needs to be. The second has null for most of the member columns. If you want to add another user column to the second select, it needs to be matched with null colname in the other select. It also needs to be added to the outer select that gets the results of the union.

Electriciansnet

ASKER

Alright, my head is spinning but, alright. This is what the results look like:

companyName      memberID      contact_Email      member_since      u_City      ZipCode      U_Name      D_Phone      C_Phone      Email      Service      comments      hit_date      qMemberID_1      qMemberID_2      qMemberID_3      sortcol
Insert 7      169      ace_prises@yahoo.com      8/5/2007 2:23:02 AM      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      1

(1 row(s) affected)

I changed this block:

FROM( SELECT companyname, memberid, contact_email, member_since, null city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, null qMemberID_1, null qMemberID_2, null qMemberID_3, 1 as sortcol

To:

FROM( SELECT companyname, memberid, contact_email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, email, null Service, comments,
hit_date, null qMemberID_1, null qMemberID_2, null qMemberID_3, 1 as sortcol

and here are the results I got:

companyName      memberID      contact_Email      member_since      u_City      ZipCode      U_Name      D_Phone      C_Phone      Email      Service      comments      hit_date      qMemberID_1      qMemberID_2      qMemberID_3      sortcol
Insert 7      169      ace_prises@yahoo.com      8/5/2007 2:23:02 AM      N.Y.,Ny      10007      monday to insert7      777-777-7777                  (NULL)      MemberID 169      8/6/2007 8:50:08 PM      (NULL)      (NULL)      (NULL)      1

(1 row(s) affected)

Is this not ok?

Electriciansnet

ASKER

never mind I can see its not. it is returning identical rows again. I'll try to follow what you said above.

That's OK if there is only one user to return to the member, but I think it's going to give you multiples for the member email again if there is more than one user to report. I think that to make this work, one row returns member only, followed by those that return users only + memberID - or you can even skip that if you do not need it.

Disregard part of my last - we do need memberID all around for the sort.

Electriciansnet

ASKER

Well, I'm not real sure whats going on but I did try it with more than 1 result and got repeating rows. I put it back the way it was but I do need to show the members their results from the users table.

I'm trying to follow your instructions above. I get that I can't touch the top select but you said:
"If you want to add another user column to the second select, it needs to be matched with null colname in the other select. It also needs to be added to the outer select that gets the results of the union."

I do add cols to second select?
It needs to be matched with which other col?
which one is the outer select?

Select xxx
FROM (Select YYY)
UNION (SELECT zzz)
ORDER BY ...
Columns in xx, yy and zz need to match in number and datatype.

Post your current select, and tell me what you want added.

Electriciansnet

ASKER

OK I'm sorry. I'm really not trying to get you to do all the work for me but this is over my head atleast for now. Notice the time constraints- I left the -6 in place to return rows but that will go away too. It will only need to return on any 1 day. CFschedule will point to this page daily. The way I have it (without-2) is returning error. I need everything to display from users except uid and the 3 qmemberid cols.
Here it goes:

SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, qMemberID_1, qMemberID_2, qMemberID_3, sortcol
FROM( SELECT companyname, memberid, contact_email, member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, null qMemberID_1, null qMemberID_2, null qMemberID_3, 1 as sortcol
FROM members INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-6)
OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())
OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())
AND users.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT null companyname, memberid, null contact_email, null member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, email, Service, comments,
hit_date, qMemberID_1, qMemberID_2, qMemberID_3, 2 as sortcol
FROM members INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-6)
OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())
OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())
AND users.hit_date >= dateAdd(dd,-30,getDate())
ORDER BY memberid, sortcol, U_Name;

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Electriciansnet

ASKER

got the time constraint error straightened out

Electriciansnet

ASKER

I believe that'll doer. Thank you very much Jim. You have worked very hard; been very patient and made yourself available to me through the whole process.This was a tough one huh? Is there a place on this site where I can give you your kudos or anything? Is there a way I can direct other questions to you? Can I give credit to jim@expertsexchange on electriciansnet.com? I'll accept this as the answer now. Thanks Jim!

My pleasure. The tougher they are, the better it feels when they fall.

Good luck Andy!

Electriciansnet

ASKER

I posted the following with customer service:

I just wanted to say how instrumental EE has been in the development of my new site, www.electriciansnet.com. Experts Exchange is an excellent resource for support and well worth every penny. Kind of like having your very own IT department. You could justifiably charge much more for your services. I am particularly impressed with one of your experts, Jim Brandley, he has painstakingly hung in there with me for almost a week to come up with a solution to a very tough query. You can view this post with keywords "Return only 1 row in email column fro... "

Thank you Jim! Keep up the good work people and I will recommend you to all my friends. Best Regards, Andy Carter
____________________________________________________
Again do you mind if I look up your profile on my next advanced question?

Andy - You're making me blush. I will be glad to help you any time I can in the future. However, there are a lot of people on this site who would have stuck in there as well, and possibly have answered it faster and saved you some time.

Thanks for the kudos.

Jim

Electriciansnet

ASKER

Sorry to bug you Jim but I guess I need to show CompanyName. Here is the CF error:

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.

Null Pointers are another name for undefined values.
Hello #getUsers.CompanyName#! You have now been a member for #DateDiff("d", member_since, Now())# months.

For anything that can be null, change the select to:
COALESCE(CompanyName, '') CompanyName

As an example - that causes it to return an empty string instead of the null.

We may also need to replace the ones like:
null ZipCode with '' ZipCode

Electriciansnet

ASKER

yes maybe but lets just hope the missing contact_Email s don't trow error. Here is the working query.

SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT companyname, memberid, contact_email, member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-6)
OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate()))
OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())))
AND users.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT null companyname, memberid, null contact_email, null member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, email, Service, comments,
hit_date, 2 as sortcol
FROM members INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-6)
OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate()))
OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())))
AND users.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

Electriciansnet

ASKER

In fact come to think of it- it does not matter if those other variables output 100's of times as long as cfmail to="#CompanyName#" is limited to 1 per and the results match because the cfoutput will only show on the valid emails. I think!

I modified two of these so you can see what I was talking about:

SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(companyname, '') companyName, memberid, contact_email, member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-6)
OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate()))
OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())))
AND users.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT '' companyname, memberid, '' contact_email, '' member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, email, Service, comments,
hit_date, 2 as sortcol
FROM members INNER JOIN users
ON memberid = qmemberid_1 OR memberid = qmemberid_2 OR memberid = qmemberid_3
WHERE (qMemberID_1 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())-6)
OR qMemberID_2 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate()))
OR qMemberID_3 IN (Select memberID From Members WHERE datepart(day, member_since) = datepart(day, getdate())))
AND users.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

Electriciansnet

ASKER

I'm not real clear on how you got blank fields instead of null but I took what you had and got this error (which means that blank fields won't get it either) (I read somewhere about creating a fake query (q= query new{};) that will feed an email list:

Attribute validation error for tag CFMAIL.
The value of the attribute TO is invalid. The length of the string, 0 character(s), must be greater than or equal to 1 character(s).

If you insert one space between the single quotes, that should satisfy it. Will that screw up your mail processing?

Electriciansnet

ASKER

Yes because I outputted a ValueList tag just to see how cfmail would read it (for dumping purposes) and I got results like this with fields set to null:
andy@enet.com;;;jim@enet.com;;

When you set the email field to blank I got error (see my last post). When I tried to execute cfmail with ValueList it sent out dozens of emails. Last night I downloaded an IsNull UDF but I cannot seem to get it to work. If I could then we could set the fields back to null.

<cfif NOT IsDefined (getUsers.CompanyName) >
<cfinclude template="../_udfs/isNull.cfm">
</cfif>

Also, this guy says hi's post is "too basic" which makes me wonder what I'm missing but do you think something like this will work:

http://cfsilence.com/blog/client/index.cfm/2006/12/12/Sending-CFMAIL-To-Multiple-Recipients

This is way outside anything I am familiar with. I'm pretty good with SQL, but have never done anything with ColdFusion.

Sorry,

Jim

Electriciansnet

ASKER

This was posted today on previous question that you saw. This was sent in response to posting your query trying to get help with the cf logic:

dgrafx:
ok - whenever doing IN you need to make sure there is a value (not null and not zero length)
that's why in a query such as this I prefer to do
a) where whatever IN (Select whatever From table where ...)
instead of
b) where whatever IN (#aCFvariable#)

method a does not throw an error - just doesn't return a result because it's not in the list
method b throws an error if #aCFvar# is null or zero lenth

but if your column is int (to answer your question) you need to change to (#ValueList(getMembers.MemberID)#)

note: is it possible that you need to redo your db structure and application logic?
a task such as this should be easier than this.
if interested - post the general concept behind having 3 different memberid's in one table that need to match memberid in the other table.
just kinda describe what you're doing
I know what you are doing with sending the email - just not sure about whats up before this point.

Does it help any?

I sort of understand the bit about the in clause. Technically your user table is not in first normal form, which can present problems. Since members may be referenced by many users, and users may reference multiple members, a better approach would be to create a table called user_members which contains only uID and memberID. Then you can build the selects for mail using simple joins with this relation table.

However, I still have no idea how ColdFusion works, so could not advise you on what changes need to be made to integrate the two. If you need help building more selects, I can certainly help there.

Jim

Electriciansnet

ASKER

Yes I don't know why I did'nt think of it before. When you visited site you entered a zip code which queried dbo.memberzip which contains every zip code of a member (sometime members have hundreds of zips).
Anyway memberzip.memberID is FK to members.memberID PK. Here is that query:

SELECT TOP 3 members.*, networkID.networkID, networkMethod
FROM members INNER JOIN
memberZip ON members.memberID = memberZip.memberID
INNER JOIN networkID ON members.networkID = networkID.networkID
WHERE memberzip.zipCode= '#form.myZip#'
ORDER BY dbo.members.member_since

I can go create users_members now and I think that I need to do the same as memberzip which has 3 columns zipID int autonumber , memberID int FK_members.memberid, zipCode varchar(10) PK (why PK ? I don't know Jim!)
only the zipCode col will be replaced with memberID so the memberIDs will stack up if necessary and memberID will become uid int FK_users.uid and zipID will become umID int aoutonumber

is this right? Do I need to post new question?

I will look at this a bit. Beware that questions are locked some period (24 hrs?) after an answer is accepted. Will get back to you as quickly as I can...

1. zipCode was made the primary key so it could appear in the table only once. Instead of adding zipID, I would have placed a UNIQUE constraint on (zipID, memberID) so it could be assigned to a user only once.
2. In the new table, I would think
userID
memberID
PK on (userID, memberID)
FK on both

Then each member can reference multiple users and vice-versa, and
each user-member relationship can only appear once.

Jim

Electriciansnet

ASKER

You're going to have to break that last comment down into more detail Jim.

Here is an excerpt from memberzip so that you can see how that constraint would be impossible:
zipID memberID zipCode
2595      163      33601
2594      163      33602
2593      163      33603
2592      163      33604
2591      163      33605
2590      163      33606
2589      163      33607
2588      163      33608
2587      163      33609
2586      163      33610
2585      163      33611
2584      163      33612

zipID is only there so there will be an autonumber to insert memberID multiple times with each zip code that belongs to that member.
On the ColdFusion side a cfloop loops through a cfgrid which sometimes contains over 200 zipcodes and inserts them. You might enter 10009 on the home page and as many as 3 members has that zip as one of theirs.
I was hoping to do the same with this table. If I omit muID then I wont have an autonumber if the users query returns more than 1 member, right. I know I'm novice and this method may be novice but I don't know a better way. I have already gone to the CF page that contains the user's form (the form you filled out) and created a hidden combobox for the memberIDs that the user returns.

So, you want users_members to have uID PK FK_users_uID, memberID PK FK_members.memberID?
Can you write the selects for this?

CREATE TABLE member_user
(
memberID int NOT NULL,
uID int NOT NULL,
CONSTRAINT PK_MU PRIMARY KEY( memberID, uID),
CONSTRAINT FK_MU1 FOREIGN KEY(memberID) REFERENCES members,
CONSTRAINT FK_MU2 FOREIGN KEY(uID) REFERENCES users
)

Does that make sense?

Electriciansnet

ASKER

consider it done. I'll just delete the member_user I created in SQL studio.

Electriciansnet

ASKER

Msg 1767, Level 16, State 0, Line 1
Foreign key 'FK_MU1' references invalid table 'members'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Electriciansnet

ASKER

I went over to MS Query and it created just fine. Now I just need the select.

I think this will do it:
SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(m.companyname, '') companyName, m.memberid, m.contact_email, m.member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-6
AND u.hit_date >= dateAdd(dd,-30,getDate()) memberRow
UNION ( SELECT '' companyname, memberid, '' contact_email, '' member_since, u.u_city,
u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.email, u.Service, u.comments,
u.hit_date, 2 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-6
AND u.hit_date >= dateAdd(dd,-30,getDate())
ORDER BY memberid, sortcol, U_Name;

Electriciansnet

ASKER

Hey Jim... Your probably out enjoying your Saturday night like I should be but when you do get back I
Got a Q 4 U. I was able to create table to your specs and I got my Insert to work so I went and did a couple of user queries. Here is the data that was inserted into member_user:

memberID      uID
166        43
169        42

How are we going to relate users to members based on these IDs?

This works but it obviously does not have the filters we're looking for. Andy

select users.u_city, users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email, users.Service, users.comments, users.hit_date, members.CompanyName, members.Contact_Email
FROM users INNER JOIN member_user ON users.uID = member_user.uID
INNER JOIN members ON members.memberID = member_user.memberID
WHERE datepart(day, member_since) = datepart(day, getdate()-6)
AND users.hit_date >= dateAdd(dd,-30,getDate())

Electriciansnet

ASKER

Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:15, Msg 156, State 1, Line 18
Incorrect syntax near 'memberRow'.
Incorrect syntax near the keyword 'ORDER'.

I had unbalanced parens.
SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(m.companyname, '') companyName, m.memberid, m.contact_email, m.member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate()-6
AND u.hit_date >= dateAdd(dd,-30,getDate()) memberRow
UNION ( SELECT '' companyname, memberid, '' contact_email, '' member_since, u.u_city,
u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.email, u.Service, u.comments,
u.hit_date, 2 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-6
AND u.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

Electriciansnet

ASKER

Class:15, Msg 156, State 1, Line 10
Incorrect syntax near the keyword 'AND'.

I found missing ) here:

WHERE datepart(day, member_since) = datepart(day, getdate()-6
AND u.hit_date >=
So I fixed that one but I cannot find this error:

Class:15, Msg 102, State 1, Line 10
Incorrect syntax near 'memberRow'.

Looks like I got carried away with the delete key.
SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(m.companyname, '') companyName, m.memberid, m.contact_email, m.member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate()-6)
AND u.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT '' companyname, memberid, '' contact_email, '' member_since, u.u_city,
u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.email, u.Service, u.comments,
u.hit_date, 2 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-6
AND u.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

Electriciansnet

ASKER

I'm sorry Jim...
We can deal with it tomorow if you want but there is another error I can't change:

Class:16, Msg 209, State 1, Line 11
Ambiguous column name 'memberid'. I tried to add members. but no good. and just so you know its member_user not users

Electriciansnet

ASKER

I think I got it I changed it to:

UNION ( SELECT '' companyname, m.memberid,

Electriciansnet

ASKER

Like I said- tomorrow or whenever is convenient for you is good. I got some wierd results though:

companyName      memberID      contact_Email      member_since      u_city      ZipCode      U_Name      D_Phone      C_Phone      Email      Service      comments      hit_date      sortcol
Insert 7      169      ace_prises@yahoo.com      8/5/2007 2:23:02 AM      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      1
      169            1/1/1900 12:00:00 AM      new york      10007      new table test to insert7      777-777-7777      777-777-7777      ace_prises@hotmail.com      residentialEmergency            8/11/2007 10:24:27 PM      2

(2 row(s) affected)

If the user name = 'new table test to insert7' and there were no comments for that user, It's exactly what I would expect. I added some spaces so the data lines up with the headers. Copy the below to TextPad or NotePad or some such.

companyName memberID contact_Email member_since u_city ZipCode U_Name D_Phone C_Phone Email Service comments hit_date sortcol
Insert 7 169 ace_prises@yahoo.com 8/5/2007 2:23:02 AM (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) 1
169 1/1/1900 12:00:00 AM new york 10007 new table test to insert7 777-777-7777 777-777-7777 ace_prises@hotmail.com residentialEmergency 8/11/2007 10:24:27 PM 2

Electriciansnet

ASKER

Hi Jim. A couple of points to your last response-

select * from members
where memberID=169

memberID      companyName      streetNumber      City      State      Contact_First      Contact_Last      Contact_title      Contact_Phone      Contact_Email      PassWord      Contact_URL      License      CellNumber      NetworkID      Slogan      member_Since      logo
169      Insert 7      775 Seventh St.      New York      New York      Stephen      Stevens      Manager      777-877-7878      ace_prises@yahoo.com      stevens      http://www.steven.el.com      er00nl7      8132440620      1      Some text      8/5/2007 2:23:02 AM      System.Byte[]

the results that you sent back only has 1 U_name, 1 hit_date, 1 memberID with 2 member_since dates (one of them being over 100 years old).

when I query:

select * from member_user
where memberID=169

memberID      uID
169        42
should I not get only 1 row returned?

1. Old member_since is because we are sending back a space (something was unhappy with null) for the second select inside the union. You can try that with null again if the CF code has changed.

2. Number of user rows is a function of the content of the new table. If there is only one row in there where memberID = 169, the result is correct. If you add more rows to member_user, you will see more returned.

Electriciansnet

ASKER

OK I'll try it. I'm a little confused by your last response ("Number of user rows is a function of the content of the new table")and I still don't see how the query produced a non existent date from the 1900s. I just don't see how the same query(getUsers) above cfmail will not get the same results. The CF code has not changed but the problem seems to be in our results not are query. Thanks, Andyw

Electriciansnet

ASKER

I just wanted to make sure I didn't mess the select up when I changed the line below to stop error:

UNION ( SELECT '' companyname, m.memberid,

Your change is fine. The bogus date is just how the space is being interpreted. It is undoubtedly the default data when nothing is privided to initialize it. If we could return null instead of the space, it would go away.

Jim

Electriciansnet

ASKER

I just came up with an idea:
Even though I have never done one they don't look too dificult. I think I can do a query of queries in CF under your query to populate cfmail something like this:

<cfquery name="newlist" dbtype="query">
SELECT CompanyName, Contact_Email, member_since, u_name, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments FROM getUsers WHERE contact_email IS NOT NULL AND contact_email != ''
</cfquery>

I would just need you to provide the same new query with nulls. Then I will save the old one and also be able to compare the 2 so I can figure out what you did.
Do you think this might work?

What select is called getUsers?

Electriciansnet

ASKER

the name of your query.
i.e. the query you sent last night will be the 1st query and it will be wraped in a tag (like all queries in CF must be)

<cfquery name="getUsers" datasource="#enet#">
SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(m.companyname, '') companyName, m.memberid, m.contact_email, m.member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members m INNER JOIN member_user mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-8
AND u.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT '' companyname, m.memberid, '' contact_email, '' member_since, u.u_city,
u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.email, u.Service, u.comments,
u.hit_date, 2 as sortcol
FROM members m INNER JOIN member_user mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-8
AND u.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;
</cfquery>

<cfquery name="newlist" dbtype="query">
SELECT CompanyName, Contact_Email, member_since, u_name, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments FROM getUsers WHERE contact_email IS NOT NULL AND contact_email != ''
</cfquery>

only I will need to do it with nulls

Got it. So thepurpose of the second query is to separate out the members from the users?

Electriciansnet

ASKER

No- I'm hoping to weed out the null values. If you remember, we have not fixed the problem yet. We made the query better and typical by adding member_user but cfmail still will not tollerate nulls or empty strings.
So (I Hope) by doing this we will only end up with member info x 1 and all user info w/o blanks in between.
I don't know if the idea will work but I do know that the results were better when we had the null values in it. The way the query is set now we are only returning 1 member with no user data.

Electriciansnet

ASKER

The top row is a cfdump on the query of queries. The bottom is a dump on just the query. Like you say- you may need text editor.

query - Top 1 of 1 Rows
      COMMENTS      COMPANYNAME      CONTACT_EMAIL      C_PHONE      D_PHONE      EMAIL      HIT_DATE      MEMBERID      MEMBER_SINCE      SERVICE      U_CITY      U_NAME      ZIPCODE
1       [empty string]       Insert 4       sales@acesolutions4u.com       [empty string]       [empty string]       [empty string]       [empty string]       166       2007-08-04 01:57:18.497       [empty string]       [empty string]       [empty string]       [empty string]
query - Top 2 of 2 Rows
      COMMENTS      COMPANYNAME      CONTACT_EMAIL      C_PHONE      D_PHONE      EMAIL      HIT_DATE      MEMBERID      MEMBER_SINCE      SERVICE      SORTCOL      U_CITY      U_NAME      ZIPCODE
1       [empty string]       Insert 4       sales@acesolutions4u.com       [empty string]       [empty string]       [empty string]       [empty string]       166       2007-08-04 01:57:18.497       [empty string]       1       [empty string]       [empty string]       [empty string]
2       [empty string]       [empty string]       [empty string]       [empty string]       444-444-4444       ace_prises@yahoo.com       2007-08-11 22:40:08.89       166       1900-01-01 00:00:00.0       residentialEmergency       2       hickory, NC       q to insert 4       28601

This one returns nulls for member columns from the select for users:
SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(m.companyname, '') companyName, m.memberid, m.contact_email, m.member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate()-6)
AND u.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT null companyname, memberid, null contact_email, null member_since, u.u_city,
u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.email, u.Service, u.comments,
u.hit_date, 2 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-6
AND u.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

Electriciansnet

ASKER

After some minor table name corrections and prefixing members with m I got it to work. Thank You. The only question I have is why does it display in 2 lines when there is only 1 possible result to be had?

companyName      memberID      contact_Email      member_since      u_city      ZipCode      U_Name      D_Phone      C_Phone      Email      Service      comments      hit_date      sortcol
Insert 7      169      ace_prises@yahoo.com      8/5/2007 2:23:02 AM      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      1
(NULL)      169      (NULL)      (NULL)      new york      10007      new table test to insert7      777-777-7777      777-777-7777      ace_prises@hotmail.com      residentialEmergency            8/11/2007 10:24:27 PM      2

(2 row(s) affected)

Now that we are returning null instead of the space, try:
SELECT CompanyName, Contact_Email, member_since, u_name, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments FROM getUsers WHERE contact_email IS NOT NULL

Electriciansnet

ASKER

Okay I will

FYI- I'm stacking up fake user queries and the new select by itself is looking pretty good in MS Query although I am going to have to do something in cfmail like query of queries.

Electriciansnet

ASKER

cfdump still showing [empty string] rather than null. Did you mean to leave-

FROM( SELECT COALESCE(m.companyname, '') -the single quotes behind companyName?

That's only for the member side of the select. I thought you had trouble earlier when it returned null if there was no company name for that member. If null is OK now, just replace
COALESCE(m.companyname, '')
with mcompanyname

Electriciansnet

ASKER

OK Jim here's the problem:
There are too man null or empty values on the users side. The other day before we changed the tables around your select was returning company a: user1, user2 company b:user1, user2, user3. What is being returned now is: company a: user1, null company b:null, user 2, null.

This is a very broad example but things like U_Name, hit_date etc. should never be null because that is exactly what will be mailed assuming I can ever get cfmail to work. When I tried it earlier the message said " Hello Insert 4! You have now been a member for -37987 (serious bug in that argument huh?) months.
2 Consumers Have Submitted A Request To Your Company: "Then an empty HTML table (no results). If possible, I don't think we can affor any nulls on the users side. Thanks, Andy

I think you are saying we are getting members out, but no row for the users. Is that correct?
What do you get if you run this:

Select memberID from member_users where uID IS NULL

Electriciansnet

ASKER

The most updated query returns with the time argument for hit_date set to today:

_companyName      memberID      contact_Email      member_since      u_city      ZipCode      U_Name      D_Phone      C_Phone      Email      Service      comments      hit_date      sortcol
Insert 8      170      ace_prises@hotmail.com      8/12/2007 9:12:31 AM      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      1
(NULL)      170      (NULL)      (NULL)      wich, kansas      88888      query to insert8      888-111-1111                              8/12/2007 11:51:04 AM      2
insert9      171      ace_prises@hotmail.com      8/12/2007 1:16:14 PM      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      (NULL)      1
(NULL)      171      (NULL)      (NULL)      wi, ks      99999      q to insert 8      888-888-8888      888-888-8888      andy@yahoo.com      residentialEmergency      test for multiple queries      8/12/2007 6:46:44 PM      2
(NULL)      171      (NULL)      (NULL)      wich,ks      88888      query to inserts 8,9      888-999-8989      888-999-9898      ace_prises@hotmail.com      commercialRepair      test insert to 2 members      8/12/2007 6:52:37 PM      2

(5 row(s) affected)

_________________________________________________________
select * from users
WHERE datepart(day, hit_date) = datepart(day, getdate())

uID      u_city      ZipCode      U_Name      D_Phone      C_Phone      Email      Service      comments      hit_date
44      wich, kansas      88888      query to insert8      888-111-1111                              8/12/2007 11:51:04 AM
45      wi, ks      99999      q to insert 8      888-888-8888      888-888-8888      andy@yahoo.com      residentialEmergency      test for multiple queries      8/12/2007 6:46:44 PM
46      wich,ks      88888      query to inserts 8,9      888-999-8989      888-999-9898      ace_prises@hotmail.com      commercialRepair      test insert to 2 members      8/12/2007 6:52:37 PM
47      hickory, nc      55555      query to insert 4      444-444-4444      444-444-4444      ace_prises@yahoo.com      otheResidentialService      some test      8/12/2007 7:02:35 PM
48      hickory, nc      55555      query to insert 4      444-444-4444      444-444-4444      ace_prises@yahoo.com      otheResidentialService      some test      8/12/2007 7:02:36 PM

(5 row(s) affected)

See what I mean Jim? All of this should be returned with limited member info to mail to members. Some of the user rows above were done to the same member twice today to see what the mail would display. Now I wish I had waited because its making things to complicated to analyze.

Electriciansnet

ASKER

Select memberID from member_users where uID IS NULL

I get no rows returned with select by itself in MS Query

Do the users with uID = 47 and 47 show up in the member_user table?

Electriciansnet

ASKER

yes- 1 time from test query today

We are doing an inner join, so all should be there, except:
WHERE datepart(day, member_since) != datepart(day, getdate())-6
AND u.hit_date < dateAdd(dd,-30,getDate())

So, use that in a where clause to see what you get.

Electriciansnet

ASKER

OK but I just found a major screw up on my part. I went to SQL studio and discovered I have somehow inserted companyName "Insert 10" 4 times under member IDs 172-175. That has got to be screwing up our results big time!
can I do Delete from memberzip, members, member_user where memberID = 172 then 173 etc.?

Also, before I do that I have taken away the -6 to only get the members I have inserted today. will that make a difference?

That delete should be fine. I have been wondering about:
datepart(day, member_since) = datepart(day, getdate()-6)

That will only get members where member date is 6 days old. Is that what you wanted?

Electriciansnet

ASKER

It was... you may not have noticed that that number (-6)increased as the says went by to yield results until this morning when I did a bunch of new member inserts. Then it needed to equal today. This is it's permanent state because cfschedule hits 30daysmail.cfm (where your query resides) once a day and the only members that should be sent mail (as a result of the query i.e. <cfif getUsers.RecordCount = 0> <cfabort> <cfelse> <cfmail to="getUsers.contact_Email>"are the ones that have been enrolled for 30 days, every 30 days. So, in short, if cfschedule hits the page on day 1 the query should only produce results for the members that enrolled on day 1 and send the member a copy of every user that hit on 1 of their zip codes in the last 30 days.
I will change the WHERE statement now. I was busy deleting the duplicate rows.

Electriciansnet

ASKER

the server's clock just flipped over to the next day but no matter what combo I use i.e., -1, +1, <=, !+, = nothing is returning results

We were getting the correct results. I do not understand how we went from a good list to none.
Any thoughts?

Electriciansnet

ASKER

that was before we changed the DB structure (added member_user). The results have not been right since we changed. I'm checking something now and I'll let you know in a few

Andy - I'm going to post my own question to see if I can help you get closure on this one. I think my lack of Cold Fusion knowledge is a big stumbling block here. So, I will post in the CF zones.

Jim

Electriciansnet

ASKER

OK the problem IS in the date arguments. I was getting no results so I commented out the u.hit_date, still nothing so, I commented out the member_since arguments and I got 7 rows returned which are hard to read but look much more accurate as far as the nulls go.

Electriciansnet

ASKER

I was thinking the same thing but I wanted to make sure the query was right 1st. Take a look at these results. Can you see how the rows of members and users never line up. If the 1st member got 2 results should'nt the 1st user row be with the member then 1 additional row with the second user on it and so on? That, I believe is the way you had it before.

      [empty string]       Insert 4       sales@acesolutions4u.com       [empty string]       [empty string]       [empty string]       [empty string]       166       2007-08-04 01:57:18.497       [empty string]       1       [empty string]       [empty string]       [empty string]
2       [empty string]       [empty string]       [empty string]       [empty string]       444-444-4444       ace_prises@yahoo.com       2007-08-11 22:40:08.89       166       [empty string]       residentialEmergency       2       hickory, NC       q to insert 4       28601
3       [empty string]       Insert 5       postmaster@electriciansnet.com       [empty string]       [empty string]       [empty string]       [empty string]       167       2007-08-05 00:14:36.28       [empty string]       1       [empty string]       [empty string]       [empty string]
4       some test       [empty string]       [empty string]       444-444-4444       444-444-4444       ace_prises@yahoo.com       2007-08-12 19:02:35.95       167       [empty string]       otheResidentialService       2       hickory, nc       query to insert 4       55555
5       some test       [empty string]       [empty string]       444-444-4444       444-444-4444       ace_prises@yahoo.com       2007-08-12 19:02:36.06       167       [empty string]       otheResidentialService       2       hickory, nc       query to insert 4       55555
6       [empty string]       Insert 7       ace_prises@yahoo.com       [empty string]       [empty string]       [empty string]       [empty string]       169       2007-08-05 02:23:02.67       [empty string]       1       [empty string]       [empty string]       [empty string]
7       [empty string]       [empty string]       [empty string]       777-777-7777       777-777-7777       ace_prises@hotmail.com       2007-08-11 22:24:27.95       169       [empty string]       residentialEmergency       2       new york       new table test to insert7       10007
8       [empty string]       Insert 8       ace_prises@hotmail.com       [empty string]       [empty string]       [empty string]       [empty string]       170       2007-08-12 09:12:31.153       [empty string]       1       [empty string]       [empty string]       [empty string]
9       [empty string]       [empty string]       [empty string]       [empty string]       888-111-1111       [empty string]       2007-08-12 11:51:04.59       170       [empty string]       [empty string]       2       wich, kansas       query to insert8       88888
10       [empty string]       insert9       ace@acesolutions4u.com       [empty string]       [empty string]       [empty string]       [empty string]       171       2007-08-12 13:16:14.123       [empty string]       1       [empty string]       [empty string]       [empty string]
11       test for multiple queries       [empty string]       [empty string]       888-888-8888       888-888-8888       andy@yahoo.com       2007-08-12 18:46:44.543       171       [empty string]       residentialEmergency       2       wi, ks       q to insert 8       99999
12       test insert to 2 members       [empty string]       [empty string]       888-999-9898       888-999-8989       ace_prises@hotmail.com       2007-08-12 18:52:37.7       171       [empty string]       commercialRepair       2       wich,ks       query to inserts 8,9       88888
13       [empty string]       Powers Electric       sales@acesolutions4u.com       [empty string]       [empty string]       [empty string]       [empty string]       176       2007-08-12 14:14:55.67       [empty string]       1       [empty string]       [empty string]       [empty string]
14       Shagadellic baby!       [empty string]       [empty string]       800-555-1212       800-555-5555       andy@electriciansnet.com       2007-08-12 22:00:36.373       176       [empty string]       otherCommercial

CF help should be on the way. Now for the dates. Can you do this:
Select memberID, member_since from members;

Select uID, hit_date from users

And post the results.

It looks like they are in that order:
MEM1 [empty string] Insert 4 sales@acesolutions4u.com
USR2 [empty string] [empty string] [empty string] [empt
MEM3 [empty string] Insert 5 postmaster@electriciansnet.com
USR4 some test [empty string] [empty string] 444-444-44
USR5 some test [empty string] [empty string] 444-444-44
MEM6 [empty string] Insert 7 ace_prises@yahoo.com [empt
USR7 [empty string] [empty string] [empty string] 777-7
MEM8 [empty string] Insert 8 ace_prises@hotmail.com [em
USR9 [empty string] [empty string] [empty string] [empt
MEM10 [empty string] insert9 ace@acesolutions4u.com [em
USR11 test for multiple queries [empty string] [empty string]
USR12 test insert to 2 members [empty string] [empty string]
MEM13 [empty string] Powers Electric sales@acesolutions4u.com
USR14 Shagadellic baby! [empty string] [empty string] 8

Electriciansnet

ASKER

Yet another road block! Even though I have 4 members enrolled on day 12 and I change the select to read "WHERE datepart(day, member_since) = datepart(day, 12)" I still got no results so I was thinking that something was wrong with our member_user table. I went and added another member with a member_since date of day, 13 and changed accordingly. Still no results.

That's when I realized that that member has not been queried by a user therefore has no results in member_user. Well that wont work. That means that the member will not get his 30 day status report unless he has been queried. It also means that we have alot more riding on that table in the query than intended.

I stopped getting results after I deleted the duplicate members from the tables which tells me that what I was looking at all day was those duplicates. Idon't know Jim

Do you expect to send an email to a member every 30 days, even if there have been no users who selected that member in the 30-day span?

Electriciansnet

ASKER

yes because the user will need to be contacted regardless. Eventually I will charge for this service once a good search engine position is obtained and this letter will become a statement.

Sidebar:
I pay $250 a month for the exact same service but a lesser site and it sends me 10 - 15 service calls a week averaging about a 300% return. So yeah I want to let them know they're not forgotten.

Got it. The current SQL will not send them.
My current copy of the select looks like this:
SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(m.companyname, '') companyName, m.memberid, m.contact_email, m.member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate()-6)
AND u.hit_date >= dateAdd(dd,-30,getDate())) memberRow
UNION ( SELECT null companyname, memberid, null contact_email, null member_since, u.u_city,
u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.email, u.Service, u.comments,
u.hit_date, 2 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-6
AND u.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

I will modify that one. If you have one that's newer, please post it so I'm working the right one.

Here's that one modified to return all members in the date range, regardless of whether they have been selected by users.

SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(m.companyname, '') companyName, m.memberid, m.contact_email, m.member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members m LEFT OUTER JOIN member_users mu
ON m.memberID = mu.memberID AND AND u.hit_date >= dateAdd(dd,-30,getDate())
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate()-6)) memberRow
UNION ( SELECT null companyname, memberid, null contact_email, null member_since, u.u_city,
u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.email, u.Service, u.comments,
u.hit_date, 2 as sortcol
FROM members m INNER JOIN member_users mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-6
AND u.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

Electriciansnet

ASKER

Thanks Jim! I'll give er' a shot when I return this evening and I'll let ya know

Electriciansnet

ASKER

Hi Jim. There are some errors. Please use this one to fix.
Did you mean to do "u.hit_date >=" ? Because we want every user for member for past month.
Is there a way to return no null values from the user side?

Here is the error and the query with minor corrections:

Error on Server:TERMINAL-SERVER\ENET1, Source:.Net SqlClient Data Provider
Class:16, Msg 4104, State 1, Line 1
The multi-part identifier "u.hit_date" could not be bound.

SELECT companyName, memberID, contact_Email, member_since, u_city,
ZipCode, U_Name, D_Phone, C_Phone, Email, Service, comments,
hit_date, sortcol
FROM( SELECT COALESCE(m.companyname, '') companyName, m.memberid, m.contact_email, m.member_since, null u_city,
null ZipCode, null U_Name, null D_Phone, null C_Phone, null email, null Service, null comments,
null hit_date, 1 as sortcol
FROM members m LEFT OUTER JOIN member_user mu
ON m.memberID = mu.memberID AND u.hit_date >= dateAdd(dd,-30,getDate())
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate()-6)) memberRow
UNION ( SELECT null companyname, m.memberid, null contact_email, null member_since, u.u_city,
u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.email, u.Service, u.comments,
u.hit_date, 2 as sortcol
FROM members m INNER JOIN member_user mu ON m.memberID = mu.memberID
INNER JOIN users u ON mu.uID = u.uID
WHERE datepart(day, member_since) = datepart(day, getdate())-6
AND u.hit_date >= dateAdd(dd,-30,getDate()))
ORDER BY memberid, sortcol, U_Name;

Did you mean to do "u.hit_date >=" ? Because we want every user for member for past month.

I do not think I have changed the date portion of the where clauses. They should be as you had them to start with, except for a bit of shuffeling to acommodate the new table and the new LEFT OUTER JOIN.

Is there a way to return no null values from the user side?
Do you mean for user columns, or the member columns as well? Also, what do you want returned in place of nulls?

Rather than doing a gigantic query like you are trying to do it would be better if you simplified it like I has suggested earlier in this thread. Based on what I have read your code should be something like this:

<cfquery name="GetMembers" datasource="#enet#">
SELECT M.memberID, M.companyName, M.contact_Email, M.member_since
       FROM Members M JOIN member_users MU on M.MemberID = MU.MemberID
                              JOIN users U on U.uid = MU.uid
            Where U.hit_date >= dateAdd(dd,-30,getDate())
and M.member_since >= dateAdd(dd,-2,getDate())
GROUP BY M.memberID, M.companyName, M.contact_Email, M.member_since
</cfquery>

<cfloop query="GetMembers">
<cfquery name="getUsers" datasource="#enet#">
Select u.City ,u.ZipCode, u.U_Name, u.D_Phone, u.C_Phone, u.Email,
            u.Service , u.comments , u.hit_date
            from users u join member_users mu on mu.uid = u.uid
            WHERE mu.memberid = #GetMembers.MemberID#
                  AND U.hit_date >= dateAdd(dd,-30,getDate())
</cfquery>
<cfmail to="#GetMembers.EmailAddress#" from="member_services@electriciansnet.com" subject="30 Day Status Report" type="HTML">
Hello #GetMembers.CompanyName#! You have now been a member for #DateDiff("m", GetMembers.member_since, Now())# months.<br />
#getUsers.RecordCount# Consumers Have Submitted A Request To Your Company This Month:<br>
      <cfif getUsers.RecordCount neq 0>
      <table>
            <tr>
                  <td>Name</td>
                  <td>City</td>
                  <td>ZipCode</td>
                  <td>Phone</td>
                  <td>Email</td>
                  <td>Service</td>
                  <td>comments</td>
                  <td>hit date</td>
            </tr>
<cfloop query="getUsers">
<tr>
                  <td>#getUsers.U_Name#</td>
                  <td>#getUsers.City#</td>
                  <td>#getUsers.ZipCode#</td>
                  <td>#getUsers.D_Phone#</td>
                  <td>#getUsers.Email#</td>
                  <td>#getUsers.Service#</td>
                  <td>#getUsers.comments#</td>
                  <td>#getUsers.hit_date#</td>
            </tr>
</cfloop>
       </table>
</cfmail>
       </cfif>
</cfloop>

to clarify.... do you want to send an email to just to people who have had users hit them within the last 30 days or do you want to send an email to all the members every 30 days wether anyone has hit them or not?

Last night, Andy said all. That's when I switched from an inner join to a left outer.

I do not know what time zone you are in - Andy is typically around from early evening till midnight or so CDT - UTC -6.

Electriciansnet

ASKER

Hey Jim, I think I might just have it and it's simplicity will kill you but I have some adjustments to make to cfmail first. I was wondering if you could look at this:

You have now been a member for #DateDiff("d", member_since, Now())# months.<br >

This is returning astronomical numbers!

Will You have now been a member for #DateDiff("m", member_since, Now())# months.<br>
fix the problem? Don't I need Datepart? What is the most accurate? This is not my creation.

DateDiff("m", member_since, Now()) will return the number of months elapsed between member_since and now (which can return zero). DatePart just picks out chunks of it, which could present a problem; i.e. DatePart - month from 12/31/2007 is 12, while datepart - month 1/01/2007 is one. So instead of reporting membership for 2 days, it would be -11 months. Integral months in this is probably OK, but that's up to you.

For comparisons, I think something like:M.member_since >= dateAdd(dd,-2,getDate())
is much safer.

Did you get a chance to look at the CF script (I think that's what it would be called) Mr Bennett posted?

Andy & SBennett - I have to be off line for the rest of the evening. I'll be back tomorrow morning.

Jim

Electriciansnet

ASKER

Hi Jim. I'm in Eastern Standard Time but I think the problem was: You have been member for (datediff, DAY) months. When it should be: You have been member for (datediff, MONTH) months.
But guess what- I got it!!! I was reading something the other day about cfmail having the ability to dynamically sort query results and I guess I under estimated the robustness of CF. I had 4 test members in dbo.members with contact_email addresses that belong to me. I aslo had 5 test user queries that belonged to those members. I executed the queries below and it worked perfectly!

<cfquery datasource="#ENET#" name="getMembers">
select members.CompanyName, members.Contact_Email, members.memberID, members.member_since
FROM members
WHERE datepart(day, member_since) = datepart(day, getdate())
</cfquery>

<cfmail ...
to="#Contact_Email#"
query="getMembers">
#Companyname# [#memberID#]<br>
Here is your stuff!<br>
<cfquery datasource="#ENET#" name="getUsers">
select users.u_city, users.ZipCode, users.U_Name, users.D_Phone, users.C_Phone, users.Email, users.Service, users.comments, users.hit_date
FROM users
INNER JOIN member_user ON users.uID = member_user.uID
WHERE member_user.memberID = #getMembers.memberID#
and users.hit_date >= dateAdd(dd,-30,getDate())
</cfquery>
#getUsers.recordcount# hits<br>
<cfloop query="getUsers">
<td>#getUsers.U_Name#</td>
<td>#getUsers.U_City#</td>
<td>#getUsers.ZipCode#</td>

<td>#getUsers.D_Phone#</td>
<td>#getUsers.C_Phone#</td>
<td>#getUsers.Email#</td>
<td>#getUsers.Service#</td>
<td>#getUsers.comments#</td>
<td>#getUsers.hit_date#</td>
</cfloop>
</cfmail>

Never the less you have worked very hard on this problem and I really do appreciate it Jim!

I have some coldFusion code in place that does not allow an enrolling member to make it past a certain page if their CompanyName already exists in the members table but I found out yesterday that if there are errors not related to the INSERT and the member refreshes the page for any reason, the data is inserted multiple times. Is there a smple SQL solutions for this?
Thanks again! Andy.

Electriciansnet

ASKER

I'm sorry SBennett: I was following your post and did not even realize that someone besides Jim was assisting. As you can see above though, I think I have the problem clobbered.
One thing that I did find out is that cfmail has dynamic sorting so you can't use results from 2 different queries in 1 cfmail because it throws an error. However, I did manage to do it once and the results were very not good. Yeah, every member got every member's results and that got a seperate email for every result. But, the way that dgrafx layed it out above seems to work perfectly. His way the second query is within the body of the cfmail and the #results# are wraped inside a cfloop

Electriciansnet

ASKER

It just occurred to me- is this the very same legendary SBennett from the Macromedia support forum?

No I don't post on the Adobe support forums much, and I believe my nickname on there is SBennett304 because SBennett was already taken. I'm afarid I'm only legendary in my own mind =).

BTW you will probably want to do your first query like this:

<cfquery datasource="#ENET#" name="getMembers">
select members.CompanyName, members.Contact_Email, members.memberID, members.member_since
FROM members
WHERE datepart(day, member_since) <cfif (datepart("d",now()) eq daysinmonth(now())) and datepart("d",now()) lt 31> in (<cfloop from="#datepart('d',now())#" to="31" index="i">#i#<cfif i lt 31>,</cfif></cfloop>)<cfelse> = #datepart("d",now())#</cfif>
</cfquery>

That will make it so that on the last day of each month it on months where there are less than 31 days. it will get all the members whose dates are between the number of days the month and 31. for example on february 29th it will get members who registered on the 29th,30th, and 31st. that way no one get skipped on months that are shorter than the month they registered.

Electriciansnet

ASKER

Awesome SBennett! And very interesting but I do have 2 questions:

1)Is there an easy way to test this w/o running it through cfmail? (<cfdump var="#i#">?)

2) I was wondering how I could prevent members from receiving 30daysmail.cfm on the day of enrollment aside from having cfschedule set to run at midnight (which may be enough) because I think they meet the criteria of our WHERE clause.

2.5)I see this a lot behind comments in the forums: "=)" what does it mean?

Andy - Even I can answer 2.5: That's like this: :-)

Jim

Electriciansnet

ASKER

I don't get it Jim ":: :-) ". Did you see my Q to you about an SQL solution as an extra precaution to preven a company from enrolling >1x? SQL studio will not allow me to set constraint on varchar without simple expression. However, it would let me set it to is unique if I did expression like 1+0=1 but I worry about screwing with my DB and I could not imagine why something like that would make a difference.

Sorry - guess I missed that one. You should be able to add a unique constraint to the table, or place a unique index on that column; Syntax for constraint is:
CONSTRAINT uq_members UNIQUE(companyname)

index format is:
CREATE UNIQUE INDEX companyname_idx ON members(companyname)

Electriciansnet

ASKER

It seemed to work great when executing in MS Query. Thanks Jim

My pleasure.

1) if you are talking about testing the query results you can just jump out the first query result and then cfabort like :

<cfquery datasource="#ENET#" name="getMembers">
select members.CompanyName, members.Contact_Email, members.memberID, members.member_since
FROM members
WHERE datepart(day, member_since) <cfif (datepart("d",now()) eq daysinmonth(now())) and datepart("d",now()) lt 31> in (<cfloop from="#datepart('d',now())#" to="31" index="i">#i#<cfif i lt 31>,</cfif></cfloop>)<cfelse> = #datepart("d",now())#</cfif>
</cfquery>
<cfdump car="#getMembers#"><cfabort>

2) you would add the creteria to the where clause:
<cfquery datasource="#ENET#" name="getMembers">
select members.CompanyName, members.Contact_Email, members.memberID, members.member_since
FROM members
WHERE member_since < #createodbcdate(now())#
AND datepart(day, member_since) <cfif (datepart("d",now()) eq daysinmonth(now())) and datepart("d",now()) lt 31> in (<cfloop from="#datepart('d',now())#" to="31" index="i">#i#<cfif i lt 31>,</cfif></cfloop>)<cfelse> = #datepart("d",now())#</cfif>
</cfquery>

2.5) "=)" is ASCII art for a smilley face people use it after they say something they think is funny or makes them smile. To avoid future confusion here are some others as well for your reference

=P face with tounge sticking out
;) winking
=D big smile/laughing
=( sad face
='( crying

Electriciansnet

ASKER

Thanks for the smiley cheat sheet SBennett, I learn something new everyday. Actually I was talking about testing the query with the <cfif> but what does car represent? <cfdump car="#getMembers#"><cfabort>

Electriciansnet

ASKER

Hey Jim, this is a 'whenever you get around to it' question: A couple of weeks ago I setup mail in SQL studio. I run Merak meil server on the same machine as SQL and CF. I always love to try new stuff. Anyway, I was wondering what kinda stuff I could do with it. Can you give me a brief example of it's benefits so that I have something to Google or post a new question with? Thanks, Andy

I have no clue - I mastered sending emails from C# via SMTP, and never had a requirement to attempt it from a DB server. The bulk of my customers would not consider that a desirable feature.

If I were you, I would Google first - you are probably not the first to ask. If you do not find what you want, post a new question in the appropriate zones here. I am astounded by the depth and breadth of knowledge of many of the people who answer questions at EE. There are walking encyclopedias here on so many topics. I started answering questions here in May. I have learned so much from other experts since then, I am a better C# programmer, algorithm designer and SQL author than I was. And that is good for my company too.

Jim

"car" represens a typo =)
it should have been:
<cfdump var="#getMembers#"><cfabort>

What exactly do you want about the query with a cfif statement?

you can test to see if any records were returned like:

<cfif getMembers.recordcount eq 0>
There were no records returned
</cfif>

Andy - I have not heard from you tonight. Could you please let me know when this one is complete? Then I can close the question I posted in Cold Fusion and give SBennett points he so richly deserves for all his help here.

Thanks,

Jim

Electriciansnet

ASKER

OH I'm sorry! I had no idea that was going on. That problem is solved. My whole site is crashed on IE now and not recognizing any variables so I'm dealing with that. Yes you can close that one. Thanks to both of you. You may want to let SBennett know that I have a new question posted that he can find under my profile. Thanks, Andy

Sorry your site crashed, but I'm glad this one is finally sorted out.

Good luck Andy!

Jim