Need help in the sql query to give a result set based on the previous version

advertisements

i have two tables , table1 and table2.

table1
id  name   city        uqid
1   vikas mysore       2

table2
id  uqid    name    status
1   1   vikas   pending
1   2   Vikas   processing

I have a SQL query to fetch the details of table1 joined with table2

select table1.id,
       table1.name,
       table1.city,
       table2.status
from table1
   left outer join table2
     on table2.uqid = table1.uqid
    and table2.id = table1.id

this will give me the result set

id      name     city    status
1   vikas   mysore  processing

how can i modify the above query to not to give us the result set until the status is set to "pass" in table2 for uqid = 1 and id = 1 ?


Try the following.

select table1.id,
       table1.name,
       table1.city,
       table2.status
from table1
   left outer join table2
     on table2.uqid = table1.uqid
    and table2.id = table1.id
where table2.status ilike 'pass';

If by stating that you need table2's uqid=id=1, you mean that you need both the fields to have same value then use the following.

select table1.id,
       table1.name,
       table1.city,
       table2.status
from table1
   left outer join table2
     on table2.uqid = table1.uqid
    and table2.id = table1.id
where table2.status ilike 'pass' and table2.uqid=table2.id;

Suggestion: Try to normalize your tables