I am new to SQL, but I would like to borrow your wisdom.
I'd like to select an item from the employee table with the following content, but I'm struggling because I don't even know if a join is necessary. .. ..
Could you please refer to the attached image and let me know? Sorry for the rudimentary question. .. Sweat
When selecting an employee table, the cust_name of the customer table with the same employee_id is
If at least one exists in the customer_2 table and the employee_id of that record is not the same
Set "flg" of the select item to 1, and set "flg" to 0 if none of them exist, or if they exist but the employee_id is the same.
We look forward to hearing from you.
In the explanation, I wrote that the cust_name of the customer table and the customer_2 table are exactly the same.
Actually, I want to flag the partial match.
Answers from sazi
on cst1.cust_name = cst2.cust_name
on cst1.cust_name like CONCAT ('%', cst2.cust_name,'%')
I changed it to and ran it, but I still can't get the results I expected. ..
Is there any good way? .. ??
■ Sample data
I want to judge that even data like ↑ exist by partial match.
Answer # 1
-When at least one cust_name of the customer table exists in the customer_2 table and the employee_id of that record is not the same.
-If none of them exist, or if they exist but the employee_id is the same
The above is a contradictory relationship, so it is only necessary to determine whether one of the conditions is met.
Since the SELECT to be calculated is the same as the number of rows of employee, the judgment of flg is a correlation subquery.
select employee. * , case when exists (exists select 1 from customer cst1 inner join customer_2 cst2 on cst1.cust_name = cst2.cust_name and cst1.employee_id! = cst2.employee_id where cst1.employee_id = employee.employee_id ) then 1 else 0 end as flg frpm employee
Answer # 2
SELECT E.employee_id, MAX (E.emp_name) AS emp_name , MAX (CASE WHEN C2.customer_id IS NULL THEN 0 ELSE 1) AS flg FROM employee E LEFT JOIN customer C ON C.employee_id = E.employee_id LEFT JOIN customer_2 C2 ON C2.cust_name = C.cust_name AND C2.employee! = C.employee_id GROUP BY E.employee_id
something like that.
- [mysql] correspondence when group_concat is mixed in select statement to insert
- java - i want to select data with the select statement of mysql and display it on the browser
- i want to get data from two tables and display it mysql php
- mysql - select * from contents are not displayed even if the table name is displayed
- mysql - is it ant to give the same index name to multiple tables in sql?
- how to use mysql select case
- [laravel] [mysql] how to obtain other data from the obtained id
- mysql study notes full use examples of select statements
- mysql - i want to total the data extracted by select
- mysql automatic numbering for tables with many additions and deletions
- php - rollback doesn't work in mysql and only some tables are updated (wordpress)
- MySQL select, insert, update batch operation statement code example
- mysql - count after sql select
- Getting Started with MySQL Multiple Tables
- Linux regular backup mysql and sync to other servers
- How to compare the structure of two database tables in mysql
- A Brief Talk About MySQL Pivot Tables
- MySQL create data tables and establish primary and foreign key relationships
- mysql : How to withdraw the number of people in each SQL Department
- mysql : Is there a combination of LIKE and IN operators in the query condition?
- mysql : Sorting table by date from the SQL connected table
- sql : When trying to calculate the number of days left, in dbms_output days are displayed with a minus
- mysql : Understand the lines from the database table
- php : Warning a piece of text from one column to another SQL
- sql : How to create two triggers that run each other and limit the repetition of launches with two times?
- sql : How to get a challenge on the chain of objects of the object type Returning SELF?
- sql : As separate lines, turn and convert to columns using aggregate functions.
- sql : Counting table column values