mysqlsqlviewcreate-view

how can i make a view using the foreign key that is applies in mysql?


I am still a beginner and i searched in google ,read w3schools and couldn't get how to do it the following thing.

i want to create view from the info in cases field

these are the tables i want to add info from AS you saw these are the info inside the tables (names,depart,idcards)

the main table

this the table i want to get the data from i made all the column start with id_* as foreign key for the PK in the previous tables

note:id_case is the PK of the table, id_dep is for department,id name is for name,id_complaint


Solution

  • This is just a bunch of joins:

    CREATE VIEW viewname AS
    SELECT *
    FROM cases AS c
    JOIN names AS n ON c.id_name = n.id
    JOIN depart AS d ON c.id_dep = d.id
    JOIN complaint AS cm ON c.id_complaint = cm.id
    ...
    

    Note that if there are any column names that are the same among any of the tables, you'll need to list them explicitly in the SELECT list and assign distinct aliases to them. See MySQL JOIN tables with duplicate column names