SQL query answer

Question

Using SQL I am selecting the following:

SELECT itemid, custid, email FROM tableA;

I can have multiple itemid for each one Custid and only one email like

itemid1, custid, email
itemid2, custid, email
itemid3, custid, email

How can i send only one email per customer specified that he has 3 itemid?

thanks

Aare you wanting a "Count" or a "List" of items? If list, answer varies based on rdbms... so what flavor of SQL?? MSFT, MySQL, oracle, etc? — xQbert, Mar 11 '15 at 18:08
search online for `select distinct` which is another possibility — aucuparia, Mar 11 '15 at 18:10
It seems like a bad idea to have email in the same table as itemid (not a normalized schema). I'd normally (no pun intended) expect to see one table of email, custid and another of custid, itemid and perhaps another of itemid, itemname or whatever. That would be a normalized schema with custid, itemid as a junction table. — Jim Dennis, Mar 11 '15 at 18:25

Jen R · Answer 1 · 2015-03-11T18:57:54.593

1

select count(itemid), custid, email 
from tableA
group by email, custid

After clarification that the OP is joining tables and wants a list of items in one row with the email address, we have this query:

select b.custid, b.email, 
   itemids = STUFF((select ',' + itemid 
             from tableA a 
             where a.custid = b.custid FOR XML PATH('')), 1, 1, '') 
from tableB b

This will do it, but if you need more info about the items I would strongly recommend getting a list of customers and then looping through them to get the item info as you go.

edited Mar 11 '15 at 18:57

answered Mar 11 '15 at 18:04

Jen R

1,527
18
23

1

Change that to `SELECT count(itemid), custid, email ...` and I think you'll have it. – JNevill Mar 11 '15 at 18:05
lol... I copied, pasted, and added group by... Forgot to change the itemID part. Thanks @JNevill! – Jen R Mar 11 '15 at 18:06
Though I do feel like I just did someone's homework for them. I'm going to pretend it's too late in the semester for such a simple question. – Jen R Mar 11 '15 at 18:07
i know that but probably i did not explain very well. from that sql i have these items . .. for the same email (assume is 1 customer with 3 itemids) is it possible to write a new one sql that sends one email and describe that the specific customer has 3 itemid , itemd1, itemd2, itemd3( i do not want to count them) – georg9 Mar 11 '15 at 18:13
Please add more info to your question then. What kind of database are you using? What is the table structure? Sample data? Desired output example? As you have described it, COUNT() and GROUP BY solves the problem. – Jen R Mar 11 '15 at 18:18
db: sql server, i am using multiple tables ( using left join) in order to get some fields from each one. the output is some of this – georg9 Mar 11 '15 at 18:29
OK, then we would need to know the relevant fields from each of those tables – Jen R Mar 11 '15 at 18:31
db: sql server, i am using multiple tables ( using left join) in order to get some fields from each one. the output is some of this itemid1, custidA, emailA itemid2, custidA, email A. there is the same email (semailA) for the customer (custid) and i want to send a db email . the problem as i described is that i only want to send ONLY ONE email for the customer to tell him that he has itemid1 and itemid2 . thanks – georg9 Mar 11 '15 at 18:35
This is essentially the GROUP_CONCAT function in MySQL. Unfortunately sql-server has no similar aggregate function. There are work arounds though: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server – JNevill Mar 11 '15 at 18:41
Edited the answer to accommodate the list of items requirement. – Jen R Mar 11 '15 at 18:58

score 0 · Answer 2 · answered Mar 11 '15 at 18:53

You could use function like this for all your custid and email :

ALTER        FUNCTION itemrow(@custid int, @email varchar(max)) --check your datatypes
RETURNS VARCHAR(max) AS
BEGIN
  DECLARE @itemrow VARCHAR(max)
  SELECT  @itemrow = COALESCE(@itemrow + ', ', '') + COALESCE(itemid,'')
  FROM tableA
  where email = @email
  and custid = @custid
return @itemrow
END

SQL query answer

2 Answers2