CODEWITHSUNDEEP
sqljoinconditional-statementsmultiple-columnsderived-table
sampath acharya
sampath acharya

Reputation: 143

Derived Table for single table to fetch correct result Set

I have a table where i need to fetch a result using derived table . Display the products which name starts with either ‘S’ or ‘W’ and which belongs to Stationary and cost more than 300 Rs . enter image description here

I have tried but i got multiple same column . is it correct or how to correct .

        select * from
        (
           select * from tblProduct 
           where (P_name like 'S%' or  P_name like 'W%') 
         ) A
         join
         tblProduct t
       on t.P_id=a.P_id
        where a.P_family like '%Stationary%' and a.cost >300

Upvotes: 0

Views: 55

Answers (2)

Paresh
Paresh

Reputation: 654

Why do you need a derived table here? You can simply do this:

    select * from tblProduct 
    where P_family like '%Stationary%' 
    and cost > 300
    and (P_name like 'S%' or  P_name like 'W%');

Upvotes: 1

Mehta Harshit
Mehta Harshit

Reputation: 140

Take it as

select order.*,p.p_name from orderTable order left join tblProduct 
where p.P_name like 'S%' or p.P_name like 'W%' and
order.P_family='Stationery' and order.cost>300

Now change that orderTable name as above table image given and then you can get Your desired result.

Upvotes: 0

Related Questions