I am trying to make a SQL view of 2 different tables with some identical fields and some unique fields.
With both tables having their userId as their PK
Table Male
-------- ------- ------- --------- ------------
| UserId | Fname | Lname | Contact | DOB |
-------- ------- ------- --------- ------------
| abcd1 | Jack | Smith | 1234555 | 12/12/1954 |
| abcd2 | John | White | 1234333 | 01/09/1960 |
| abcd3 | David | King | 1234444 | 23/01/1973 |
-------- ------- ------- --------- ------------
Table Female
-------- ------- ------- ------------------- ------------
| UserId | Fname | Lname | Email | Age |
-------- ------- ------- ------------------- ------------
| abcd1 | Sally | Smith | sally@example.com | 40 |
| abcd2 | Lily | White | lily@example.com | 35 |
| abcd3 | Susan | King | susan@example.com | 55 |
-------- ------- ------- ------------------- ------------
I would like to create a view of this 2 tables that returns my query with these 2 criteria:
SELECT * FROM view_members WHERE UserId = 'abcd1';
-------------- ------- ------- --------- ------------ ------------------- ------------
| UserId | Fname | Lname | Contact | DOB | Email | Age |
-------------- ------- ------- --------- ------------ ------------------- ------------
| male_abcd1 | Jack | Smith | 1234555 | 12/12/1954 | NULL | NULL |
-------------- ------- ------- --------- ------------ ------------------- ------------
| female_abcd1 | Sally | Smith | NULL | NULL | sally@example.com | 40 |
-------------- ------- ------- --------- ------------ ------------------- ------------
The primary key of UserId from the two tables will remain the identifier, and the value returned will be appended with the respective table names. As for values that are unique to each table will return null for results drawn from the other tables.
Are there any possible solution for this? Any logic to be added into my Create View statement to make this work?
CodePudding user response：
Use a union approach:
SELECT CONCAT('male_', UserId) AS UserId, Fname, Lname, Contact, DOB, NULL AS Email, Age
FROM Male
UNION ALL
SELECT CONCAT('female_', UserId), Fname, Lname, NULL, NULL, Email, Age
FROM Female;
Note that we are using NULL placeholders in the select statements when a particular column not exist within a certain table. For example, in the first select we use NULL as a placeholder for the email, because the Male table does not have this columnn.
CodePudding user response：
You can't have a view where you redefine the values of a column only after querying it. You can define the view as a union. Just hardwire a gender value.
CREATE VIEW your_view AS
SELECT UserId, Gender AS 'Male', Fname, Lname, Contact, DOB, NULL AS Email, Age FROM Male
UNION ALL
SELECT UserId, Gender AS 'Female', Fname, Lname, NULL, NULL, Email, Age FROM Female;
Now you can get both male and female rows from a SELECT statement, or get just the female or male version by adding Gender criteria.
Assume a male and female row with the same ID as in your example:
SELECT * FROM your_view WHERE UserId = 'abcd1' AND Gender = 'Male'
Leave out Gender if you desire both male and female rows.