How to access data using a SQL query that references multiple tables?

Question

I have a few tables and inserted values into them using SQL plus:

INSERT INTO Location(name, addr, phone) 
VALUES ('Texas Branch', '4832 Deercove Drive, Dallas, TX 75208', '214-948-7102');

INSERT INTO Librarian(eid, ID, pay, Loc_name) 
VALUES (2591051, 88564, 30000.00, 'Texas Branch');

INSERT INTO Stored_In(media_id, name) 
VALUES (8733, 'Texas Branch');

Suppose I write a SQL query like select location = "texas branch", then the output should contain the librarian for the Texas branch and media_id, the name stored_in - in simple words, I should get all the information related to Texas branch)

Can anyone please help write the SQL query?

Lookup on Joins. Also you can add a Identity column to Location table and refer it in Librarian and Stored_In table — Pரதீப்
– Pரதீப், Commented Aug 7, 2015 at 3:10

Zwander · Accepted Answer · 2015-08-07 03:23:42Z

1

Something along the lines of:

SELECT * FROM (
    Location
    INNER JOIN 
    Librarian ON Location.name = Librarian.Loc_name
    INNER JOIN 
    Stored_In ON Stored_In.name = Location.name
)
WHERE(
    Location.name='Texas Branch'
)

Because of the SELECT * you will get a bunch of duplicate fields and you should replace this with the proper select.

As others have said, Google JOINs and understand them.

answered Aug 7, 2015 at 3:23

Zwander

3822 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SezMe · Accepted Answer · 2015-08-07 03:18:39Z

0

You need to use joins then the SQL is straightforward. If that is unclear then maybe you need to do some homework.

answered Aug 7, 2015 at 3:18

SezMe

5378 silver badges27 bronze badges

1 Comment

SezMe Over a year ago

My concern, Drew, is that nadh is obviously trying to write an application that uses data from a database without - apparently - knowledge of joins. This is a situation fraught with problems, among which is the lack of knowledge of relational database design and table relations. In that light, writing his SQL for him/her is simply putting off a learning experience that needs to come before the writing of an application. YMMV.

Collectives™ on Stack Overflow

How to access data using a SQL query that references multiple tables?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related