Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple select statements from multiple tables in the one select query for one row. Expand / Collapse
Author
Message
Posted Friday, July 18, 2008 6:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 23, 2011 1:11 AM
Points: 19, Visits: 21
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.







Post #536689
Posted Friday, July 18, 2008 9:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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.
Post #536893
Posted Friday, July 18, 2008 9:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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_book 12 10_001 0 1
--My_book 12 10_002 1 0


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.
Post #536909
Posted Sunday, July 20, 2008 11:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 23, 2011 1:11 AM
Points: 19, Visits: 21
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.
Post #537354
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse