CODEWITHSUNDEEP
sql
Patrick
Patrick

Reputation: 145

Search over multiple combined Columns

I have the following View:

enter image description here

I want to search there for, let's say: propertyname like 'Umax' and value_num = 550 but to display all properties from that part(s) (part_id) then. So in that case i would like to see all properties from parts 8, 10 and 11. And in addition I'd like to do that for more cases. Like: propertyname like 'Umax' and value_num = 550 and propertyname like 'Imax' and value_num = 5.5 which should show just all properties from part 8. Maybe I'm trying it in the completly wrong way, but I'm really struggling at this point right now!

Upvotes: 1

Views: 70

Answers (2)

John Woo
John Woo

Reputation: 263713

You can use EXISTS().

SELECT  a.*
FROM    tableName a
WHERE   EXISTS
        (
            SELECT  1
            FROM    tableName b
            WHERE   a.part_ID = b.part_ID AND
                    b.propertyname = 'umax' AND
                    b.value_num = 8
        )

Upvotes: 1

Hari Menon
Hari Menon

Reputation: 35405

select * from table1 where part_id in 
(select distinct part_id from table1 where propertyname = 'UMax' and <any other criteria>)

Replace table1 with your table/view name. You can add other criteria like value_num=5.5 and it should still work.

Upvotes: 0

Related Questions