Обсуждение: Using AND in query

On 07/08/2010 20:40, aravind chandu wrote:

> condition in where clause :
> table.date in date() to date() and table.item = "laptop" and table.item
> = "Desktop"

I don't think this is correct - you need "BETWEEN".

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

aravind chandu wrote on 07.08.2010 21:40:
> Hello every one,
> I have encountered a problem while working .I have a sample table with
> the following data
> *TID*     *Date*     *Item*
> T100     8/1/2010     Laptop
> T100     8/1/2010     Desktop
> T101     8/1/2010     Laptop
> T102     8/1/2010     Desktop
> T103     8/2/2010     Laptop
> T103     8/2/2010     Desktop
> T104     8/2/2010     Laptop
>
> need the data when a person bought laptop & desktop on the sameday.I
> used a condition in where clause but its not working,it is returning no
> rows.Can any one please help me to resolve this issue ?
>
> condition in where clause :
> table.date in date() to date() and table.item = "laptop" and table.item
> = "Desktop"
>
You should first understand why your query is not working.

The condition

    and table.item = 'laptop' and table.item = 'Desktop'

says: I want all rows where the column item has the value 'Laptop' and *at the same time* has the value 'Desktop'
Which clearly cannot be the case (a column can only have a single value)

So you need to join all "Laptop" rows to all "Desktop" rows to get what you want.

SELECT l.tid, l.purchase_date
FROM the_table_with_no_name l
   JOIN the_table_with_no_name d
        ON l.tid = d.tid AND l.purchase_date = d.purchase_date AND d.item = 'Desktop'
WHERE l.item = 'Laptop'

Or as an alternative:

SELECT tid, purchase_date
FROM orders
WHERE item in ('Laptop', 'Desktop')
GROUP BY tid, purchase_date
HAVING count(*) = 2

Regards
Thomas

On Sat, Aug 07, 2010 at 12:40:41PM -0700, aravind chandu wrote:
> Hello every one,
>
> I have encountered a problem while working .I have a sample table with the
> following data
>
>
> TID Date Item
> T100 8/1/2010 Laptop
> T100 8/1/2010 Desktop
> T101 8/1/2010 Laptop
> T102 8/1/2010 Desktop
> T103 8/2/2010 Laptop
> T103 8/2/2010 Desktop
> T104 8/2/2010 Laptop
>  
> need the data when a person bought laptop & desktop on the sameday.

This is actually relatively straight-forward using modern PostgreSQL.
Rather than counting, use direct aggregation to compare, so:

SELECT TID, "Date"
FROM "table"
GROUP BY TID, "Date"
HAVING ARRAY['Laptop','Desktop'] <@ array_agg(item);

That last line checks whether the array created by array_agg contains
at least the elements Laptop and Desktop.  If you need an "equals"
comparison rather than the above "contains or equals", you can sort
both arrays canonically using the array_sort function below and then
compare them with "=".

CREATE OR REPLACE FUNCTION array_sort(ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(SELECT * FROM unnest($1) ORDER BY 1);
$$;

The "=" query would look like this:

SELECT TID, "Date"
FROM "table"
GROUP BY TID, "Date"
HAVING array_sort(ARRAY['Laptop','Desktop']) = array_sort(array_agg(item));

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 7 Aug 2010, at 23:18, Thomas Kellerer wrote:

> Or as an alternative:
>
> SELECT tid, purchase_date
> FROM orders
> WHERE item in ('Laptop', 'Desktop')
> GROUP BY tid, purchase_date
> HAVING count(*) = 2


This one is incorrect, it will also find people who bought two laptops or two desktops on the same date.

I was going to suggest:

SELECT tid, "date", 001::bit as type
  FROM orders
 WHERE item = 'Laptop'
UNION ALL
SELECT tid, "date", 010::bit as type
  FROM orders
 WHERE item = 'Desktop'
GROUP BY tid, "date"
HAVING type & 011::bit = 011::bit;

But I think David's solution is more readable, as it leaves the item names in tact.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c5e6ee4286211665369939!

>
> The condition
>
>    and table.item = 'laptop' and table.item = 'Desktop'
>
> says: I want all rows where the column item has the value 'Laptop' and
> *at the same time* has the value 'Desktop'
> Which clearly cannot be the case (a column can only have a single value)
>
> So you need to join all "Laptop" rows to all "Desktop" rows to get
> what you want.

why not use OR ?

    ...  AND (table.item = 'laptop' OR table.item='Desktop') ...

Alban Hertroys wrote on 08.08.2010 10:46:
> On 7 Aug 2010, at 23:18, Thomas Kellerer wrote:
>
>> Or as an alternative:
>>
>> SELECT tid, purchase_date
>> FROM orders
>> WHERE item in ('Laptop', 'Desktop')
>> GROUP BY tid, purchase_date
>> HAVING count(*) = 2
>
>
> This one is incorrect, it will also find people who bought two laptops or two desktops on the same date.

Right. I didn't think about that ;)

> But I think David's solution is more readable, as it leaves the item names in tact.

I absolutely agree. Another example of PG's cool array handling :)

Regards
Thomas

On Sun, Aug 08, 2010 at 01:55:19AM -0700, John R Pierce wrote:
> >The condition
> >
> >   and table.item = 'laptop' and table.item = 'Desktop'
> >
> >says: I want all rows where the column item has the value 'Laptop'
> >and *at the same time* has the value 'Desktop'
> >Which clearly cannot be the case (a column can only have a single value)
> >
> >So you need to join all "Laptop" rows to all "Desktop" rows to get
> >what you want.
>
> why not use OR ?
>
>    ...  AND (table.item = 'laptop' OR table.item='Desktop') ...

OR doesn't account for duplicates.  Two laptops on the same date would
cause a false positive.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sun, Aug 08, 2010 at 12:33:40PM -0700, David Fetter wrote:
> On Sun, Aug 08, 2010 at 01:55:19AM -0700, John R Pierce wrote:
> > >The condition
> > >
> > >   and table.item = 'laptop' and table.item = 'Desktop'
> > >
> > >says: I want all rows where the column item has the value
> > >'Laptop' and *at the same time* has the value 'Desktop' Which
> > >clearly cannot be the case (a column can only have a single
> > >value)
> > >
> > >So you need to join all "Laptop" rows to all "Desktop" rows to
> > >get what you want.
> >
> > why not use OR ?
> >
> >    ...  AND (table.item = 'laptop' OR table.item='Desktop') ...
>
> OR doesn't account for duplicates.  Two laptops on the same date
> would cause a false positive.

Thinking this over a little more, it's probably fastest to combine the
approaches, i.e. use both a WHERE clause and a HAVING clause.  For
example:

SELECT TID, "Date"
FROM "table"
WHERE item = ANY(ARRAY['Desktop','Laptop'])
GROUP BY TID, "Date"
HAVING ARRAY['Desktop','Laptop'] <@ array_agg(item);

Cheers,
David
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate