Mysql command for showing a single column from two table (same named column in both table) with usin-CodePudding

The database name is employee-information in that database I was trying to show one "person_name" column that is available in both tables "works" & "employee" by using two different conditions that will filter values in each table. So that I can see the values filtered by the two conditions from both tables in one single "person_name" column.

work & employee tables,

Here is what I have done so far,

USE employee_information;

SELECT employee.person_name, works.person_name 
FROM employee, works
WHERE city = "Miami" AND salary > 50000;

The result I am getting,

For that command I am getting this two-column from both table. Conditions are working but values are repetitive and there are two columns but I need to show one column filled with the value from both tables where those two conditions are true

My desired result is,

person_name//table name
Alex      //values those are true by both condition in each table 
Robin Hood

CodePudding user response：

You need to join the tables using person_name as the relationship.

SELECT employee.person_name
FROM employee 
JOIN works ON employee.person_name = works.person_name
WHERE employee.city = 'Miami' AND works.salary < 50000;

CodePudding user response：

In your case you can use JOIN , here is example

SELECT w.*,e.company_name,e.salary FROM works w INNER JOIN employee e ON e.person_name = w.person_name WHERE city = "Miami" AND salary > 50000;

CodePudding user response：

you must add a primary key in "employee table" with name id and a foreign key in "work table" with name employee_id

and your query will be

SELECT employee.person_name
FROM employee
WHERE employee.id, works.employee_id
and city = "Miami" AND salary > 50000;