Distinct with Join to 3 Tables

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 with Join to 3 Tables

Author

Topic

zemantm
Starting Member

8 Posts

Posted - 2009-02-03 : 12:41:40

This returns the correct results but I need it to return all the columns from the search_data table. Right now it is only returning the batch_id from the batch table. When I add the search_data columns to the select I get the wrong results. Any way to actually do this?

select distinct (i.batch_id)
from search_data s,
initiator i,
batch b
where s.initiator_id = i.initiator_id
and i.batch_id = b.batch_id
and b.interface_id >= 5000
and b.create_dt = ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 12:45:29

when you return distinct batch_id from initiator, you can only return a single resultset from search_data along with it. in that case which record you want to return? first,recent or random?

zemantm
Starting Member

8 Posts

Posted - 2009-02-03 : 12:47:09

quote:
Originally posted by visakh16

when you return distinct batch_id from initiator, you can only return a single resultset from search_data along with it. in that case which record you want to return? first,recent or random?

Random will be fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 12:52:16

[code]
select i.batch_id,s.*
from initiator i
join (select s1.*
from search_data s1
inner join (select min(pk) as minid,initiator_id
from search_data
group by initiator_id )s2
on s1.initiator_id =s2.initiator_id
and s1.pk=s2.minid
)s
on s.initiator_id = i.initiator_id
join batch b
on i.batch_id = b.batch_id
where b.interface_id >= 5000
and b.create_dt = ?
[/code]
pk is primary key of search_data table

zemantm
Starting Member

8 Posts

Posted - 2009-02-03 : 12:59:07

quote:

Originally posted by visakh16


select i.batch_id,s.*
from initiator i
join (select  s1.*
     from search_data s1
     inner join (select min(pk) as minid,initiator_id 
                 from search_data
                 group by initiator_id )s2
     on s1.initiator_id =s2.initiator_id 
     and s1.pk=s2.minid
   )s
on s.initiator_id = i.initiator_id
join batch b
on i.batch_id = b.batch_id
where b.interface_id >= 5000
and b.create_dt = ?

pk is primary key of search_data table

initiator_id is also the primary key of the search_data table

zemantm
Starting Member

8 Posts

Posted - 2009-02-03 : 13:01:31

quote:

Originally posted by visakh16


select i.batch_id,s.*
from initiator i
join (select  s1.*
     from search_data s1
     inner join (select min(pk) as minid,initiator_id 
                 from search_data
                 group by initiator_id )s2
     on s1.initiator_id =s2.initiator_id 
     and s1.pk=s2.minid
   )s
on s.initiator_id = i.initiator_id
join batch b
on i.batch_id = b.batch_id
where b.interface_id >= 5000
and b.create_dt = ?

pk is primary key of search_data table

When I put initiator_id in for pk and run the query I'm still getting duplicate batch_id's I need the results to display records based on unique batch_id's.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 13:05:26

nope if initiator_id is pk of searchdata then you wont get duplicates at all. is there any other columns that form part of pk?

zemantm
Starting Member

8 Posts

Posted - 2009-02-03 : 13:44:47

quote:
Originally posted by visakh16

nope if initiator_id is pk of searchdata then you wont get duplicates at all. is there any other columns that form part of pk?

No other PK's. Ok, I'm not getting duplicates but I'm not getting the unique records I want. For example I'm still get two records with the same batch_id. I only want one record for each unique batch_id.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 13:53:12

do you mean this?


select i.batch_id,s.*
from search_data s
join (select  i1.batch_id,i1.initiator_id
     from initiator  i1
     inner join (select min(initiator_id) as minid,batch_id 
                 from initiator
                 group by batch_id)i2
     on i1.batch_id =i2.batch_id 
     and i1.initiator_id =i2.minid
   )i
on s.initiator_id = i.initiator_id
join batch b
on i.batch_id = b.batch_id
where b.interface_id >= 5000
and b.create_dt = ?

zemantm
Starting Member

8 Posts

Posted - 2009-02-03 : 14:06:32

quote:

Originally posted by visakh16

do you mean this?


select i.batch_id,s.*
from search_data s
join (select  i1.batch_id,i1.initiator_id
     from initiator  i1
     inner join (select min(initiator_id) as minid,batch_id 
                 from initiator
                 group by batch_id)i2
     on i1.batch_id =i2.batch_id 
     and i1.initiator_id =i2.minid
   )i
on s.initiator_id = i.initiator_id
join batch b
on i.batch_id = b.batch_id
where b.interface_id >= 5000
and b.create_dt = ?

zemantm
Starting Member

8 Posts

Posted - 2009-02-03 : 14:06:58

quote:

Originally posted by visakh16

do you mean this?


select i.batch_id,s.*
from search_data s
join (select  i1.batch_id,i1.initiator_id
     from initiator  i1
     inner join (select min(initiator_id) as minid,batch_id 
                 from initiator
                 group by batch_id)i2
     on i1.batch_id =i2.batch_id 
     and i1.initiator_id =i2.minid
   )i
on s.initiator_id = i.initiator_id
join batch b
on i.batch_id = b.batch_id
where b.interface_id >= 5000
and b.create_dt = ?

That's it...thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 02:56:01

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources