May 17, 2007 at 1:24 am
Hi
I have two tables:
CREATE TABLE Tab1 (ID int,Name varchar (50))
CREATE TABLE Tab2 (ID_FK int,Company varchar (50))
ID from Tab1 is the Primary Key and ID_FK from the Tab2 is Foreign key for Tab1.ID. Values Inserted are:
INSERT Into Tab1 Values(1,'Ajit')
INSERT Into Tab1 Values(2,'Rakesh')
Insert Into Tab2 Values(1,'Wipro')
Insert Into Tab2 Values(1,'Infosys')
Insert Into Tab2 Values(1,'TCS')
Insert Into Tab2 Values(1,'Satyam')
Insert Into Tab2 Values(2,'Accenture')
Insert Into Tab2 Values(2,'Pegasus')
Insert Into Tab2 Values(2,'Lucent')
Insert Into Tab2 Values(2,'Convergys')
Now i want to display record from each Primary key ID, there should be only two linked Foreign key record. Ex:
Name Company
Ajit Wipro
Ajit Infosys
Rakesh Accenture
Rakesh Pegasus
If i'm executing INNER JOIN , it'll return all the linked record. So plz suggest me how to return two records only from the Tab2 Table.
Thanks
May 17, 2007 at 2:57 am
Which two do you want?
This will get you first 2 alphabetically. I assume you are using SQL 2005.
SELECT
Name, Company FROM
(SELECT Tab1.Name, Tab2.company, Row_number() OVER (Partition by Name ORDER BY Company) AS RowNo FROM Tab1 inner join Tab2 on Tab1.ID = Tab2.ID_FK) subquery
WHERE RowNo<=2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2007 at 3:03 am
Hi, I'm using SQL 2000. I want any two row for each Primary key.
Thanks for your effort.
May 17, 2007 at 3:29 am
If you're using 2000, please rather post in the 2000 forums in the future. The row_number function is SQL 2005 only. Since the query was in the 2005 forum, I assumed you were using 2005
In 2000 it's a lot more difficult to do this.
You didn't answer my question. Which two rows do you want? Any two? alphabetically?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2007 at 3:33 am
'O' sorry. Normally i worked in SQL 2005 so wrongly i put in the 2K5 forum. For this query, i need for SQL 2000.
I need two row order by company name. Your last sent query worked in 2005. so i need in the same way.
Thanks
May 17, 2007 at 3:36 am
This works in a fairly unpleasant sort of way:
declare @id int
select @id = min(id)
from #tab1
while @id is not null
begin
insert into @tab
select top 2 a.Name,b.Company
from #tab1 a join #tab2 b on a.id = b.id_fk
where a.id = @id
select @id = min(id)
from #tab1
where id > @id
end
select * from @tab
order by name
May 17, 2007 at 3:47 am
Hi
Works fine but i need the same in a single query as what u did for 2K5. If it can be done like that, will be highly appreciable. Actually i have to use in a large application. What i given u, is a small reference of that. PLz if u can
Thanks & Regards
May 17, 2007 at 3:59 am
This will probably work, but it is not nice. Untested. Give it a try, if it throws errors, let me know
SELECT tab1.name, min(tab2.company)
from tab1 inner join tab2 on tab1.id = tab2.id_fk
group by tab1.name
UNION ALL
SELECT tab1.name, min(tab2.company)
from tab1 inner join tab2 on tab1.id = tab2.id_fk
WHERE tab2.company != (select min(company) from tab2 t where t.id_fk = Tab1.id)
group by tab1.name
Order by 1,2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2007 at 4:10 am
Perfect. Great man.
Thanks. Take care
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply