Combining multiple SQL Queries into one statement

Question

I am looking for a way to combine multiple SQL queries to save having to keep calling the database. I have three tables, one with a clubs details, one with the club facilities and the third combines them.

Here is a simple version of the tables:

t1

id, name, city
1, curby, London
2, zoo, Birmingham

t2

id, facility
1, bar
2, function suite
3, VIP room

t3

id, club_id, facility_id
1, 2, 1
2, 2, 3
3, 1, 2
4, 2, 2

In all of the above the first columns (id) are just unique identifiers, and I am using commas to show you where each field separates. What I would like to get from the database is something like:

1, curby, London, function suite
2, zoo, Birmingham, bar, VIP room, function suite

If any one has any ideas please share :)

eaolson · Accepted Answer · 2012-01-14 23:14:46Z

1

You need to read up on JOINs. You didn't say what RDMS you're using, but this is what I'd use in Oracle:

SELECT t3.id, t2.name, t2.city, t3.facility
  FROM t3
 INNER JOIN t2 ON t2.id = t3.facility_id
 INNER JOIN t1 ON t1.id = t3.club_id

answered Jan 14, 2012 at 23:14

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

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

Comments

scott.korin · Accepted Answer · 2012-01-14 23:18:37Z

0

OK, so t1 is the club, t2 is a list of facilties, and t3 assigns a facility to a club, right?

You can use the JOIN (INNER or OUTER) syntax to get data from multiple tables at once. A OUTER JOIN is used when there can be data in the parent table (t1) but nothing in the child table (t2). An INNER JOIN is used when you only want records with data in both tables.

You also must include an "ON" clause that indicates how the two tables are joined together:

SELECT t1.id as club_id, t1.name as club_name, t2.facility as facility_name
FROM t1
INNER JOIN t2 ON
    t2.club_id=t1.id
INNER JOIN t3 ON
    t3.id=f2.facility_id

Also, I would actually change the names of the tables if I were you, to something that better represents the data held in the table.

answered Jan 14, 2012 at 23:18

scott.korin

2,6152 gold badges26 silver badges37 bronze badges

1 Comment

scott.korin Over a year ago

Although this will return multiple records (one per each club-facility combination).

onedaywhen · Accepted Answer · 2012-01-16 09:46:49Z

0

Consider renaming:

t1 to Clubs and its id to club_id;
t2 to Facilities and its id to facility_id;
t3 to ClubFacilities and dropping its needless id column.

Then you could write

SELECT Clubs.*, facility
  FROM Clubs 
       NATURAL JOIN Facilities 
       NATURAL JOIN ClubFacilities;

answered Jan 16, 2012 at 9:46

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Collectives™ on Stack Overflow

Combining multiple SQL Queries into one statement

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related