DISTINCT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DISTINCT

Author

Topic

yanaho
Starting Member

20 Posts

Posted - 2010-02-26 : 12:36:52

Sorry, my knowledge is limited.

I have 2 tables, Tenants, Certificates.
Certificates has FK TenantID.

I need to list (DISTINCT Tenants.TenantID, Tenants.TenantName), and (Most recent Certificates.ExpiryDate).

Stipulation is there may not be any certificates on file for Tenant, but tenantc still needs to be listed.

Any help would be great!

Thank you!

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 12:48:39

Use a left join.

PBUH

yanaho
Starting Member

20 Posts

Posted - 2010-02-26 : 13:04:49

Could you show an example?
I tried left join and get same result, all records from Certificates..?

SELECT Tenants.TenantID, Tenants.TenantName, Certificates.ExpiryDate, Tenants._BuildingID
FROM Tenants LEFT JOIN
Certificates ON Tenants.TenantID = Certificates._TenantID
WHERE (Tenants._BuildingID = 1)

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 13:17:55

Can you pls post some sample data?

PBUH

yanaho
Starting Member

20 Posts

Posted - 2010-02-26 : 13:27:25

TenantID TenantName
1 Tenant1
2 Tenant2

CertificateID ExpiryDate _TenantID
1 2010-01-01 1
2 2011-01-01 1
2 2011-01-01 2

I need to display all tenants, with most recent certificate, if no certificate exists, still display tenant

Thanks!

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 13:36:07

[code]
declare @tbl as table(tenantid int,name varchar(40))
insert into @tbl
select 1,'Tenant1' union all
select 2,'Tenant2' union all
select 3,'Tenant3'

declare @tbl1 as table(certificateid int,expirydate datetime,TenantID int)
insert into @tbl1
select 1,'2010-01-01',1 union all
select 2,'2011-01-01',1 union all
select 2,'2011-01-01',2

select t.tenantid,MAX(t1.expirydate)as recentcertificatedate from @tbl t
left join @tbl1 t1 on t.tenantid=t1.tenantid
group by t.tenantid

[/code]

PBUH

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-26 : 13:38:24

Try this
SELECT a.TenantID,a.TenantName,b.Certificate,b.ExpiryDate
from Tenants a LEFT JOIN (SELECT TenantID,MAX(ExpiryDate) as ExpiryDate from Certificates GROUP BY TenantID) b on a.TenantID = b.TenantID

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-26 : 13:39:07

yanaho
Starting Member

20 Posts

Posted - 2010-02-26 : 13:50:16

That was it vijayisonly.

Thanks a bunch!
Saved me headaches.

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 13:54:04

quote:
Originally posted by yanaho

That was it vijayisonly.

Thanks a bunch!
Saved me headaches.

Well dont see any difference in the output of the query between vijay & mine

PBUH

MrQuizzles
Starting Member

20 Posts

Posted - 2010-02-26 : 13:58:50

Wait, here's another method of getting the same result!


SELECT TenantID, TenantName, (SELECT MAX(ExpiryDate) FROM Certificates c WHERE c.TenantID = t.TenantID) AS certificate FROM Tenants t;

yanaho
Starting Member

20 Posts

Posted - 2010-02-26 : 14:04:07

Sorry Idera, I didn't try it because I didn't understand it, and I'm trying to learn.

Thanks for all your help everyone.

yanaho
Starting Member

20 Posts

Posted - 2010-02-26 : 14:05:25

If there is performance benefits for one query over the other than I would try it and learn it. Otherwise, the easiest to understand it the easiest to implement.

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 14:06:15

quote:
Originally posted by yanaho

Sorry Idera, I didn't try it because I didn't understand it, and I'm trying to learn.

Thanks for all your help everyone.

No prob

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources