Join Query on 2 tables

Question

1.00/5 (1 vote)

See more:

CSS

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

Question:
Write query which give list of Books, having more than one Author.

This question was asked to me in interview.

Posted 23-Dec-13 18:53pm

Yogi ,Pune

Add a Solution

1 solution

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)

Christian Graus · Answer 1 · 2013-12-23T19:27:00

Solution 1

If you didn't know, then you didn't deserve the job.

Can you see a column that both tables have in column ? It's BID. So the sql is

SQL

select b.bname, a.aname from book b inner join author a on b.bid = a.bid

to get a list of all authors and books. However, what you want, is books having more than one author. So the answer is:

SQL

select a.aname, count(b.bname) from book b inner join author a on b.bid = a.bid group by a.aname having count(b.bname) > 1

Posted 23-Dec-13 19:27pm

Christian Graus

v2

Comments

Yogi ,Pune 24-Dec-13 1:44am

it is not that i don't know, I was already tried below mentioned query
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

for above query system throws an error:"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."

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

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

I have also tried query given by you but system showing blank result but there is records in database , having more than one author to books

Christian Graus 24-Dec-13 1:47am

The error means what it says. Every non aggregate column you select, needs to be in the group by list. Just copy all your column names, comma separated, and it will work.

Yogi ,Pune 24-Dec-13 1:50am

I used Select * in my query.

Christian Graus 24-Dec-13 1:52am

Well, that was dumb. Then you need to list ALL the columns, and if more are added, your query will break.

Yogi ,Pune 24-Dec-13 1:58am

I have also tried it,i.e instead of * putting column names still same error persist.

Christian Graus 24-Dec-13 1:58am

The error means what it says. SQL always works the same. The error means a column is in your select and not an aggregate ( like sum, avg, count ), and not in your group by statement. That's what it means, that's what the issue is.

Maciej Los 24-Dec-13 2:12am

If you didn't know, then you didn't deserve the job.
Well said!
+5!