-- An INNER JOIN returns only the rows where there is a match between the two tables based on the join condition


CREATE TABLE Guest ( 
    GuestCardNumber VARCHAR(20) PRIMARY KEY, 
    Name VARCHAR(20), 
    CheckInTime DATETIME, 
    VIPStatus INT CHECK (VIPStatus IN (0,1))
    );

CREATE TABLE GuestLog (
    LogID INT PRIMARY KEY, 
    GuestCardNumber VARCHAR(20), 
    RoomAccessed VARCHAR(20), 
    AccessTime DATETIME
-- FOREIGN KEY (GuestCardNumber) REFERENCES Guest(GuestCardNumber)
); 

-- ERROR 1216 (23000) at line 28: Cannot add or update a child row: a foreign key constraint fails, is not caused by a join.
-- Instead it happens when you're trying to insert or update a row in a child table (GuestLog) that references a parent table (Guest) via a foreign key 
-- But the referenced value does not exist in the parent table. 

INSERT INTO Guest (GuestCardNumber, Name, CheckInTime, VIPStatus) VALUES 
    ('G0001','Emily Carter', '2025-10-01 14:30:00', 1), 
    ('G0002', 'James Bennett', '2025-10-01 15:00:00', 0), 
    ('G0003', 'Sophia Wright', '2025-10-01 16:15:00', 1), 
    ('G0004', 'Daniel Foster', '2025-10-01 17:45:00', 0), 
    ('G0005', 'Olivia Turner', '2025-10-01 18:20:00', 1),
    ('G0010', 'John Reese', '2025-10-01 17:23:00', 0);


INSERT INTO GuestLog(LogID, GuestCardNumber, RoomAccessed, AccessTime) VALUES 
    (1, 'G00010', 'VIP Bar Lounge', '2025-10-01 14:45:00'), 
    (2, 'G0001', 'Casino', '2025-10-01 15:10:00'), 
    (3, 'G0002', 'Casino',     '2025-10-01 16:30:00'), 
    (4, 'G0003', 'Library', '2025-10-01 15:30:00'), 
    (5, 'G0004', 'Sensory Room', '2025-10-01 18:00:00'); 


-- INNER JOIN NOTES 

-- An inner join returns only the rows where there is a match between two tables based on the join condition.
-- Need to percise on what type of join you are doing, if you are doing an inner join, write inner join. If you are doing a left join write left join. 

-- Working left, right,left, right is going to make it easier for you to answer some questions. For the inner join, most of the time, we may not see why
-- it's important to keep everything left right. 

-- But when we get to the left join or the right join, those are defiently things that we want to keep in mind. 

SELECT Guest.Name, GuestLog.RoomAccessed 
-- Guest.Name(Left Table) GuestLog.RoomAccessed (Right Table)
    
FROM Guest -- (Left Table)
    
INNER JOIN GuestLog ON GuestLog.GuestCardNumber;  -- ON is the join the condition
-- GuestLog (Right Table) ON Guest.GuestCardNumber --(Left Table)

    
-- How do I look at which one is my left Join or which one is right join table. 
-- Always read the scenerio left, right, left, right what do I mean by that? 

-- Ex: We want to list the names of the guests (Left) and the last room they accessed (right)

-- So guest name (left table) is going to go first, then were going to have the room accessed from the gues log which is going to go second. 


