retrieve data based on a condition

Posted on

Question :

I have 2 tables, TA and TB:

TA
--------
id  name
1   a
2   b
3   c
4   d
5   e
6   f

TB
--------
id  p_id  name
1   1     a
2   1     b
3   1     c
4   2     a
5   2     b
6   2     d

I need : if p_id = 1 then :

id  name match
1   a    1 
2   b    1
3   c    1
4   d    0
5   e    0
6   f    0

If p_id = 2 then :

id  name match
1   a    1 
2   b    1
3   c    0
4   d    1
5   e    0
6   f    0

Answer :

You need to use a LEFT OUTER JOIN to achieve this:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE ta
( id INT NOT NULL, 
  name VARCHAR(20) NOT NULL
) ;


INSERT INTO ta
  (id, name)
VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c'),
  (4, 'd'),
  (5, 'e'),
  (6, 'f') ;



CREATE TABLE TB
    (`id` int, `p_id` int, `name` varchar(20))
;

INSERT INTO TB
    (`id`, `p_id`, `name`)
VALUES
    (1, 1, 'a'),
    (2, 1, 'b'),
    (3, 1, 'c'),
    (4, 2, 'a'),
    (5, 2, 'b'),
    (6, 2, 'd')
;

GO

CREATE PROCEDURE test
(my_p_id INT)
BEGIN
  SELECT TA.id, TA.name,
         (IF(TB.name IS NOT NULL, 1, 0)) AS `match`
    FROM TA
    LEFT JOIN TB
      ON TA.name = TB.name
     AND TB.p_id = my_p_id;

END;

Query 1:

CALL test(1);

Results:

| ID | NAME | MATCH |
|----|------|-------|
|  1 |    a |     1 |
|  2 |    b |     1 |
|  3 |    c |     1 |
|  4 |    d |     0 |
|  5 |    e |     0 |
|  6 |    f |     0 |

Query 2:

CALL test(2);

Results:

| ID | NAME | MATCH |
|----|------|-------|
|  1 |    a |     1 |
|  2 |    b |     1 |
|  3 |    c |     0 |
|  4 |    d |     1 |
|  5 |    e |     0 |
|  6 |    f |     0 |

The query of interest is wraped in the procedure test. That makes working with SQLFiddle a little easier. But you certainly can use the query outside of a procedure. However, pay attention to the fact that the comparison to the parameter value is part of the LEFT JOIN evaluation.

For more informations on JOINs check out my JOIN series here: http://sqlity.net/en/1146/a-join-a-day-introduction/

That series is written about SQL Server. But most concepts and most of the syntax apply to MySQL as well.

Leave a Reply

Your email address will not be published. Required fields are marked *