Select Aninhado

Asked

Viewed 725 times

3

I need to develop a screen that shows the TOP 50 most accessed menus. Tell the number of accesses and which city it belongs to. Sort by number of accesses and select values for the last 30 days.

To solve the first situation I have this command:

SELECT
    menu. NAME AS Nome,
    city. NAME AS Cidade,
    count(*) AS qtdAcessos
FROM
    history.view_menu
JOIN menu ON menu.menu_id = view_menu.menu_id
JOIN address ON address.address_id = menu.address_id
JOIN city ON city.city_id = address.city_id
GROUP BY
    menu. NAME,
    city. NAME
ORDER BY
    qtdAcessos DESC
LIMIT 50

And the following command is to return the values for the last 30 days:

SELECT
    view_menu. CURRENT_DATE
FROM
    history.view_menu
WHERE
    view_menu. CURRENT_DATE BETWEEN CURRENT_DATE - 30
AND CURRENT_DATE

My question is: how to join these two commands?

sql
  • 1

    Post also the structure of the tables, please.

  • history_menu(view_manu_id current_date menu_id people_id)

  • In a quick look your Where of the last query is wrong. WHERE view_menu. CURRENT_DATE BETWEEN CURRENT_DATE - 30 AND CURRRENT_DATE

  • Something else would not only suffice for this Where in the first query?

2 answers

1

In this case the only thing you need to do is add your Where in the first sentence, in your case it would look like this

SELECT
    menu. NAME AS Nome,
    city. NAME AS Cidade,
    count(*) AS qtdAcessos
FROM
    history.view_menu
    JOIN menu ON menu.menu_id = view_menu.menu_id
    JOIN address ON address.address_id = menu.address_id
    JOIN city ON city.city_id = address.city_id
WHERE view_menu.CURRENT_DATE BETWEEN CURRENT_DATE - 30
    AND CURRENT_DATE
GROUP BY
    menu. NAME,
    city. NAME
ORDER BY
    qtdAcessos DESC
LIMIT 50

0

To view information belonging to more than one table and that is obtained by executing more than one command SELECT, we should use the clause UNION ALL, which facilitates the display of data from separate tables. 

SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2

You can use the UNION or UNION ALL. UNION will not combine lines of the other combined tables that already exist in the result of the queries applied in the previous tables. UNION ALL won’t mind that. Look at this reply the full explanation of the difference between them.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.