-2

I have a mysql database with the following tables.

Excursion Table

Excursion ID    |  Port ID   |    Excursion Name

Bookings Table

Booking ID  |  User ID  |  Excursion ID  |  Number of People  | Event Date

I want to be able to create a view, like the diagram below when given a certain UserID from the bookings table.

Booking ID   |  Excursion Name  |  Excursion  ID    |  Number of People
DineshDB
  • 5,998
  • 7
  • 33
  • 49
Edmund
  • 63
  • 8
  • 1
    First create a working select statement then create the view https://dev.mysql.com/doc/refman/8.0/en/views.html and if you get into trouble post what you have tried here. – P.Salmon Apr 24 '18 at 11:30
  • 1
    Copy the table structure into the question. If you do not do that someone will vote for close your question. – Victor Apr 24 '18 at 11:31
  • Possible duplicate of [How do I create a view in MySQL?](https://stackoverflow.com/questions/5185940/how-do-i-create-a-view-in-mysql) – RubioRic Apr 24 '18 at 11:32
  • @RubioRic I can create a view, making this particular one is the problem – Edmund Apr 24 '18 at 11:36
  • @Victor okay, just did it – Edmund Apr 24 '18 at 11:39
  • Then create the view defining in the select a join of the two tables on Excursion ID selecting the desired fields, including UserId. Once you got the view, you can query it filtering by the desired UserId. – RubioRic Apr 24 '18 at 11:41

2 Answers2

0
CREATE VIEW `yourDB`.`yourViewName` AS
SELECT 
    `a`.`BookingID` AS `BookingID`,
    `b`.`ExcursionName` AS `ExcursionName`,
    `b`.`ExcursionID` AS `ExcursionID`,
    `a`.`NumberOfPeople` AS `NumberOfPeople`
FROM
    (`yourDB`.`BookingsTable` `a`
    JOIN `yourDB`.`ExcursionsTable` `b`)
WHERE
    (`a`.`UserID` = 'YourValue')

Hope this solves your issue.

Vignesh
  • 686
  • 6
  • 10
0

Simply try this:

CREATE VIEW Your_View_Name AS
SELECT BT.Booking_ID, ET.Excursion_Name, ET.Excursion_ID,BT.Number_of_People
FROM Excursion_Table ET
JOIN Bookings_Table BT ON ET.Excursion_ID = BT.Excursion_ID
WHERE BT.User ID = ????;
DineshDB
  • 5,998
  • 7
  • 33
  • 49