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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
