SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple select statements from multiple tables in the one select query for one row.


Multiple select statements from multiple tables in the one select query for one row.

Author
Message
Ronvy
Ronvy
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 23
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.
Greg Snidow
Greg Snidow
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4267 Visits: 2494
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.
Greg Snidow
Greg Snidow
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4267 Visits: 2494
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.
Ronvy
Ronvy
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 23
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search