Re: Using AND in query

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