I fall in mistake Please help me my code not work correct

Question

Hello every one

I fall in mistake

Please help me

I have 3 table

1 – User (id, email)

2 – Friend (sender, receiver, state)

3 – Post (sender, text, date)

Now

I want to select User (id), Post (*)

But

Select user’s post who is login and the other guy that receive a request for follow from the user and my code is:

Create PROCEDURE showpost

@email nvarchar (50)

AS

BEGIN

Select User.Code as code, post.* from post, friend, User where (User.email=post.sender) and ((friend.sender=@email or friend.reciver=@email) and state='ok' and (post.sender=friend.sender or post.sender=friend.reciver)) order by post.code desc

END

This is a problem

When show post the user’s post will be Repeated as many as his request for follow

Attached file is better

http://s6.uplod.ir/i/00629/4m2dkharb1oe.png

 

Answer 1

Hello @Nofact  you can use distinct keyword for your query like as

 

 

Select DISTINCT User.Code as code, post.* from post, friend, User where (User.email=post.sender) and ((friend.sender=@email or friend.reciver=@email) and state='ok' and (post.sender=friend.sender or post.sender=friend.reciver)) order by post.code desc

 i also try this method for your tables and  found correct o/p

 

Result Without Distinct Keyword

 

After Distinct Keyword

 

 

Please tell me if this solution is not as you required . I will try again.

Answer 2

thank you thank you thank you alot

Answer 3

http://s6.uplod.ir/i/00629/br1930c8nskx.png another pic from relation

Answer 4

hello @Nofact  it is difficult to understand your table structure can you post relationship of your tables

 

Answer 5

I think You need To go by applying inner join on one by one table