MySQL – query to show guests with same last_name + booking_date [closed]

I have a guest table and a booking table and I am attempting to query how to find guests who share the same last names with the same booking date.

The result should output their last_name and first_names without any duplicate results.

SELECT g1.first_name, 
       g2.first_name,
       g1.last_name, 
       b1.booking_date,
       b2.booking_date
FROM guest g1 JOIN booking b1 ON g1.id=b1.booking_id 
JOIN guest g2 JOIN booking b2 ON g2.id=b2.booking_id
WHERE g1.last_name = g2.last_name AND g1.id<>g2.id AND b1.booking_date=b2.booking_date;

When I run this, I get back the empty set.

Where am I going wrong?

The desired output should look something like this:

+-----------+------------+-------------+
| last_name | first_name | booking_date|
+-----------+------------+-------------+
| Smith     | George     |  2016-09-11 |
| Smith     |  Adam      |  2016-09-11 |
+-----------+------------+-------------+

There should be no duplicate results.

To fix SELECT

Change

g1.id<>g2.id

to

g1.id < g2.id

to avoid getting exactly 2 copies of each result row.

To follow output:

SELECT  g3.first_name, x.last_name, x.booking_date
    FROM  
    (
        SELECT  DISTINCT  g2.last_name, g2.id, b2.booking_date
            FROM  guest AS g2
            JOIN  booking b2  ON g2.id = b2.guest_id
    ) AS x
    JOIN  guest   AS g3  ON g3.last_name = x.last_name
                        AND g3.id = x.id

@RickJames created the solution. The JOIN ON clause was on the wrong field. The correct answer should be:

SELECT DISTINCT 
       g1.last_name,
       g1.first_name,
       g2.first_name,
       b2.booking_date
FROM guest g1 JOIN booking b1 ON g1.id=b1.guest_id
JOIN guest g2 JOIN booking b2 ON g2.id=b2.guest_id
WHERE g1.last_name = g2.last_name AND g1.id < g2.id AND b1.booking_date=b2.booking_date;

DISTINCT removes the duplicate result in the output.