CODEWITHSUNDEEP
sqlsql-serverouter-join
Binev
Binev

Reputation: 276

SQL join between table rows

I have an issue with sql query which to me seems pretty simple but can't get it to work properly.

So I have this table:

enter image description here broadcast_media_id tells me if the data is 1 - email or 2 - phone. I need to gather both email and phone for one employee (with the same employee_id).

The query I tried is this:

SELECT DISTINCT ed.employee_id,
    ed.text_value AS email,
    x.text_value AS phone
FROM employee_data ed
    INNER JOIN
        (
            SELECT employee_id,
                text_value
            FROM employee_data
            WHERE broadcast_media_id = 2
        ) x ON x.employee_id = ed.employee_id
WHERE broadcast_media_id = 1

Unfortunately because of the WHERE clause I get values only if employee has an email. So the result is this:

enter image description here

I am missing the phone value of "A773B230-754C-423F-97C4-E331268EA3C8" just because he doesn't have an email. I want to show this row as well and the value for email to be NULL.

It seems simple to me but I can't get it figured out.

I appreciate your help.

Upvotes: 0

Views: 60

Answers (4)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Just use LEFT JOIN if the employee always have phone. 

SELECT DISTINCT ed.employee_id,
    ed.text_value AS email,
    x.text_value AS phone
FROM employee_data ed
    LEFT JOIN
        (
            SELECT employee_id,
                text_value
            FROM employee_data
            WHERE broadcast_media_id = 2
        ) x ON x.employee_id = ed.employee_id
WHERE broadcast_media_id = 1

you can also avoid self join, but most probably the performance will be worse

SELECT ed.employee_id,
        MIN(CASE WHEN broadcast_media_id = 1 THEN text_value END) AS phone,
        MIN(CASE WHEN broadcast_media_id = 2 THEN text_value END) AS email
FROM employee_data ed
GROUP BY ed.employee_id

if employee can be without phone then you can use FULL JOIN 

SELECT DISTINCT
    COALESCE(ed.employee_id, x.employee_id) employee_id,
    ed.text_value AS email,
    x.text_value AS phone
FROM
    (SELECT * FROM employee_data WHERE broadcast_media_id = 1) ed
    FULL JOIN (SELECT * FROM employee_data WHERE broadcast_media_id = 2) x
        ON x.employee_id = ed.employee_id;

Upvotes: 1

Esteban P.
Esteban P.

Reputation: 2809

i would use a double LEFT JOIN like:

    SELECT ed.employee_id
          ,COALESCE(ed1.text_value,'') as mail
          ,COALESCE(ed2.text_value,'') as phone
      FROM employee_data ed
 LEFT JOIN employee_data ed1
        ON ed.employee_id = ed1.employee_id
       AND ed1.broadcast_media_id = 1
 LEFT JOIN employee_data ed2
        ON ed.employee_id = ed2.employee_id
       AND ed2.broadcast_media_id = 2

Upvotes: 0

Maverick Sachin
Maverick Sachin

Reputation: 883

Try this query -- I didn't use any joins though

Select
    EmpID,
    Max(Case When MediaID = 1 Then TextValue End) As EmailID,
    Sum(Case When MediaID = 2 Then Cast(TextValue As Int) End) As PhoneNumber
From employee_data
Group By EmpID
Order By EmpID;

Here - Cost of Sorting = 78% And Table Scan = 22%. (My instance data, it will definitely vary with your system and data though.)

If you do some indexing then it will perform better.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If you want to use a join, you can use LEFT JOIN:

SELECT ed.employee_id, ede.text_value AS email,
       edp.text_value AS phone
FROM employees e LEFT JOIN
     employee_data ede
     ON ede.employee_id = e.employee_id AND
        ede.broadcast_media_id = 1 LEFT JOIN
     employee_data edp
     ON edp.employee_id = e.employee_id AND
        edp.broadcast_media_id = 2

Note: This assumes that you have an employees table -- which seems very reasonable. This is quite handy for the LEFT JOIN. This would probably have the best performance.

If you don't have an employees table, then use a subquery (SELECT DISTINCT employee_id FROM employee_data).

I would tend to do this using conditional aggregation, which should also have reasonable performance.

Upvotes: 1

Related Questions