-1

I am trying to query data simply like,

select * from product where (color='blue' or type='suv')

I want any particular data having "color" and "type" as mentioned in query but if i do not mention him "type" then it will get data from only "color".

For example:

product    color   type 
toyota      red     suv
honda       black   

select * from product where (color='blue' or type Is null)

In second query it return me both "toyota" and "honda".

What I need is to get product if I give its "color" value then it return data by its color only and if I give its "color" and "type" value then return data according to both values?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user3664724
  • 425
  • 1
  • 6
  • 18
  • More information needed. If you want just `color = 'blue'`, then leave `type` out of the `where` clause. And vice versa. – Gordon Linoff May 22 '14 at 11:19
  • @ Gordon Linoff can you please read my comment on @xagyg post where i clearify it – user3664724 May 22 '14 at 11:29
  • Basic information must be in the *question*, not just comments. Please edit your question. Also: `In second query it return me both "toyota" and "honda".` This contradicts the displayed data. Please clarify. Finally, the answer depends on the *table definition* - provide what you with `\d product` in psql. Can be columns be NULL? – Erwin Brandstetter May 27 '14 at 18:19

2 Answers2

1

If I understand correctly, if $1 and $2 are both filled in, then you want them to be treated as an or. If only one is, you want only that one.

This should implement that logic:

where (color = $1 and $1 <> '') or (type = $2 and $2 <> '')

However, if your table is big, I would suggest that you put together the simplest where clause that you can. So, use python logic to construct one of these:

where color = $1
where type = $2
where color = $1 or type = $2

The first two of these (at least) could take advantage of appropriate indexes.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Either this... returns anything that matches that colour and type, or if no type passed to the query, then everything that matches the colour:

select * from product where color=$1 and (type=$2 or $2='')

or ... returns anything that matches the colour and type as well as products that have no type specified:

select * from product where color='blue' and (type='suv' or type is null)

... it depends what you want to get back.

If both color and type may or may not be passed in and you want to use either in a query, then this:

select * from product where (color=$1 or $1='') and (type=$2 or $2='')
xagyg
  • 9,562
  • 2
  • 32
  • 29
  • actually i am using it is search process so i can enter "color" and "type" or i will enter any one of them then i will give that product accordingly – user3664724 May 22 '14 at 11:27
  • so do you mean you want this... `where (color = 'blue' or color is null) and (type = 'suv' or type is null)` – xagyg May 22 '14 at 11:28
  • actually i think you want this... `select * from product where (color=$1 or $1='') and (type=$2 or $2='')` ... choose out of the 4 solutions which suits you. – xagyg May 22 '14 at 11:30
  • but problem is that if i mention color=red value and type empty then it consider type as null and return me all product not only red car :( – user3664724 May 22 '14 at 11:32
  • `where (color=$1 or $1='') and (type=$2 or $2='')` will work. The AND ensures you still get the 'red' (or whatever) cars regardless of type. – xagyg May 22 '14 at 11:47
  • how to declare "color" and "type" value as you mentioned in your query $1 and @$2 ? – user3664724 May 22 '14 at 11:55
  • i use declare @var; set @var='red' and @war; set @war='SUV' – user3664724 May 22 '14 at 11:55
  • Does this help ... http://stackoverflow.com/questions/9771546/how-do-i-reference-named-parameters-in-postgres-sql-functions or this http://stackoverflow.com/questions/766657/how-do-you-use-variables-in-a-simple-postgresql-script – xagyg May 22 '14 at 12:28