sql query of paid and star rating logic

Pooja Chowdhury

8y777

two tables are there one starrating and one paidmember table.

ID	paid
1	1
2	1
3	0
4	1
5	1
6	1

ID	star
1	6
2	6
3	6
4	5
5	6
6	3

if star will be 6 and paid=1 it shows first if paid=0 and rating 6 it show last then check the star 5 same logic

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayesh Vaghasiya8y ago
1. declare @paid table
2. (
3. [id] integer not null identity(1, 1) primary key clustered
4. ,[paid] integer not null
5. )
6. insert into @paid values
7. (1)
8. ,(0)
9. ,(0)
10. ,(1)
11. ,(0)
12. ,(1)
13. declare @star table
14. (
15. [id] integer not null identity(1, 1) primary key clustered
16. ,[star] integer not null
17. )
18. insert into @star values
19. (6)
20. ,(5)
21. ,(6)
22. ,(5)
23. ,(3)
24. ,(3)
25. select p.[id], p.[paid] , s.[star]
26. from @paid p
27. left join @star s on p.[id] = s.[id]
28. order by p.[paid] desc , case when p.[paid] = 0 then s.[star] else p.[paid] end asc

Nitin Sontakke8y ago

Try the following script. Remember that when you ask question next time, please provide the initial script, so we don't have to type everything. Just the answer sql. Hope you get the point.

declare @paid table
(
[id] integer not null identity(1, 1) primary key clustered
,[paid] integer not null
)
insert into @paid values
(1)
,(1)
,(0)
,(1)
,(1)
,(1)
declare @star table
(
[id] integer not null identity(1, 1) primary key clustered
,[star] integer not null
)
insert into @star values
(6)
,(6)
,(6)
,(5)
,(6)
,(3)
select p.[id]
from @paid p
left outer join @star s on p.[id] = s.[id]
order by p.[paid] + s.[star] desc

Pooja Chowdhury8y ago

OUTPUT:

Nitin Sontakke8y ago

Whatever you have explained is not quite clear to me.

Please give expected output in tabular format for all combinations and also explain the logic in detail. If you cannot logically put it together then it cannot be done. Simple.