sql help

[rh71]

Table "BLAH":

Item - UserMain - UserAlt - Sent
whistle - john@test.com - barry@test.com - no
keys - larry@test.com - ted@test.com - no
clock - ted@test.com - cory@test.com - no
phone - john@test.com - zack@test.com - no
radio - john@test.com - cory@test.com - no
candy - larry@test.com - john@test.com - no

I'm using ColdFusion and have to email each UserMain & each UserAlt (in separate emails) telling them they are responsible for the items... the easier but less practical way is to just go through each row but someone like john@test.com would get 4 emails (1 for each of his items). It is a huge table that changes and that's not going to work for some users.

I would like to send only 1 email to the distinct UserMains or UserAlts listing all their items instead. Complicating this is that the UserAlt column has to be considered, and then update the last column as sent.

Sample emails:
TO: john@test.com
You are responsible for the following:
whistle
phone
radio
candy

TO: larry@test.com
You are responsible for the following:
keys
candy

Is it possible or would I have to create a view and work off that, etc.? The proper SQL just isn't coming to me.

 

[theknight571]

I'm not familiar with ColdFusion so I may be way off with this solution. If so... pretend I wasn't here. 🙂

You could use PHP (or similar) instead of trying to get it with only SQL.

Excuse my "pseudo PHP code"...

$results = mysql_query("SELECT * FROM BLAH;",$DBConn);
while($data = mysql_fetch_array($results))
   {
      $itemlist[$data['UserMain']] .= $data['Item']."\n";
      $itemlist[$data['UserAlt']] .= $data['Item']."\n";
      $update = mysql_query("UPDATE BLAH SET Sent = 'YES' WHERE Item = $data['Item']",$DBConn);
   }
foreach($itemlist as $email -> $items)
   {
      $message = "You are responsible for the following:\n".$items;
      mail($email,"Items you're responsible for",$message);
   }

I haven't tested the code, but I think it at least gives the general idea of what I'm trying to say.. lol

Essentially you're going through each line of the table and creating a list:
- itemlist(john@test.com) = "whistle phone radio candy"
- itemlist(larry@test.com) = "keys candy"

Then using the array's index (email addr) and the array's value (item list) to make and send the emails.

 

[lozina]

Yeah I would definitely try doing this within the code rather than sql.

Even if you do come up with a query to get what you are looking for its going to be complex and a nightmare to debug or modify later.

It wont be a noticeable performance hit doing it in code - the bottleneck here is the emailing so there is no reason to kill yourself over getting the data at break-neck speed.

IF you really insisted on using sql than instead of a query or view the best would probably be a stored procedure to do this as you can at least write it to be more readable than a single complex sql query.

 

[rh71]

I'm not familiar with ColdFusion so I may be way off with this solution. If so... pretend I wasn't here. 🙂

You could use PHP (or similar) instead of trying to get it with only SQL.

Excuse my "pseudo PHP code"...

$results = mysql_query("SELECT * FROM BLAH;",$DBConn);
while($data = mysql_fetch_array($results))
   {
      $itemlist[$data['UserMain']] .= $data['Item']."\n";
      $itemlist[$data['UserAlt']] .= $data['Item']."\n";
      $update = mysql_query("UPDATE BLAH SET Sent = 'YES' WHERE Item = $data['Item']",$DBConn);
   }
foreach($itemlist as $email -> $items)
   {
      $message = "You are responsible for the following:\n".$items;
      mail($email,"Items you're responsible for",$message);
   }

I haven't tested the code, but I think it at least gives the general idea of what I'm trying to say.. lol

Essentially you're going through each line of the table and creating a list:
- itemlist(john@test.com) = "whistle phone radio candy"
- itemlist(larry@test.com) = "keys candy"

Then using the array's index (email addr) and the array's value (item list) to make and send the emails.

First, thanks for the responses...

When looping each of the resulting rows of the main select statement, I go by user, it will get all their items for the array... BUT then the loop will encounter that same user again down the line and re-get all the items and form another entry for them.

Since the usermain and useralts both exist and are different for each row, I can't even mark the row (with that user present) to not be considered next time.

What if I created a view to show all users and their items? I have not been able to form the appropriate SQL for it though. 🙁

 

[theknight571]

hmm, when looping each of the resulting rows of the main select statement, I go by user, it will get all their items for the array... BUT then the loop will encounter that same user again down the line and re-get all the items and form another entry for them.

In my example (PHP specific) I'm using the user as the array's index, so when it encounters user "X" it adds the item to that array(x)'s value.

So the first time it encounters user "X", array(X) is set to item 1 : array(x) = item1

The next time it encounters user "X", array(X) is set to array(X) + item 2 : array(X) = array(X) + item2.

$itemlist[$data['UserMain']] .= $data['Item']."\n";

This is just concatenating the item and a line feed to the array's value.

Edit... since you edited while I was responding. 🙂

I think you're focusing on the UserMain and UserAlt for your query... Just query for all the items... it's the item you don't want to consider again... not the users.

Or... if I'm misunderstanding... let me know. 🙂 This is always a possibility. lol

 

[theknight571]

What database are you running on? MySQL, MSSQL, Oracle, etc?

