CODEWITHSUNDEEP
sqloracle-database
Sunil
Sunil

Reputation: 112

Oracle :Matching row from first and second table

I have 2 tables, code and price:

Table 1

code  name   section 
1      abc       lab
2      xyz      anes 
3      pqr       tbr

Table 2

code  name   section   transaction    id   price    
1      abc       lab       [email protected]    10   500
2      xyz      anes       [email protected]    5    200 
3      pqr       tbr       [email protected]    10   100

I need to get code, name and section from the 1st table and price from the 2nd table based on the transaction and id from the 2nd table. If the price is not available for the given code the users can see it as 0.

I wrote a query for this

SELECT c.code, c.name ,c.section,p.price
  FROM First c, second p
WHERE c.code IN
           ('00', '00', '00', '00', '01140', '01160', '01180', '01210',
            '01212', '01215', '01200', '01670', '01680', '01682', '00', '00')
  AND c.code = p.code
  AND p.id = 1001
  AND p.transaction = '[email protected]';

I am getting records when there is a price define in 2nd table but there is no record coming when I try to retrieve records which does not have price define in second table.

Upvotes: 1

Views: 200

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

Basically you need a left join, but there is a problem, you cant get any results from the second table because you are filtering data from it p.id = 1001 AND p.transactionby = '[email protected]'; so, when there is no price it means that you cant have this registry because it simple doesnt exist. What you want would be possible if the id and transactionby was on table1. To get the c.code, c.name, c.section, p.cptprice you would have to do a left join with a subquery but still is possible to not have results.

SELECT c.code, c.name ,c.section,p.cptprice
  FROM First c, 
       (select * 
          from second 
         where id = 1001 
           and transactionby = '[email protected]') p
WHERE c.code IN
           ('00', '00', '00', '00', '01140', '01160', '01180', '01210',
            '01212', '01215', '01200', '01670', '01680', '01682', '00', '00')
  AND c.code = p.code(+)

c.code = p.code(+) is oracle syntax only.

Upvotes: 1

Related Questions