Click here to Skip to main content
15,113,726 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
hi
SQL
create table test1(bid varchar(10),bname varchar(75),suid varchar(10))
insert into test1 values('B01','Java How To Do Program','S01')
insert into test1 values('B011','c','S03')
insert into test1 values('B02','Java: The Complete Reference ','S03')
insert into test1 values('B03','Java How To Do Program','S01')
insert into test1 values('B04','Java: The Complete Reference ','S01')
insert into test1 values('B05','Java How To Do Program','S01')
insert into test1 values('B06','Java: The Complete Reference ','S03')
insert into test1 values('B07','Let Us C','S03')
insert into test1 values('B08','Let Us C','S04')
insert into test1 values('B09','oops','S03')

create table test2(suid varchar(25),sname varchar(25))
insert into test2 values('S01','A')
insert into test2 values('S02','B')
insert into test2 values('S03','C')
insert into test2 values('S04','D')
insert into test2 values('S05','E')
insert into test2 values('S06','F')

NOTE:: Dont mention sid=?? this way..

i need output like this
bid bname sname
B011 c C
B02 Java: The Complete Reference C
B06 Java: The Complete Reference C
B07 Let Us C C
B09 oops C
Posted
Updated 23-May-13 21:49pm
v2
Comments
Samresh.ss 24-May-13 2:59am
   
Make an inner join on test1.suid and test2.suid

As suggested by Samresh, a query like this should accomplish your task
SELECT [bid],[bname],[sname]
  FROM [test1] INNER JOIN [test2] ON [test1].[suid] = [test2].[suid]
  WHERE [sname] = 'C'
   
Comments
gvprabu 24-May-13 4:18am
   
see If u know the maximum books Supplied by 'C', How system will know this. If we ll hard code the Conditions then no use of SQL Query.
Samresh.ss 24-May-13 5:30am
   
The answer is an attempt to help the person out with his/her question.
gvprabu 24-May-13 5:48am
   
yes correct, But his Expectation is different right... Our Solutions should help the questioner.
Gianmaria Gregori 25-May-13 6:34am
   
I have payed little attention to the question title, but my solution output is exactly what the question answers.
Hi,

Check this....

SQL
DECLARE @MaxBookCount INT 
SELECT @MaxBookCount= (SELECT TOP 1 COUNT(bid) FROM test1 GROUP BY suid ORDER BY COUNT(bid) DESC) 

SELECT B.bid, B.bname, S.sname
FROM Test1 B
INNER JOIN Test2 S ON S.suid=B.suid
WHERE B.suid IN (SELECT suid, 
                 FROM Test1 
                 GROUP BY suid
                 HAVING COUNT(B.bid)=@MaxBookCount)

Regards,
GVPrabu
   
Comments
Arun Vasu 24-May-13 6:17am
   
have some bug in your query pls correct it.
gvprabu 24-May-13 6:18am
   
yes tel me, bcos I don't have SQL server in my machine.... so use this logic and try to fix. else send the error

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900