CODEWITHSUNDEEP
databasejoinleft-join
orokusaki
orokusaki

Reputation: 57118

SQL Query question

No particular DBMS in mind, how would I do the following:

# There are many tables per one restaurant, many napkins per one table
# Pseudo SQL

SELECT RESTAURANT WHERE ID = X;
SELECT ALL TABLES WHERE RESTAURANT_ID = RESTAURANT.ID;
SELECT ALL NAPKINS WHERE TABLE_ID = TABLE.ID;

But, all in one query? I've used a JOIN to get all the tables in the same query as restaurant, but is it possible to get all napkins for each table as well, in the same query?

Upvotes: 0

Views: 252

Answers (3)

fredt
fredt

Reputation: 24352

It seems you want to return three separate results, not a single result with repeat values for RESTAURANT_N or TABLE_N.

In SQL, this is done with stored procedures which can return multiple result sets. The syntax for stored procedures varies among database products, therefore you should ask the question for specific products. In the stored procedure, there will be three select statements for the RESTAURANTS, TABLES and NAPKINS. The results of the three statements are returned in a bundle to the application, which can then use the results.

Upvotes: 0

Rez.Net
Rez.Net

Reputation: 1462

You would definitely end up in repeating Tables and restaurant information on the rows, like:

Restaurant1 Table1 Napkin1
Restaurant1 Table1 Napkin2
Restaurant1 Table1 Napkin3
Restaurant1 Table2 Napkin4
Restaurant2 Table1 Napkin5

Upvotes: 0

Jon
Jon

Reputation: 16728

select * -- replace * with the columns you need...
from restaurant as r 
    inner join tables as t on t.restaurant_id = r.id
    inner join napkins as n on n.table_id = t.id
where r.id = [restaurant id]

Upvotes: 3

Related Questions