![]() ![]() Especially if the sub-query is evaluated once or for every row! If you use sub-queries, you should be aware of how the DB-Server executes the sub-query. I hope I can provide one with this (very) late answer:įirst of all, let me say the most important: There are different forms of sub-queriesĪnd the second important statement: Size matters I've read multiple answers here some stated subqueries are faster, but it lacked a good explanation. But that approach also relies on behavior that is not guaranteed, as documented in the MySQL Reference Manual.In the year 2010 I would have joined the author of this questions and would have strongly voted for JOIN, but with much more experience (especially in MySQL) I can state: Yes subqueries can be better. Using user-defined variables, we might get better performance with large sets. There aren't any ties for top salesperson.) With that approach, it's easier to get just one "top salesperson" per store (when the specification is that there will always be a "tie breaker". The other approach I mentioned is to make use of user-defined variables. If we want to get the manager of the shop, change the join criteria for m to match to h instead of e. The manager name being returned is the manager of the employee. To answer the question you asked, how to join those two queries together to get the specified result: I don't think it's possible out of those two queries. Putting that all together, we get something like this: SELECT h.shopname AS `storename` Now we just need to add a join to fss_shop to get the storename, and a couple of joins to fss_employee to get the salesperson name, and to get that saleperson's managerĪdd an order by clause ORDER BY h.storename, e.empnameĪnd add the appropriate expressions to the SELECT list. If there are two (or more) empnin with the same highest total sales (a tie for first place), this query returns both (or all) of those salesperson. We can take that result, and join it back to the total sales for each salesperson/store, to get the salesperson that had a matching "highest" sales for that store - get salesperson with the highest total sales for each store And then we can use that query as an inline view in another query, to get the "highest" total sales for each store: - get highest total sales for each store Review the results and verify that this is correct. In MySQL, we have a couple of options: go with a straight SQL approach, or make use of some user-defined variables to emulate analytic functions.įor the straight SQL approach, we can build the query a step at a time.įirst, we can get total sales for each salesperson from each store SELECT p.shopid And some databases give us analytic/windowing functions which makes this fairly easy. We would think this would be a simple thing to do. The original answer below is still applicable to versions of MySQL before 8.0. See what some of my tables look like at this link. ![]() (SELECT MAX(sales) FROM (SELECT shopid, SUM(amount) AS sales (SELECT s.shopname, e.empname FROM fss_Shop s I tried to do it like below: SELECT * FROM ![]() Now I need to join the results of the two queries. JOIN fss_Employee e ON e.shopid = s.shopid AND e.mgrnin="" Get All Managers and their store SELECT s.shopname, e.empname FROM fss_Shop s SELECT MAX(sales) FROM (SELECT shopid, SUM(amount) AS salesįROM fss_Payment GROUP BY empnin) AS salesdata GROUP BY shopid Both these queries work but I just need help in joining them together. I have managed to create the two following queries. I am trying to create a query that will give me the: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |