How to write join query on 3 tables

Question

3.00/5 (1 vote)

See more:

SQL-Server-2005

Hi,

This question was asked to me in interview.

Tables:
Student: SID,SNMAE
Book: BID,BNAME,BPRICE,SID
Author: AID,ANAME,BID

Question:
Write query which give list of student who take books who's price is >1000 & Book having more than one Author.

Please help me to write above mentioned query.

Posted 17-Dec-13 17:44pm

Yogi ,Pune

Updated 17-Dec-13 17:45pm

v2

Add a Solution

Comments

joginder-banger 17-Dec-13 23:54pm

I hope you used primary key and Foreign key concept. if you are not used..before start the query used the key concept.other wise duplicate value showing your result.

joginder-banger 17-Dec-13 23:54pm

hit to reply

Yogi ,Pune 17-Dec-13 23:59pm

Yes I used Primary key & Foreign key concept.

joginder-banger 18-Dec-13 0:02am

check it solution...

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 18-Dec-13 0:23am

What have you tried and where is the problem?

3 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)

LaxmikantYadav · Answer 1 · 2013-12-17T18:03:00

Solution 2

SQL

SELECT S.SNAME
FROM Student as S
INNER JOIN Book as B
  ON S.SID=B.SID
INNER JOIN Author as A
  ON B.BID=A.BID
WHERE B.BPRICE>1000
GROUP BY A.BID,S.SNAME
HAVING COUNT(A.BID)>1

Posted 17-Dec-13 18:03pm

LaxmikantYadav

Updated 25-Dec-13 19:04pm

v2

Comments

Tom Marvolo Riddle 18-Dec-13 0:49am

5!

Yogi ,Pune 20-Dec-13 3:56am

When I write below mentioned code

Select * From Student_Master
Inner Join Book_Master
On Student_Master.SID=Book_Master.SID
Inner Join Author_Master
On Book_Master.BID=Author_Master.BID
Where Book_Master.BPRICE>1000
Group By Author_Master.BID
Having COUNT(Author_Master.BID)>1

SQL throws en error :
Msg 8120, Level 16, State 1, Line 1
Column 'Student_Master.SID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Tables:
Student: SID,SNMAE
Book: BID,BNAME,BPRICE,SID
Author: AID,ANAME,BID

As I mentioned above I want to write query which give list of student who take books who's price is >1000 & Book having more than one Author.

LaxmikantYadav 26-Dec-13 1:05am

I have updated query now, please check and let me know.

Peter Leow · Answer 2 · 2013-12-25T20:48:00

Solution 3

SQL

SELECT DISTINCT(s1.SNAME) FROM Student s1 JOIN BOOK b1 ON
s1.SID = b1.SID WHERE b1.BPRICE > 1000 AND EXISTS
(
SELECT COUNT(a.AID) FROM Book b2 JOIN Author a ON
b2.BID = a.BID WHERE s1.SID = b2.SID GROUP BY a.AID HAVING COUNT(a.AID) > 1
)

Posted 25-Dec-13 20:48pm

Peter Leow

joginder-banger · Answer 3 · 2013-12-17T18:01:00

Solution 1

i think after you reading this some idea your problem.

C#

select * from Student st
inner join Book id on st.SID =id.sid
inner join Author at on at.bid=st.bid

for any query hit to reply.

Posted 17-Dec-13 18:01pm

joginder-banger

Comments

TrushnaK 18-Dec-13 0:11am

where is your condition
price is >1000 & Book having more than one Author.

joginder-banger 18-Dec-13 0:14am

not a complete script ....i have all ready mention some idea...not a complete solution.

Yogi ,Pune 23-Dec-13 4:55am

I am facing problem how to write statement which give book having multiple author

Yogi ,Pune 23-Dec-13 4:54am

Where Book_Master.BPRICE>1000
Group By Author_Master.BID
Having COUNT(Author_Master.BID)>1

TrushnaK 23-Dec-13 5:29am

which problem you faceing.

Yogi ,Pune 24-Dec-13 0:01am

Can u tell me query who retrieve list of books having more than one author.

If i get this query i will try it in join query.