how to join this sql query [Resolved]

Hi,
If you want to show ModelRank and SalesRank Separately then the following query may help you.

SELECT  a1.model,a1.rating, COUNT(a2.rate) ModelRank,Sales_Rank=0

into #temp

FROM dellfeedback a1, dellfeedback a2 





WHERE a1.commands like '%light%'  and (a1.rating!='poor' and a1.rating!='Not Poor' and (a1.rate <= a2.rate or (a1.rate=a2.rate and a1.model = a2.model))) 

GROUP BY a1.model, a1.rating 

ORDER BY ModelRank DESC





insert into #temp  

--Rank Sony

SELECT a1.model,a1.rating,ModelRank=0,  COUNT(a2.rate) Sales_Rank 

FROM Sonyfeedback a1, Sonyfeedback a2 

WHERE a1.rating!='poor' and a1.rating!='Not Poor' and(a1.rate <= a2.rate or (a1.rate=a2.rate and a1.model = a2.model) )

GROUP BY a1.model, a1.rating 

ORDER BY Sales_Rank DESC



select * from #temp 



drop table #temp 

Thanks,
Sanjay

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Hi gowthaman,
Please let me know which query did work for you.
or you may click on login to Mark As Answer in that section.

Thanks,
Sanjay

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

You want the records to be displyed side by side

or you want records on common columns?

Thanks,
Sanjay

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

yes sir

gowthaman8870226416

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

if under common columns:
after 1st query you can use union
like :
SELECT a1.model,a1.rating, COUNT(a2.rate) ModelRank

FROM dellfeedback a1, dellfeedback a2

WHERE a1.commands like '%light%' and (a1.rating!='poor' and a1.rating!='Not Poor' and (a1.rate <= a2.rate or (a1.rate=a2.rate and a1.model = a2.model)))

GROUP BY a1.model, a1.rating

ORDER BY ModelRank DESC

UNION

SELECT a1.model,a1.rating, COUNT(a2.rate) ModelRank

FROM Sonyfeedback a1, Sonyfeedback a2

WHERE a1.commands like '%light%' and (a1.rating!='poor' and a1.rating!='Not Poor' and (a1.rate <= a2.rate or (a1.rate=a2.rate and a1.model = a2.model)))

GROUP BY a1.model, a1.rating

ORDER BY ModelRank DESC


if you want to merge both the queries without using union then , can store the result in a #temp or @temp table and then after storing query results you can query it from the temporary table.

Thanks,
Sanjay

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Sir i all ready try it that query but display following Error
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'UNION'.

gowthaman8870226416

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

can you add script of your table and
output of select top 10 * from table
here
i will modify the qurey

Thanks,
Sanjay

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

sir i attach my script and backup following link sir http://www.dotnetfunda.com/forums/thread7667-how-to-join-this-sql-query.aspx

gowthaman8870226416

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

sir here i attach my source
 Download source file

gowthaman8870226416

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

In one Query you have used ModelRank and 2nd query you have used Sales_Rank

Do you need separate column for this ? or let me know what would be your column name?

Thanks,
Sanjay

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Hi ,
If you want ModelRank and SalesRank in a single coulumn then use following query

SELECT  a1.model,a1.rating, COUNT(a2.rate) ModelRank

into #temp

FROM dellfeedback a1, dellfeedback a2 





WHERE a1.commands like '%light%'  and (a1.rating!='poor' and a1.rating!='Not Poor' and (a1.rate <= a2.rate or (a1.rate=a2.rate and a1.model = a2.model))) 

GROUP BY a1.model, a1.rating 

ORDER BY ModelRank DESC





insert into #temp  

--Rank Sony

SELECT a1.model,a1.rating,  COUNT(a2.rate) ModelRank 

FROM Sonyfeedback a1, Sonyfeedback a2 

WHERE a1.rating!='poor' and a1.rating!='Not Poor' and(a1.rate <= a2.rate or (a1.rate=a2.rate and a1.model = a2.model) )

GROUP BY a1.model, a1.rating 

ORDER BY ModelRank DESC



select * from #temp 



drop table #temp 

Thanks,
Sanjay

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

i got it sir thank u

gowthaman8870226416

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

hh

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator