How to Obtain Unique Results from a Select with Joined Records

Select distinct records on a join

A DISTINCT should do what you're looking for:

SELECT DISTINCT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId 
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;

That would return only distinct items.ItemName, items.ItemId tuples.

SELECT DISTINCT values after a JOIN

You need to include the restriction on person id in your join and use an outer join. Outer joins are used when you want to return information even if there are no records in the table you're joining to. Try

SELECT person_id, vehicles.* 
FROM vehicles 
LEFT OUTER JOIN Owners on vehicles.vehicle_id = owners.vehicle_id 
and person_id = 1

Get DISTINCT records on INNER JOIN SQL Server

  with cte as (SELECT ROW_NUMBER() over(PARTITION BY sdi.ID order by sdi.ID) as rn,totalCount = COUNT(*) OVER(), mailbox.ID as mailboxID, 
          sdi.ID as sdiID
           FROM [SDI].dbo.UserDocumentLocationOutbox mailbox 
           INNER JOIN [SDI].dbo.SDITransaction
           sdi on mailbox.SDITransactionID=sdi.ID 
         INNER JOIN [SYSDB].dbo.DocumentType doc on sdi.DocumentTypeID=doc.ID 
    where mailbox.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7' 
    and mailbox.UserProfileID = 'f9791614-8cc0-42e3-87d1-53709bc1e099' 
    and doc.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7' 
    and doc.Active=1 and doc.HideInMailbox=0  
    order by sdi.ProcessedDateTime desc 
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY)
select totalCount, mailboxID, sdiID
from cte 
where rn < 2

Get Distinct rows from a result of JOIN in SQL Server

You are getting duplicate because there are multiple photos per album. To get one, use row_number():

SELECT Album_Name AS Album_Name, a.Album_Date AS Album_Date, a.Page_ID AS PageID,
       p.Image_ID AS Image_ID, p.Image_Small AS Image_Small 
FROM Album_Name a left outer JOIN
     (select p.*, row_number() over (partition by Album_Id order by Image_ID) as seqnum
      from Album_Photos p 
     ) p
    ON a.Album_ID = p.Album_ID and seqnum = 1;

SQL Distinct value over joined tables

To provide my solution:
I ended up using a nested distinct through a join and all the unnested values (all 20+) of them had to be wrapped around a MIN(x), seeing as those values didnt matter that much, so long as only one distinct value was returned.

SELECT DISTINCT WHILE JOINING TABLES

If you want distinct values, then use SELECT DISTINCT:

SELECT DISTINCT fc.Indv_Sys_Id, fc.Dt_Sys_Id
FROM MiniHPDM..Fact_Claims fc INNER JOIN
     MiniHPDM..Dim_Date d 
     ON fc.Dt_Sys_Id = d.Dt_Sys_Id;

DISTINCT is not a function. It is a keyword, used in this case with SELECT.

Given that the join should succeed, I imagine that this returns the same results:

SELECT DISTINCT fc.Indv_Sys_Id, fc.Dt_Sys_Id
FROM MiniHPDM..Fact_Claims fc
WHERE fc.Dt_Sys_Id IS NOT NULL;

How to select unique records by SQL

With the distinct keyword with single and multiple column names, you get distinct records:

SELECT DISTINCT column 1, column 2, ...
FROM table_name;

How to query unique or distinct values from joining 3 tables which have one to many relationships

your are missing group by(), since you are using aggregate function string_agg()

SELECT ca.Category, string_agg(co.Company, '|'), string_agg(br.Country, '|') as Countries 
FROM Category ca
LEFT JOIN Company co ON co.Category = ca.Name
LEFT JOIN Branches br ON br.ParentCompany = ca.CompanyName
GROUP BY ca.Category

Join Only Unique Values From 2 Tables

Are you trying something like this:

 select t1.column2,t2.column2 
 from table1 t1 
 inner join 
 table2 t2 on t1.column2= t2.column2 
 where t1.column1 in ('c1value2', 'c1value3')
 group by t1.column2,t2.column2 ;

create table table1  (
  column1 varchar(50), 
  column2 varchar(50) );

insert into table1 values ( 'c1value1', 'c2value1-1'),
                          ( 'c1value2', 'c2value1-2'),
                          ( 'c1value3', 'c2value1-3');

create table table2  (
  column1 varchar(50), 
  column2 varchar(50) );

insert into table2 values ( 'c1value1', 'c2value1-1'),
                          ( 'c1value1', 'c2value2-1'),
                          ( 'c1value1', 'c2value3-1'),
                          ( 'c1value2', 'c2value1-2'),
                          ( 'c1value3', 'c2value1-3');

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/70

Edit: If you want the unique values of column2 of both tables based on column1 of table1, you can use a subquery for selecting the distinct values for column1 and use it in the where condition.

 select t1.column2,t2.column2 
 from table1 t1 
 inner join 
 table2 t2 on t1.column2= t2.column2 
 where t1.column1 in (select distinct column1 from table1)
 group by t1.column2,t2.column2 ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/72

---

Related Topics