SteveK SteveK - 15 days ago 6
MySQL Question

A better or more efficient version of this SQL query?

I'm working on learning SQL queries, but I haven't really figured out what's more efficient.

This is my code:

SELECT A.CustName, A.CustID, C.RestName,
CONCAT(A.Address, ' - ', C.Address, ' + ', C.FoodTime, ' = ', A.Address - C.Address + C.FoodTime)
AS DeliveryTime, B.OrderID
FROM (SELECT * FROM Customers) AS A
LEFT JOIN (SELECT * FROM Orders) AS B
ON A.CustID = B.CustID
LEFT JOIN (SELECT * FROM Restaurants) AS C
ON B.RestID = C.RestID
ORDER BY A.CustID;


There are three tables,
Customers
,
Restaurants
, and
Orders
. Customers contains CustID, CustName and Address. Restaurants contains RestID, RestName, Address and FoodTime. Orders contains CustID, RestID and OrderID.

I believe this is an exercise in dimensions and facts, but I'm not 100% about the proper way for these to interact in terms of tables. If anyone has a good explanation for that, I'm all ears (eyes). Thanks!

Answer

If you actually need information on all the customers for all orders in all restaurants, then your query will "work". As already pointed out (select * from sometable) and (sometable), with or without brackets, are the same thing, and most people would just put the table name.

The first part of the concat will show you the address and foodtime info. The second part where you try to subtract an address from another address and then add a time, is probably going to get you an error, depending on the database used (if your database interprets "+" as a concatenation operator, and the addresses can actually be subtracted, then you might get a valid string, but it seems unlikely).

Thirdly, as a matter of style, giving aliases a,b and c to your tables is just likely to confuse you. One character aliases are OK, but in this case whey wouldn't you use r for restaurant, o for orders and c for customers?