How to replace two values from a single table

Question

How to replace two values from a single table

nadarajan_v

SSC-Addicted

Points: 406
More actions
February 15, 2011 at 2:48 pm

#234544

Hello,
I have two tables, TBS_Posts and TBS_UserDetails. The TBS_Posts table has emailids of users in two columns, OriginalPostBy and LastPostBy. The values in these two columns could be same as ayz@yahoo.com, xyz@yahoo.com or different as xyz@yahoo.com and abc@hotmail.com. Since the emailid could not be displayed on a public website, i have to replace it with the corresponding ScreenName saved in another table, TBS_UserDetails, along with the EmailID. The following query works fine when the email values in both OriginalPostBy and LastPostBy are the same. But when they are different, it stll puts just a single screenname for both the email values. Essentially the replacement works only when the emailids are same but not when they are different. I may have to join the tables twice, once on the OriginalPostBy and on the LastPostBy. I tried that, still getting he same result. I would apprciate for any help how this could be resolved.
SELECT TBS_Posts.PostID, U.ScreenName AS OriginalPostBy,
TBS_Posts.ParentPostID, TBS_Posts.Title, TBS_Posts.Body,
TBS_Posts.LastPostDate, U.ScreenName AS LastPostBy,
FROM TBS_Posts
JOIN TBS_UserDetails U ON
TBS_Posts.OriginalPostBy = U.EmailID
WHERE PostID = @ThreadPostID OR ParentPostID = @ThreadPostID
ORDER BY AddedDate ASC
Thanks

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

nadarajan_v

You more than likely will get a tested T-SQL block of code if you posted your table definitions, some sample data following the instructions and using the T-SQL given in the article whose link is the first entry in my signature block.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

chris_n_osborne Old Hand Points: 317 More actions · Answer 2

I am working on assumptions about your table designs based on your description.

Your query is asking for screen names this way:

U.ScreenName AS OriginalPostBy

U.ScreenName AS LastPostBy

Where U = the TBS_UserDetails table.

If TBS_UserDetails is designed the way I think it is, one e-mail address per

EmailID value, and ScreenName appears to be coming from TBS_UserDetails, then

your query will always return just one screen name.

I think you need to have subqueries directly on the SELECT list like the

following code...

CREATE TABLE TBS_UserDetails

(EmailID NVARCHAR(320)

,ScreenName NVARCHAR(32)

,CONSTRAINT pk_EmailID PRIMARY KEY (EmailID)

)

go

INSERT INTO TBS_UserDetails

(EmailID

,ScreenName

)

SELECT 'a@a.com', 'SQL Questions' UNION

SELECT 'b@b.com', 'SQL Answers'

go

CREATE TABLE TBS_Posts

(PostID INTEGER

,ParentPostID INTEGER

,OriginalPostBy NVARCHAR(320)

,LastPostBy NVARCHAR(320)

,LastPostDate DATETIME

,Title NVARCHAR(100)

,Body TEXT

,CONSTRAINT pk_TBS_Posts PRIMARY KEY (PostID)

,CONSTRAINT fk_TBS_Posts_TBS_Posts_ParentPostID

FOREIGN KEY (ParentPostID)

REFERENCES TBS_Posts (PostID)

,CONSTRAINT fk_TBS_Posts_TBS_UserDetails_EmailID_1

FOREIGN KEY (OriginalPostBy)

REFERENCES TBS_UserDetails (EmailID)

,CONSTRAINT fk_TBS_Posts_TBS_UserDetails_EmailID_2

FOREIGN KEY (LastPostBy)

REFERENCES TBS_UserDetails (EmailID)

)

go

INSERT INTO TBS_Posts

(PostID

,ParentPostID

,OriginalPostBy

,LastPostBy

,LastPostDate

,Title

,Body

)

SELECT 1, 1, 'a@a.com', 'a@a.com', '2011-02-01 00:00:00.000', 'My query', 'My query need help.' UNION

SELECT 2, 1, 'a@a.com', 'b@b.com', '2011-02-01 00:10:00.000', 'My query', 'Post your ddl, data, and query so far.'

go

SELECT P1.PostID

,(SELECT U01.ScreenName

FROM TBS_UserDetails as U01

WHERE U01.EmailID = P1.OriginalPostBy) AS OriginalPostBy

,P1.ParentPostID

,P1.Title

,P1.Body

,P1.LastPostDate

,(SELECT U02.ScreenName

FROM TBS_UserDetails as U02

WHERE U02.EmailID = P1.LastPostBy) AS LastPostBy

FROM TBS_Posts AS P1

1 SQL Questions 1 My query My query need help. 2011-02-01 00:00:00.000 SQL Questions

2 SQL Questions 1 My query Post your ddl, data, and query so far. 2011-02-01 00:10:00.000 SQL Answers

These results do appear to return different e-mail addresses when the TBS_Posts table contains different e-mail addresses in the two columns in question, OriginalPostBy and LastPostBy.

I apologize for any mistaken assumptions on my part on the design of your tables and nature of the date in question.

Notes:

By the way, I do not personally think it is a good idea to be storing an

e-mail address in the TBS_Posts table, much less two different e-mail

addresses. The tables should be Posters (or UserDetails, whatever name)

and Posts. The Posts table would have a foreign key pointing at the primary

key of the Posters table, which is where the e-mail address (and screen name)

of each poster would be located.

nadarajan_v SSC-Addicted Points: 406 More actions · Answer 3

Hello Chris_n_Osborne,

Your assumption is correct and thank you so much for your code snippet. I was able to solve my issue. I agree with you that the table structure needs to be normalized properly. Since I am working on an existing project, unfortunately, I don't have much leeway :crying:

Thanks

chris_n_osborne Old Hand Points: 317 More actions · Answer 4

nadarajan_v (2/15/2011)
Hello Chris_n_Osborne,
Your assumption is correct and thank you so much for your code snippet. I was able to solve my issue.

You're welcome. 😀

nadarajan_v (2/15/2011)
I agree with you that the table structure needs to be normalized properly. Since I am working on an existing project, unfortunately, I don't have much leeway :crying:
Thanks

It's usually that way.