SQL code for newletter

aarontham
aarontham used Ask the Experts™
on
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

   
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

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

Author

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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: ';'
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

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;

Author

Commented:
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


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

sorry, this way
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial