Improve SQL query to show results

Question

Improve SQL query to show results

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I have two tables which one by means of RIGHT JOIN and the sentence is as follows:

$sql= "select * from bookings RIGHT JOIN calendar ON bookings.date=calendar.day_Date GROUP by calendar.day_date";

I add an image with the structure and the view to examine the table to better express the idea:

I need the results to bring all the rows that have the same date, it only brings me from the table bookings the first one that finds if there is more than one (as shown in the image ... shows the line that is in red, but I need in this case to bring row 1 and 2 of bookings and show them to me, since both lines have the same date).

mysql sql mariadb

asked by Joe 13.04.2017 в 21:16

source

3 answers

Error in Dropdown Bootstrap [closed] How to communicate with the WebSockets service

score 0 · Answer 1

I think that to get the results you want you should use the WHERE clause as in the following example:

$sql= "SELECT * 
       FROM bookings b 
       RIGHT JOIN calendar c 
               ON b.date = c.date 
       WHERE c.calendar_ID = 1";

By the way, the designs are not good. I still do not understand what is the meaning of the column date in the table calendar , when there are functions such as DAYWEEK to get the day of the week of a date.

score 0 · Answer 2

If I'm not confused, what you really want to do is an inner join (the right join will also include the mismatches as null)

You can check it yourself, this sentence should return what I understand you want (with null values)

Select * from bookings right join calendario on bookings.date=calendario.date

In your case, since you do not want null values, it should be

Select * from bookings inner join calendario on bookings.date=calendario.date

score 0 · Answer 3

I assume you are using RIGHT JOIN to get all the dates in the calendar table, including those that do not have related records in the bookings table. Then, the problem is the clause GROUP BY in which you indicate that you want to be grouped by the field calendar.day_date , so that only one record will be generated for each value of that field.

It seems to me that in this case, instead of grouping, what you want is to sort, so I suggest simply change GROUP BY by ORDER BY :

$sql =
    "select " .
        "* " .
    "from " .
        "bookings " .
        "RIGHT JOIN calendar ON " .
        "bookings.date=calendar.day_Date " .
    "ORDER BY " .
        "calendar.day_date";