asked on

SQL code for newletter

i have below 3 table.

Address
AddID Name Email
1 A Abc@abc.com
2 B BFE@hgF.com
3 C JKF@lhn.com

Letter
LetterID Name Path
1 VGA C:\VGA.pdf
2 RAM C:\RAM.pdf
3 Mouse C:\Mouse.pdf
4 Keyboard C:\keyboard.pdf
5 LCD C:\LCD.pdf
LetterNAddress
LetAddID AddID LetterID YesNo2Send
1 1 2 1
2 1 3 0
3 1 4 1
4 1 5 1
5 3 5 1
6 3 1 1

How can i return result like below using SQL code.
C JKF@lhn.com C:\LCD.pdf;C:\VGA.pdf

ralmada

In SQL 2005 you can try this:

SELECT DISTINCT
c.Name, c.Email, paths = REPLACE( 
	( 
	SELECT [Path] AS [data()] 
	FROM Letter a 
	WHERE a.LetterID = b.LetterID
	ORDER BY [Path] 
	FOR XML PATH ('') 
	), ' ', ',')
FROM 
LetterNAddress b
inner join Address c on c.AddID = b.LetAddID
Order by c.Name

Open in new window

k_murli_krishna

You can try this one also:
SELECT A.Name, A.Email, MIN(L.Path) + ';' + MAX(L.Path) FROM Letter L, LetterNAddress LNA, Address A WHERE L.LetterID = LNA.LetterID AND A.AddID = LNA.AddID AND A.AddID = 3 GROUP BY A.Name, A.Email;

This is platform neutral except the concatenation operator + which works for SQL Server. The other commonly used one is ||. If both suggested queries till now are giving correct result, you need to measure cost and time to pick up the better performing one. To do this refer:
http://stackoverflow.com/questions/564717/measuring-query-performance-execution-plan-query-cost-vs-time-taken

aarontham

ASKER

Hi ralmada,

i got below error, i use Mysql. pls help to add one more condition thet is YesNo2Send mush be 1

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Path] AS [data()]
      FROM Letter a
      WHERE a.LetterID = b.LetterID
      ORDER BY ' at line 4

aarontham

ASKER

k_murli_krishna,

it return below value
Name Email Min(L.path)+';'Max(L.Path)
A abc@abc.com 0

with below error

Truncated incorrect DOUBLE value: ';'

ralmada

In MySQL you can do this:

SELECT 
c.Name, c.Email, group_concat(a.Path)
FROM LetterNAddress b
inner join Address c on c.AddID = b.LetAddID
inner join Letter a on b.LetterID = a.LetterID
where b.YesNo2Send = 1
group by c.Name, c.Email

Open in new window

k_murli_krishna

Now try:
SELECT A.Name, A.Email, MIN(L.Path) + ';' + MAX(L.Path) FROM Letter L, LetterNAddress LNA, Address A WHERE L.LetterID = LNA.LetterID AND A.AddID = LNA.AddID AND A.AddID = 3 AND LNA.AddID = 3 GROUP BY A.Name, A.Email;

Column Path in table Letter should be a string type i.e. CHAR/VARCHAR.

You can also try:
SELECT (SELECT Name, Email FROM Address WHERE AddID = 3), (SELECT Path FROM Letter WHERE LetterID = 5), ';', (SELECT Path FROM Letter WHERE LetterID = 1);
This is for SQL Server & Sybase. For Oracle & MySQL it will be FROM dual; For DB2 it will be FROM SYSIBM.SYSDUMMY1;

aarontham

ASKER

i guys below sql work for me.

SELECT group_concat(letter.Path) ,address.email,address.name,letter.path,letternaddress.addid FROM letternaddress,address,letter where address.addid=letternaddress.addid and letter.letterid=letternaddress.letterid and letternaddress.addid=1

but result for the group_concat(letter.Path) is below.
C:\VGA.pdf,C:\VGA.pdf

how to change the , to ; like below
C:\VGA.pdf;C:\VGA.pdf

ralmada

use replace function

SELECT replace(group_concat(letter.Path), ';', ',') ,address.email,address.name,letter.path,letternaddress.addid FROM letternaddress,address,letter where address.addid=letternaddress.addid and letter.letterid=letternaddress.letterid and letternaddress.addid=1

Open in new window

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

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

Start Free Trial