Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Do you have multiple values in fields? –  ypercube Sep 18 '13 at 10:54
    
yes ... i mean ...for example one record in TA is 1 | a and other record is 2 | b....same in TB as well –  user21546 Sep 18 '13 at 10:56
    
Can you edit your question and put the data in tabular format? Can you see if it is like the SQL-Fiddle? –  ypercube Sep 18 '13 at 11:01
    
its same as you have mentioned in SQL-Fiddle –  user21546 Sep 18 '13 at 11:04
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
Great Sebastian. I was trying same query ,but i was unable to figure out 'ON' condition. thanks –  user21546 Sep 19 '13 at 5:49
1  
nice reply @SebastianMeine :) –  DevYudh Sep 19 '13 at 9:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.