Getting data back in 1 row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting data back in 1 row

Author

Topic

Zander1983
Starting Member

3 Posts

Posted - 2011-04-08 : 15:56:02

Hi
im creating a messages section on my site which will work the same as facebook messages i.e. with threads

I have the following tables:

Message table

MessageID -> primary key
ReplyToID -> this keep track of all messages that belong to the same thread
FromID -> member id of sender
ToID -> member id of receiver
Message -> the text of the messsage
Date -> date message was sent

i also have a member table containing Member ID and Username

I would like to get back in one row something like this:

MessageID, ReplyToID, FromID, ToID, Message, Date, FromUsername, SenderUsername

a simple join wont work as it returns 2 rows for every 1 row in the message table when it joins to member table....

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 17:56:46

Can you post the simple join that you tried? It may be that you are not joining on enough columns (or on too many columns) that is causing duplicate rows.

Zander1983
Starting Member

3 Posts

Posted - 2011-04-08 : 18:21:20

Hi
this is the join im using

SELECT * from messages ms
join member m on (m.MemberID = ms.FromID or m.MemberID = ms.ToID)
WHERE ReplyToID = 8

so for each row in messages, im going to get 2 rows, one with the username for FromID, and one with the username for ToID...

Im sure i need to use a cursor here, i just dont know how..

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-08 : 19:10:37

SELECT *
FROM dbo.Messages ms
LEFT JOIN dbo.Member frID ON frID.MemberID = ms.FromID
LEFT JOIN dbo.Member toID ON toID.MemberID = ms.ToID
WHERE ms.ReplyToID = 8;

Zander1983
Starting Member

3 Posts

Posted - 2011-04-08 : 19:39:22

Thanks Jeff, this worked perfectly, its what i was looking for

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-08 : 19:57:10

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

After a few decades of doing other people's jobs for them, why it is so much to ask them to just follow minimal, basic Netiquette?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources