CODEWITHSUNDEEP
sqlselect
Atees Tigga
Atees Tigga

Reputation: 21

select multiple tables with condition in sql query

First Table-- Product Table

P_ID   P_Name    D_Flag
 37    keyboard    N
 38    mouse       N
 39    hardisk     Y
 40    pendrive    Y
 41    printer     Y
 42    CD-DVD      Y
 43    cardreader  Y

and the second Table Product_Detail Table

P_ID  P_ID  P_Prize  P_Company  D_Flag
 11    37    600       Intex     N      
 12    38    200       Enter     N
 13    39    1000      XYZ       N
 14    40    200       SONY      Y
 15    41    5000      LG        Y

then the output should like--------> ProductList Table

P_Name      P_Prize   P_Company 
hardisk      --        --
pendrive     200       SONY
printer      5000      LG
CD-DVD       --        --
cardreader   --        --

Note- Therefor table ProductList contains those Product Name(P_Name), Product Prize(P_Prize) and Product Company(P_Company) where Product.D_Flag='Y' and select should those field from Table Product_Detail where Product_Detail.D_Flag='Y'. Otherwise values should be null.

Upvotes: 2

Views: 16480

Answers (4)

Aspirant
Aspirant

Reputation: 2278

select p_name,decode(b.d_flag,'N',null,p_prize) p_prize,decode(b.d_flag,'N',null,P_Company) P_Company
from product a,product_detail b
where a.p_id=b.p_id;

Upvotes: 1

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

SELECT P.p_name, Case When PL.p_prize is Null Then "--" Else PL.p_prize END, 
       Case When PL.p_company is Null Then "--" ELSE  PL.p_company
  FROM Product P
    LEFT JOIN Product_Detail PL ON P.p_id = PL.p_id AND PL.d_flag = 'Y'
 WHERE P.d_flag = 'Y'

Upvotes: 1

John Woo
John Woo

Reputation: 263733

SELECT  a.P_NAME,
        COALESCE(b.P_Prize, '--') Prize,
        COALESCE(b.P_Company, '--') CompanyName
FROM    Product a
        LEFT JOIN product_Detail b
            ON a.P_ID = b.P_ID AND
                a.D_FLAG = 'Y' AND
                b.D_FLAG = 'Y'
-- WHERE additional conditions here

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 0

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

This should do it:

SELECT p.p_name, pd.p_prize, pd.p_company
FROM product p
LEFT JOIN product_detail pd ON p.p_id = pd.p_id AND pd.d_flag = 'Y'
WHERE p.d_flag = 'Y'

So what you are doing is you are picking every row in product and the WHERE clause limits it to ones where the d_flag is set to Y.

You are then Left Outer Joining, meaning you get all the rows on the left (in product) and any intersections in product_detail on the right. The join condition matches the rows but only to ones where the d_flag in the detail table is 'Y' as well. This is not put in the WHERE clause as you negate the outer join (you won't get the rows where there is no join).

Upvotes: 2

Related Questions