CODEWITHSUNDEEP
sqlsql-server
user3863009
user3863009

Reputation: 49

How to get distinct record from two tables in sql?

i have two table showin below

news (table name)

NewsId   NewsDate
1       25-03-2014

2       29-03-2014

newsImageGal (table name)

newsSr   newsId      newsImages
1           1        images/i.jpg
2           1        images/j.jpg
3           1        images/k.jpg
4           2        images/l.jpg
5           2        images/m.jpg
6           2        images/n.jpg

i want a result like

 NewsId   NewsDate          newsId      newsImages
  1        25-03-2014         1          images/i.jpg
  2        9-03-2014          2          images/l.jpg

i have tried using join query and group by function but it shows duplicate multiple records how to solve this query?

Upvotes: 1

Views: 67

Answers (5)

Eslam Totti
Eslam Totti

Reputation: 125

SELECT n.NewsId,
       n.NewsDate,
       ng.newsId,
       ng.newsimages
FROM   news n,
       (SELECT *,
               Rank()OVER(PARTITION BY newsId ORDER BY newsSr DESC) AS rank
        FROM   newsImageGal) ng
WHERE  n.NewsId = ng.newsId
       AND rank = 1

Upvotes: 0

sandipon
sandipon

Reputation: 986

select n.NewsId,n.NewsDate,g.newsImages
from news n
join newsImageGal g on n.NewsId=g.newsSr

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Try this.

SELECT a.NewsId,
       a.NewsDate,
       b.newsImages
FROM   news  a
       JOIN (SELECT Row_number()OVER(partition BY newsid ORDER BY newssr) rn,
                    *
             FROM   newsImageGal) b
         ON a.NewsId = b.newsId
WHERE  rn = 1

Key here is to find the first image that got added into newsImageGal table per newsid. so use the window function to create Row_number per newsid in order of newsSr. 

SELECT Row_number()OVER(partition BY newsid ORDER BY newssr) rn,
                        *
FROM   newsImageGal

From the above query you can see the rn=1 is the first image that got added into the newsImageGal join the result with news table and filter the result with rn=1

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10264

You can write as

;WITH CTE AS 
( SELECT N.NewsId
        ,N.NewsDate
        ,NIG.newsImages
        ,ROW_NUMBER() OVER (PARTITION BY  N.NewsId ORDER BY NIG.newsSr ASC)
        AS rownum
 FROM news N
 JOIN newsImageGal NIG ON N.NewsId = NIG.newsId
 )
SELECT NewsId,
       NewsDate,
       newsImages
FROM CTE
WHERE rownum = 1

Upvotes: 1

Dgan
Dgan

Reputation: 10285

can you try this

select distinct ng.newsId, ng.newsImages
from (
select MIN(newsSr),newsId,newsImages
from newsImageGal 
group by newsId,newsImages) ng
join news ne
on ne.newsId=ng.newsId

Upvotes: 0

Related Questions