CODEWITHSUNDEEP
sql
Paul
Paul

Reputation: 2515

Combining multiple SQL Queries into one statement

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

Upvotes: 0

Views: 1264

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

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;

Upvotes: 0

scott.korin
scott.korin

Reputation: 2597

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.

Upvotes: 0

eaolson
eaolson

Reputation: 15094

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

Upvotes: 1

Related Questions