Derived Table for single table to fetch correct result Set

Question

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 .

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

score 1 · Answer 1 · answered Apr 22 '18 at 06:03

1

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%');

answered Apr 22 '18 at 06:03

Paresh

654
5
7

1

Yes thanks Paresh , But can it be possile through derived query – sampath acharya Apr 22 '18 at 06:49
what output did you get? from your query, I think you will just get double of each column – Paresh Apr 22 '18 at 14:52
maybe you can try specifying the column names you want instead of select * in both main and sub query. – Paresh Apr 22 '18 at 14:53
1

Yes we need to try by specifying column – sampath acharya Apr 22 '18 at 16:51

score 0 · Answer 2 · answered Apr 22 '18 at 05:47

0

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.

answered Apr 22 '18 at 05:47

Mehta Harshit

130
6

1

Mehta : Order table = tblproduct here no order table – sampath acharya Apr 22 '18 at 06:49
Take it as tblProduct only and you can left join a single table for fetching result. @sampathacharya – Mehta Harshit Apr 23 '18 at 06:40

Derived Table for single table to fetch correct result Set

2 Answers2