Subqueries?

[ADW]

Can anyone help? the query I am working with works fine: -

Select distinct TotalReceipts = count(all transaction_type), warehouse AS 'WAREHOUSE' from strip
where warehouse = 'S1'
and TRANSACTION_TYPE = 'issu'
AND DATED > '2000-08-01'
group by warehouse

but when I add an extra request: -

Select distinct TotalReceipts = count(all transaction_type), warehouse AS 'WAREHOUSE' from strip
where warehouse = 'S1'
and TRANSACTION_TYPE = 'issu'
and TRANSACTION_TYPE = 'recp'
AND DATED > '2000-08-01'
group by warehouse
I dont get a result. Do I need to include a subquery for the second and statement, if so why?

 

[rajeevnandanmishra]

Hi adw,
In following statement
where warehouse = 'S1'
and TRANSACTION_TYPE = 'issu'
and TRANSACTION_TYPE = 'recp'
AND DATED > '2000-08-01'
you are trying to fetch all the records where transaction = 'issu' and 'recp', which is not possible. you should modify your query as
where warehouse = 'S1'
and (TRANSACTION_TYPE = 'issu'
or TRANSACTION_TYPE = 'recp')
AND DATED > '2000-08-01'

Hope this will help you.

 

[ColinM]

SQL Server will return all rows that match your where clause. You are looking for rows that equal 'issu' AND 'recp'. A row can't equal both of these, you want an OR statement, just a small change required.

Select distinct TotalReceipts = count(all transaction_type), warehouse AS 'WAREHOUSE' from strip
where warehouse = 'S1'
and (TRANSACTION_TYPE = 'issu'
OR TRANSACTION_TYPE = 'recp')
AND DATED > '2000-08-01'
group by warehouse