Remove redundant data in select statement

Question

Post reply

Remove redundant data in select statement

RKaram

SSCommitted

Points: 1518
More actions
September 11, 2014 at 4:58 am

#299173

Hello,
Having these two tables
Table1:
ContactDetail
---------------
ID Name
-- --------
1 name1
2 name2
3 name3
Table2:
Email
------
ID ContactDetailID EmailAddress
-- ----------------- ---------------
1 1 Address1
2 1 Address2
3 1 Address3
4 2 Address4
5 2 Address5
6 2 Address6
when i select all contacts with emails by the below query
SELECT CD.ID,
CD.NAME,
EM.EMAILADDRESS
FROM ContactDetail CD
LEFT JOIN Emails EM ON EM.ContactDetailID = CD.ID
I got the below result
ID Name EmailAddress
-- -------- --------------
1 name1 Address1
1 name1 Address2
1 name1 Address3
2 name2 Address4
2 name2 Address5
2 name2 Address6
3 name3 NULL
what is the best solution to get it like below
ID Name EmailAddress
--- ------ ---------------
1 name1 Address1
NULL NULL Address2
NULL NULL Address3
2 name2 Address4
NULL NULL Address5
NULL NULL Address6
3 name3 NULL
Thank you in advance,
Rabih,
rkaram

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

To be blunt. Don't.

That's formatting for display, not data retrieval (unless Address3 has no user, which is what your example implies).

If you want to display like that, look at the settings and properties of whatever you're using as a front end. Reporting Services for example will let you chose the names as a grouping level and the addresses as details.

In SQL however, the rows should be independant (especially since there's no order by or anything suggesting order in your example) and so your example is equivalent to

NULL NULL Address2

NULL NULL Address3

NULL NULL Address5

NULL NULL Address6

1 name1 Address1

2 name2 Address4

3 name3 NULL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RKaram SSCommitted Points: 1518 More actions · Answer 2

I know that I can do it from any front end but I need to send it to a user in this format when he call procedure.

The problem is they asked me to return the reault in that way and i need the best solution for that.

I know how to do it with a loop and other ideas but i m asking you as experts for a better performance what will be the best solution to return the result in this way.

Any way Thank you for your reply

Rabih

rkaram

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

I have to agree with Gail. The grouping (presumably for a leveled report) should be done on the front end.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions