Home>

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.

■ Postscript
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
Part of
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

customer.cst_name
hello stock

customer_2.cst_name
hello corporation

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.