Running a query that has a sub-query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Running a query that has a sub-query

Author

Topic

cire
Starting Member

15 Posts

Posted - 2006-02-14 : 05:19:16

Hi all i've got a couple of questions that i need answers, to help me develop a database query solution with access as a frontend.

i will be implementing pass through queries as speed is a big concern for me. The problem i have is that i need to run query within a query,i.e. multiple queries. i've searched
the forum but haven't found a solution. Let me describe it with an example:

SELECT call_req.summary, call_req.description, call_req.status, ctct.c_first_name AS assignedfn, ctct.c_last_name AS assignedln, ctct_1.c_first_name AS owner, ctct_1.c_last_name AS Sitename, int_org_1.iorg_name, sevrty.nx_desc, pri.nx_desc, impact.nx_desc, urgncy.nx_desc, sevrty.enum AS sev, impact.enum AS imp, urgncy.enum AS urge, pri.enum AS pri, call_req.category, prob_ctg.description, prob_ctg.sym, int_org_1.iorg_name, int_org_1.iorg_desc, loc.l_name, act_log.description

FROM ((((((((((ctct INNER JOIN call_req ON ctct.id = call_req.assignee) LEFT JOIN int_org ON ctct.c_org_id = int_org.id) LEFT JOIN ctct AS ctct_1 ON call_req.customer = ctct_1.id) LEFT JOIN int_org AS int_org_1 ON ctct_1.c_org_id = int_org_1.id) LEFT JOIN sevrty ON call_req.severity = sevrty.enum) LEFT JOIN pri ON call_req.priority = pri.enum) LEFT JOIN impact ON call_req.impact = impact.enum) LEFT JOIN urgncy ON call_req.urgency = urgncy.enum) INNER JOIN prob_ctg ON call_req.category = prob_ctg.persid) LEFT JOIN loc ON ctct_1.c_l_id = loc.id) LEFT JOIN closed_log

the last table in bold: closed_log is actually a result of another query. So what do i have to include to actually make SQL already execute the query, store the table closed_log temporaily and then execute the above query in order to retrieve the data

i heard that using atomic instructions allows SQL to do the above but i haven't been able to find much information abt atomic instructions, thus hopefully i can find answers here. hopefully my explaination is clear enough

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-14 : 09:57:53

[code]Select ....
from tbl1
inner join tbl2 on ....
inner join tbl3 on ....
...
left join
(select ... from ... where ...) as closed_log[/code]

The query u use to create the closed_log result set has to be inside the brackets (blue color portion)

Or u can have that query in a view with the name that u want and use it directly

cire
Starting Member

15 Posts

Posted - 2006-02-14 : 20:21:53

quote:
Originally posted by Srinika

Select ....
from tbl1
inner join tbl2 on ....
inner join tbl3 on ....
...
left join
(select ... from ... where ...) as closed_log

The query u use to create the closed_log result set has to be inside the brackets (blue color portion)

Or u can have that query in a view with the name that u want and use it directly

oh that means i can actually put, let say
CREATE VIEW closed_log AS
SELECT date, time, status, id
FROM chgsta
WHERE status = NULL

this 4 lines of code is placed on top, followed by the rest of the code below?

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-15 : 06:39:47

If u have a view defined as of

CREATE VIEW closed_log AS
SELECT date, time, status, id
FROM chgsta
WHERE status = NULL

U can directly use it as

Select ....
from tbl1
inner join tbl2 on ....
inner join tbl3 on ....
...
left join closed_log on .....

or if u don't have a view u can use

Select ....
from tbl1
inner join tbl2 on ....
inner join tbl3 on ....
...
left join ( SELECT date, time, status, id
            FROM chgsta
            WHERE status = NULL ) as closed_log on .....

cire
Starting Member

15 Posts

Posted - 2006-02-16 : 20:26:23

as in putting the code below all in one query:
CREATE VIEW closed_log AS
SELECT date, time, status, id
FROM chgsta
WHERE status = NULL

Select ....
from tbl1
inner join tbl2 on ....
inner join tbl3 on ....
...
left join closed_log on .....

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources