i need your help to achive this guys.

Question

2.50/5 (3 votes)

See more:

i have two tables..
1- student_details

sid sname sphonenum
1 x 123
2 y 245
3 z 985

2-book_details

bid bname bauthor

12 DAA XYZ
23 FLAT BHG

now i have created a lend table which stores lendign details where sid and bid are foreign key
3- lend_details

lid sid bid lend_time
214 1 23 xx:xx:xx

my question is how can i retrieve the following details

lid sid sname bid bname bauthor lend_time
214 1 x 23 FLAT BHG XX:XX:XX

hope im clear now..im new to sql and trying to create library management database ..
thanks in advace

Posted 20-Dec-13 3:57am

Member 10479174

Updated 20-Dec-13 4:14am

v4

Add a Solution

Comments

Manfred Rudolf Bihy 20-Dec-13 10:04am

Show what you have tried and where exactly you are stuck.

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)

OriginalGriff · Accepted Answer · 2013-12-20T04:07:00

Solution 1

Try:

SQL

SELECT s.Sid, s.StudentName, b.BID, b.BookName, l.LendTime 
FROM LEND_DETAILS l
JOIN STUDENT_DETAILS s ON l.SIS = s.SID
JOIN BOOK_DETAILS b ON l.BID=b.BID

Posted 20-Dec-13 4:07am

OriginalGriff

Comments

Member 10479174 20-Dec-13 10:39am

thanks a lot for the quick response @orginalgriff that did work exactly like i want ..thanks a lot

OriginalGriff 20-Dec-13 10:55am

You're welcome!

Member 10479174 20-Dec-13 12:36pm

well thanks for a the help.. but want if i want to just find a single record of lending details.. e.g. i create a stored procedure and pass a Sid which will return all the book of that particular Sid . the solution that you provided will return entire table details

OriginalGriff 20-Dec-13 14:00pm

So add a WHERE clause...

S. M. Ahasan Habib · Accepted Answer · 2013-12-20T04:48:00

Solution 2

Establish inner join with all tables.
Based on common SId field. Lend_Details Table and Student_Details table will be joined.
Based on common BID Field, Book_Details tble and Lend_Detail will be joined.

SELECT
L.LId
,L.SId
,S.SName
,L.BId
,B.BName
,L.Land_Time
FROM Lend_Details L
INNER JOIN Student_Details S ON S.SId = L.SId
INNER JOIN Book_Details B ON B.BId = L.BId

Posted 20-Dec-13 4:48am

S. M. Ahasan Habib

Comments

CHill60 20-Dec-13 11:06am

No different to solution 1

S. M. Ahasan Habib 20-Dec-13 11:12am

yes! just added some description and inner word which is little readable. Though the guy said he is new in sql so i think readability might help and he might not confuse with inner/left/right join.

Member 10479174 20-Dec-13 12:34pm

well thanks for a the help.. but want if i want to just find a single record of lending details.. e.g. i create a stored procedure and pass a Sid which will return all the book of that particular Sid . the solution that you provided will return entire table details.

S. M. Ahasan Habib 20-Dec-13 16:18pm

create proc GetBooks(@SId int)
as
begin
SELECT
L.LId
,L.SId
,S.SName
,L.BId
,B.BName
,L.Land_Time
FROM Lend_Details L
INNER JOIN Student_Details S ON S.SId = L.SId
INNER JOIN Book_Details B ON B.BId = L.BId
WHERE L.SId = @SId;
end

Member 10479174 21-Dec-13 5:04am

thank you s.m.ahasan habib for your help.

S. M. Ahasan Habib 21-Dec-13 5:17am

you are most welcome!