Might be a obvious answer, but since I'm not familar with Cold Fusion I'm gonna ask anyway.

 

[rh71]

MS SQL... I'm having a hard time equating your PHP to the equivalent CF language. 😉 Still working on it.

I've got the arrayappend() part and trying to figure out the rest... this is a 1-dimensional array right?

<CFQUERY NAME="getRows" DATASOURCE="#dbname#" Username="#dbuser#" Password="#dbpass#" blockfactor="1000">
SELECT item, userMain, userAlt, sent
FROM blah
</CFQUERY>

<!--- loop through query all from table --->
<CFLOOP QUERY="getRows">

	<CFSET testarray1 = arrayNew(1)>

	<CFSET arrayappend(testarray1, "#item#")>

        <!--- output array contents --->
	<cfloop array=#testarray1# index="show">
	    <cfoutput>#show#</cfoutput><br>

        <!--- emailer here --->

	</cfloop>

</CFLOOP>

That only creates a single array with all the items listed, for as many rows (items) as there are in the whole table. I have no idea how to loop through and match users with their entry. I've tried nesting loops and queries but the inner query is not producing the desired results.

 

[rh71]

is there no SQL join query that would create:

USER - ITEMS
john@test.com - whistle phone radio candy
barry@test.com - whistle
larry@test.com - keys candy
ted@test.com - keys clock
cory@test.com - clock radio
zack@test.com - phone

 

[theknight571]

Ok... I dug around a little and found this solution here: http://stackoverflow.com/questions/...unction-to-join-multiple-rows-from-a-subquery

I tested it on an MSSQL2005 database I have here in the office and it seems to work.

SELECT UserMain, 
 (SELECT CAST(b.Item AS VARCHAR(MAX)) + ' '
   FROM (SELECT DISTINCT UserMain, Item FROM BLAH UNION SELECT DISTINCT UserAlt, Item FROM BLAH) b
   WHERE a.UserMain = b.UserMain
   FOR XML PATH ('')) as ItemList
FROM (SELECT DISTINCT UserMain FROM BLAH UNION SELECT DISTINCT UserAlt FROM BLAH) a

 

[rh71]

you're awesome for taking the time to help out... I will let you know.

BTW, that stackoverflow site looks like a great resource... I came across it yesterday but didn't dig around enough to find that... appreciate it.

 

[rh71]

EDIT>>> so far it's working with a simple UNION statement... more testing...

ORIGINAL>>> After many tries I have been unable to get that method to create a list to work. The query and its many variants on the stackoverflow website come back with syntax errors and since I'm almost out of time, I had to do the next best thing which is loop through and create 1 email containing all items per usermain and 1 email containing all items per useralt. So the most emails they'd get is actually 2 (instead of 1 email each item as discussed at the very beginning). Not so bad, but it could be better.

I've been using simplified data to present this problem. Using a more accurate model, this is what I'm actually faced with. Maybe you can help me another way:

Table blah:

Current Queries:
SELECT distinct(user1)
FROM blah
let's call this loop1

SELECT distinct(user2)
FROM blah
let's call this loop2

SELECT distinct(user3)
FROM blah
let's call this loop3

Current results:
(done via loop1, distinct user1):
John's email: whistle, tv
Cory's email: candy
Frank's email: train

(done via loop2, distinct user2):
Larry's email: whistle
John's email: candy
Cory's email: tv, train

(done via loop3, distinct user3):
Bob's email: whistle, tv
Tom's email: candy, train

So you can see with this method, some people are going to get multiple emails (john and cory both got 2). Is there a way to create a better SQL statement so that it captures all the distinct users, across all 3 user columns? That would allow me to just use the 1 query and result in 1 email each.

EDIT>>> so far it's working with a simple UNION statement... more testing...

SELECT distinct(user1) as CurrUser
FROM blah
UNION
SELECT distinct(user2)
FROM blah
UNION
SELECT distinct(user3)
FROM blah

(then run against #curruser#)

 

[GeekDrew]

I would do something along these lines:

SELECT item, user
FROM
(
SELECT item, user1 as user
FROM blah
UNION ALL
SELECT item, user2 as user
FROM blah
UNION ALL
SELECT item, user3 as user
FROM blah
) AS t0
WHERE t0.user = %currentuser%

Without the where statement, the result set would look somewhat like this:

whistle john
candy cory
tv john
train frank
whistle larry
candy john
tv cory
train cory
whistle bob
candy tom
tv bob
train tom

With the where statement, you'll get a list of all of the items that that person is associated with, regardless of whether they are associated as user1, user2, or user3.

 

[BoberFett]

Just break it into logical steps. Since you only want to send one email per email address you first need a unique list of email addresses. Then simply use that as a subquery in a join to determine which objects have not been sent for that email address. This seems to do exactly what you're looking for, at least in MSSQL2005

select sq.Email, BLAH.Item
from BLAH
join (select UserMain as email from BLAH union select UserAlt from BLAH) sq on BLAH.UserMain = sq.email or BLAH.UserAlt = sq.email
where BLAH.sent = 'no'

After that you're just walking the results and looking for changes in the email column to gather a list of responsible items.