Search code examples
sqlcartesian-productwhere-in

Avoid Cartesian Product in SQL when using Where in clause

My query goes below

 select a.col1,a.col2,b.col1,b.col2 from table1 a, table2 b
 where a.col3=value and b.col2 in (select col from table1 where col3=val)

This is giving be repeated values Eg.

Result obtained

-----------------------
S.No| Name| Value| Code
------------------------
1| Delhi| capital| 100
------------------------
2 |Mumbai| city | 101
------------------------
1 |Delhi| city| 101
------------------------
2 |Mumbai| capital |100
------------------------

Expected Result

 -----------------------
S.No| Name| Value| Code
------------------------
1 |Delhi| capital |100
----------------------
2 |Mumbai| city |101
----------------------

I tried Group by it is not working. How could this be solved?

Sample Data

Table 1

------------------------
S.NO| Name |Type |Value 

1| Delhi |BIGCITY| Capital
-------------------------
2| Mumbai| BIGCITY| City
------------------------

Table 2

---------------
Value |Code
---------------  
Capital |100 
---------------
City |101
--------------

  select a.sno,a.name,b.value,b.code from table1 a, table2 b
    where a.type=BIGCITY and b.col2 in (select value from table1 where col3=BIGCITY)
Solution

  • Try this basic join query:

    SELECT a.sno,
       a.name,
       COALESCE(b.value, 'NA') AS value,
       COALESCE(b.code, 'NA') AS code
FROM table1 a
LEFT JOIN table2 b
    ON a.value = b.value
WHERE a.type = 'BIGCITY'

    Demo here:

    SQLFiddle