how to join this sql query [Resolved]

Posted by Gow.net under Sql Server on 2/7/2012 | Points: 10 | Views : 1220 | Status : [Member] | Replies : 13
TableName:dellfeedback
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



TableName:Sonyfeedback
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


how to join both table please any one tell me

gowthaman8870226416


Responses

Posted by: Sksamantaray on: 2/7/2012 [Member] Silver | Points: 50

Up
0
Down

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

Posted by: Sksamantaray on: 2/7/2012 [Member] Silver | Points: 50

Up
0
Down

Resolved
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

Posted by: Sksamantaray on: 2/7/2012 [Member] Silver | Points: 25

Up
0
Down
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

Posted by: Gow.net on: 2/7/2012 [Member] Starter | Points: 25

Up
0
Down
yes sir

gowthaman8870226416

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

Posted by: Sksamantaray on: 2/7/2012 [Member] Silver | Points: 25

Up
0
Down
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

Posted by: Gow.net on: 2/7/2012 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Sksamantaray on: 2/7/2012 [Member] Silver | Points: 25

Up
0
Down

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

Posted by: Gow.net on: 2/7/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Gow.net on: 2/7/2012 [Member] Starter | Points: 25

Up
0
Down
sir here i attach my source
 Download source file

gowthaman8870226416

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

Posted by: Sksamantaray on: 2/7/2012 [Member] Silver | Points: 25

Up
0
Down

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

Posted by: Sksamantaray on: 2/7/2012 [Member] Silver | Points: 25

Up
0
Down
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

Posted by: Gow.net on: 2/7/2012 [Member] Starter | Points: 25

Up
0
Down
i got it sir thank u

gowthaman8870226416

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

Posted by: Rajni123 on: 2/7/2012 [Member] Starter | Points: 25

Up
0
Down
hh

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

Login to post response