CODEWITHSUNDEEP
sqlsql-server
atabrizi
atabrizi

Reputation: 918

select from two tables with certain sorting

I have this tables:

Relate : (ID,ArticleID,rArticleID)

TagInArticle: (ItemID,TagID,ArticleID)

I want to have the selection of two tables together. But the result of relate table show first and result of TagInArticle table comes after that by its own sort by count

for select of per table I use this codes :

quey1:

select  ArticleID, COUNT(*) 
as counts from   TagInArticle  
where TagID in  ( select [TagID] 
from TagInArticle     
where ArticleID=5  ) and ArticleID!=5 
group by ArticleID

and :

query2: 

select rArticleID from Relate
where ArticleID=1

if result of table TagInArticle was : 

ArticleID    counts

1          |    1

5          |    3

and result of Relate table 

ArticleID    

7

I want to show :

7

5

1

How can I do that?

Upvotes: 0

Views: 48

Answers (2)

M.Ali
M.Ali

Reputation: 69514

Is this something you are looking for 

SELECT R.ArticleID
      ,COUNT(T.ArticleID) AS Total
FROM Relate R INNER JOIN TagInArticle T
ON R.ArticleID = T.ArticleID
WHERE R.ArticleID = 1       
GROUP BY R.ArticleID
ORDER BY Total DESC

I dont have the foggiest clue what you are trying to do but after looking at your provided result sets you can do something like this.. A simple UNION ALL

SELECT ArticleID
(
select  ArticleID, COUNT(*) 
as counts from   TagInArticle  
where TagID in  ( select [TagID] 
from TagInArticle     
where ArticleID=5  ) and ArticleID!=5 
group by ArticleID 
)A
UNION ALL
select rArticleID 
from Relate
where ArticleID=1

Upvotes: 1

AK47
AK47

Reputation: 3797

Try this,

select  r.ArticleID, ta.TagIDCount as counts 
from   Relate as r
left join 
    (
        select ArticleID,count([TagID]) as TagIDCount
        from TagInArticle    
        group by  ArticleID
    )as   ta on ta.ArticleID = r.ArticleID
order by r.ArticleID desc

EDITED for Descending order

Upvotes: 0

Related Questions