CODEWITHSUNDEEP
mysqlsql
Jhilom
Jhilom

Reputation: 1028

Exists key word is not working

I have two tables like below.

Table val1

e_id  name   val  

1     A1     Abc         
2     A2     Abd        
3     A3     AbE        
4     A4     AEd   
5     A5     AEd   
6     A6     Bdc

Table val2

e_id   e_desc   t_id   

1       desc1    1
2       desc1    1
1       desc1    2
3       desc1    1
5       desc1    1
2       desc1    2
4       desc1    2
5       desc1    2
2       desc1    3
4       desc1    1
6       desc1    1
3       desc1    2
6       desc1    2
3       desc1    2
5       desc1    3

I want to fetch only values from Table Val1 by e_id map with e_id on Table Val2 where Table Val2 t_id = 1

I am using this query but it is fetching all the datas. How can I solve the problem. Here is the sql code I used shown below

SELECT 
    a.*,
    b.e_desc 
FROM 
    val1 AS a, val2 AS b 
WHERE 
    b.e_id = a.e_id 
AND EXISTS (SELECT 
                c.e_id 
            FROM val2 AS c 
            WHERE 
                c.e_id = a.e_id 
            AND c.t_id='1' 
           )

Upvotes: 0

Views: 67

Answers (2)

John Woo
John Woo

Reputation: 263893

It can be solved by JOINing both tables with two condition. try this one,

SELECT  a.*, b.e_desc
FROM    tableA a
        INNER JOIN tableB b 
           ON a.e_id = b.e_id  AND 
              a.e_id = b.t_id
WHERE b.t_id = 1

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166576

How about just

SELECT  a.*,
        b.e_desc 
FROM    val1 AS a,
        val2 AS b 
WHERE   b.e_id = a.e_id 
AND     b.t_id='1'

or even

SELECT  a.*,
        b.e_desc 
FROM    val1 AS a INNER JOIN
        val2 AS b ON b.e_id = a.e_id 
WHERE   b.t_id='1'

Upvotes: 2

Related Questions