-- NOTE: 
-- So when we have questions that are asking us to return only matched informations, we are going to do an INNER JOIN 
-- if that question does not have a stipulation (doesn't have a but) , we are also going to user the inner join becuause that's the default type of join 

-- Inner Join does not accept unmatched rows, only matched information. 

--

-- LEFT JOIN NOTES 
-- A Left Join returns all rows from the left table(Guest Table), along with the matched rows from the right table(GuestLog). If not match exists,
-- NULL values are returned for columns from the right table. 


-- We want to list the names of all guest and the last they accessed (if available), if a guest does not have a record in the GuestLog table, the RoomAccessed 
-- Column will show NULL. 


SELECT Guest.Name, GuestLog.RoomAccessed -- I want the room access if available
    
-- Guest.Name(left) GuestLog(Right)
    
FROM Guest -- (left) -- You want all of the guests, so that is why we want to do a left join, left table is the primary table
    
LEFT JOIN GuestLog ON Guest.GuestCardNumber = GuestLog.GuestCardNumber; 

-- GuestLog (Right) Guest.GuestCardNumber(Left) GuestLog.GuestCardNumber(Right)


--

-- RIGHT JOIN NOTES

-- A Right JOIN returns all rows form the right table(GuestLog Table), along with the matched rows from the left table(Guest Table)
-- If no match exists, NULL Values are returned for columns from the left table. 

-- We want to list the names of all the last accessed rooms along with who accessed them (if available)
    -- Right Join is one we don't tend to code with, because most of the time what you do with a right join you can just do them with a left join. 

SELECT Guest.Name, GuestLog.RoomAccessed 
-- Guest.Name(Left) GuestLog(Right)

FROM Guest -- left

RIGHT JOIN GuestLog ON Guest.GuestCardNumber = GuestLog.GuestCardNumber; 
-- GuestLog (Right) Guest.GuestCardNumber(Left) GuestLog(Right)

-- 

-- FULL JOIN NOTES 

-- A FULL JOIN (or FULL OUTER JOIN) returns all rows when there is a match in one of the tables.
-- If there is no match, the result will contain NULL values for non-matching rows from both tables 

-- OUTER JOIN is a join that accpets unmatched rows from one or both tables
-- LEFT AND RIGHT JOIN are also outer JOIN. 

-- FULL JOIN accepts unmatched rows from both tables. 

-- NOTE: MySQL doesn't support the FULL OUTER JOIN directly. However, you can achieve the same result by combining a LEFT JOIN and RIGHT JOIN 
-- and using UNION. 

-- We want to list all guests and all rooms, whether they are linked or not. 

-- LEFT JOIN: all guests, even if they never accessed a room
SELECT Guest.Name, GuestLog.RoomAccessed
FROM Guest
LEFT JOIN GuestLog ON Guest.GuestCardNumber = GuestLog.GuestCardNumber

UNION

-- RIGHT JOIN: all room accesses, even if the guest is missing
SELECT Guest.Name, GuestLog.RoomAccessed
FROM Guest
RIGHT JOIN GuestLog ON Guest.GuestCardNumber = GuestLog.GuestCardNumber;


-- RECAP 

-- Common Joins 

-- INNER JOIN 
-- Returns only the rows where there is a match in both tables (Guest with a room access log)


-- LEFT JOIN 
-- Returns all rows from the left table (Guest) and matched rows from the right table (Guest Log). Non-matching rows in the right table are NULL. 

-- RIGHT JOIN 
-- Returns all rows from the right table (GuestLog) and matched rows from the left table (Guest). Non-matching rows in the left table are NULL. 

-- FULL JOIN 
-- Returns all rows where there is a match in one of the table. Non-matching rows in eiter table are NULL. 
-- MySQL simulates this with a combination of LEFT JOIN and RIGHT JOIN using UNION. 


-- Other Joins 
-- EQUI JOIN - Returns rows where there is a match in both tables based on equality. 


-- SELF JOIN - Returns a table with itself. used to find relationships within the same table
-- Look at yourself in the mirror

-- CROSS JOIN - Returns the Cartesian product of two tables, combining each row of the first table with every row of the second table. 
-- not the same as full join. 

SELECT Guest.Name, GuestLog.RoomAccessed 
FROM Guest 
CROSS JOIN GuestLog; 

-- Since there are 5 guests and 5 logs, the result contains 5*5 = 25 rows 
-- This is a Cartesian Product, meaning every possible combination of creators and content is listed. 

-- Used to combine each rows of the first table with every row from the second one.
-- Returning to you every possible values, regardless of if they are true or not.