Supplier who has supplied maximum number of books

Question

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 23-May-13 20:39pm

jmohanraj2008

Updated 23-May-13 20:49pm

Aarti Meswania

v2

Add a Solution

Comments

Samresh.ss 24-May-13 2:59am

Make an inner join on test1.suid and test2.suid

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gianmaria Gregori · Answer 1 · 2013-05-23T21:20:00

Solution 1

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'

Posted 23-May-13 21:20pm

Gianmaria Gregori

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.

gvprabu · Answer 2 · 2013-05-23T23:45:00

Solution 2

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

Posted 23-May-13 23:45pm

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