CODEWITHSUNDEEP
mysql
everisk
everisk

Reputation: 183

How to select distinct rows from a table and join the selected row to another table in mysql

I have a query like below to select 'sid' with highest count and its 'lid'. Now I'd like to select details of 'lid' from another table and keep the 'tot' in the result set. Is that possible?

select count(distinct sid) as tot, lid 
from wt_stats_linkclicks 
where statsid IN (1) 
GROUP BY lid order by tot DESC limit 1

Thanks!

Upvotes: 2

Views: 438

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

If your linkidactually is lid, this might be one way to do it.

SELECT  *
FROM    dbo.liddetails ld
        INNER JOIN (
           SELECT COUNT(DISTINCT(sid) AS tot, lid  
           FROM   wt_stats_linkclicks  
           WHERE  statsid IN (1)  
           GROUP BY 
                  lid
        ) ldtot ON ldtot.lid = ld.lid

Upvotes: 3

Related Questions