July 18, 2008 at 6:13 am
Hi,
table name : item
item_id item_name
------ ---------
12 My_book
15 Another_book
table name : item_Process
item_id item_page process1status process2status
------- -------------- -------------- ---------------
12 10_001 0 2
12 10_002 2 0
table name : item_trans
item_id item_page transstatus
------ --------- ------------
12 10_001 0
12 10_002 1
12 10_003 2
These are the 3 tables I have.
I need to fetch the counts of pages with status other than 0 for the item for both item_trans and item_ process status in one query.
For eg like this :
select (count(ip.process1status)) from item_process where item_id = 12 and process1status <> 0,
select (count(ip.process2status)) from item_process where item_id = 12 and process2status <> 0,
select (count(it.transstatus)) from item_trans where item_id = 12 and transstatus <> 0)
select i.itemname from item i where i.item_id = 12 and p.pagename like '10_001%'
This is the query somewhat that I'm looking for to fetch a row of a particular item and the counts of the pages not '0'. I'm fetching these details from 3 different tables in one query.
But this query doesn't work.
Kindly suggest a solution for this please.
Many thanks in advance.
July 18, 2008 at 9:12 am
Ron (7/18/2008)
select i.itemname from item i where i.item_id = 12 and p.pagename like '10_001%'
Ron, the first of your four queries work fine with the data you provided, but the fourth only names one table, "item", in the FROM clause, but there are two aliases. I could assume the "p" alias means item_process, but then there is no column called "pagename". Did you mean p.item_page? Also, a good way to post your tables and data are like below. Often, questions go unanswered because they require work on the part of the people trying to help. But it is Friday, so what the heck?
--Create the tables
IF OBJECT_ID('TempDB..#item','u') IS NOT NULL
DROP TABLE #item
GO
CREATE TABLE #item
(
item_id INT,
item_name VARCHAR(30)
PRIMARY KEY(item_id)
)
IF OBJECT_ID('TempDB..#item_process','u') IS NOT NULL
DROP TABLE #item_process
GO
CREATE TABLE #item_process
(
item_id INT,
item_page VARCHAR(20),
process1status INT,
process2status INT
)
IF OBJECT_ID('TempDB..#item_trans','u') IS NOT NULL
DROP TABLE #item_trans
GO
CREATE TABLE #item_trans
(
item_id INT,
item_page VARCHAR(20),
transstatus INT
)
--Populate them
INSERT INTO #item
SELECT 12,'My_book' UNION ALL
SELECT 15,'Another_book'
INSERT INTO #item_process
SELECT 12,'10_001',0,2 UNION ALL
SELECT 12,'10_002',2,0
INSERT INTO #item_trans
SELECT 12,'10_001',0 UNION ALL
SELECT 12,'10_002',1 UNION ALL
SELECT 12,'10_003',2
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 18, 2008 at 9:35 am
Ron, I'm not sure exactly what you are looking for, but based on the four queries you provided, and I fixed the last one based on my assumptions, you get...
select (count(ip.process1status)) from #item_process ip where item_id = 12 and process1status <> 0
select (count(ip.process2status)) from #item_process ip where item_id = 12 and process2status <> 0
select (count(it.transstatus)) from #item_trans it where item_id = 12 and transstatus <> 0
select i.item_name from #item i, #item_process p where i.item_id = 12 and i.item_id = p.item_id AND p.item_page like '10_001%'
Output
1
1
2
MyBook
I think this is maybe what you are looking for?
SELECT
i.item_name,
t1.item_id,
t1.item_page,
t1.process1status,
t1.process2status
FROM #item i,
(--Derived table t1 counts the item_id in each process
SELECT
item_id,
item_page,
process1status = SUM(CASE WHEN process1status <> 0 THEN 1 ELSE 0 END),
process2status = SUM(CASE WHEN process2status <> 0 THEN 1 ELSE 0 END)
FROM #item_process
GROUP BY item_id,item_page
) t1--Should be an end paren before the 't1'. I can't get it to show.
WHERE i.item_id = t1.item_id
--Output
--My_book1210_00101
--My_book1210_00210
Let us know if this is at least headed in the right direction
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 20, 2008 at 11:23 pm
This is what I tried and succeeded.
select
(
select (count(ip.process1status)) from item_process ip where ip.item_id = i.item_id and process1status <> 0 and p.pagename like '10_001%' ,
select (count(ip.process2status)) from item_process ip where ip.item_id = i.item_id and process2status <> 0 and p.pagename like '10_001%',
select (count(it.transstatus)) from item_trans it where it.item_id = i.item_id and transstatus <> 0),
select i.itemname
)
from item i where i.item_id = 12
Thanks for the helping hand you extended a lot Greg,
Ron.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply