running a query within a query

  • Thread starter Thread starter Cire via AccessMonster.com
  • Start date Start date
C

Cire via AccessMonster.com

Hi all i've got a couple of questions that i need answers, to help me develop
a database query solution with access as 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've searched
the forum but haven't found a solution. Let me describe it with an example:

i have tables chg, chgstat and chgcat
i use the normal select command to select certain columns.
say the table created by this query is named "details"

now i got ano few tables, ctct, callreq
i got to select columns from these and at the same time select columns from
"details"
how do i do this in sql? as pass through queries uses the sql code directly
unlike the GUI of linked tables

e.g.
SELECT id hp add country request status type
FROM ctct callreq details
ORDER Ascending

the columns 'status' and 'type' is from the table formed by the query
"details"
i heard that using atomic instructions allows SQL to form the table "details"
1st and then store it to be used by commands later. but i haven't been able
to find much information abt atomic instructions, hopefully i can find
answers here.
 
Cire said:
Hi all i've got a couple of questions that i need answers, to help me develop
a database query solution with access as 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've searched
the forum but haven't found a solution. Let me describe it with an example:

i have tables chg, chgstat and chgcat
i use the normal select command to select certain columns.
say the table created by this query is named "details"

now i got ano few tables, ctct, callreq
i got to select columns from these and at the same time select columns from
"details"
how do i do this in sql? as pass through queries uses the sql code directly
unlike the GUI of linked tables

e.g.
SELECT id hp add country request status
FROM ctct callreq details
ORDER Ascending

the column 'status' is from the table formed by the query
"details"
i heard that using atomic instructions allows SQL to form the table "details"
1st and then store it to be used by commands later. but i haven't been able
to find much information abt atomic instructions, hopefully i can find
answers here.
Click to expand...

anyone? and there's some error to the code, forgotten to include the joins:

SELECT id hp add country request status time
 
I must admit I'm a bit befuddled by your posting.

The sample query you've posted is not correct. If you are talking about a
more or less standard version of SQL then I would expect to see something
more like

SELECT id, hp, add, country, request, status, time
FROM ctct INNER JOIN callreq ON chg.id=callreq.time INNER JOIN details ON
details.sta=ctct..hp
ORDER BY ID, Hp ASC

Each item in the SELECT clause needs to be separated with a comma
Each item in the ORDER BY clause needs to be separated with a comma
Each item in the SELECT and ORDER BY clauses must be unique enough for the
query analyzer to identify. That is, if a field name exists in more than
one table in the query. then the item must contain the tablename and the
fieldname.

Then you talk about running a query with a query, but I only see one query.
You really need to take a course on or read a book about designing queries.
If you are trying to do pass-through queries, then you should try to get a
book or query that includes information about the particular system you are
trying to pass the query to.
 
John said:
I must admit I'm a bit befuddled by your posting.

The sample query you've posted is not correct. If you are talking about a
more or less standard version of SQL then I would expect to see something
more like

SELECT id, hp, add, country, request, status, time
FROM ctct INNER JOIN callreq ON chg.id=callreq.time INNER JOIN details ON
details.sta=ctct..hp
ORDER BY ID, Hp ASC

Each item in the SELECT clause needs to be separated with a comma
Each item in the ORDER BY clause needs to be separated with a comma
Each item in the SELECT and ORDER BY clauses must be unique enough for the
query analyzer to identify. That is, if a field name exists in more than
one table in the query. then the item must contain the tablename and the
fieldname.

Then you talk about running a query with a query, but I only see one query.
You really need to take a course on or read a book about designing queries.
If you are trying to do pass-through queries, then you should try to get a
book or query that includes information about the particular system you are
trying to pass the query to.
Click to expand...
yes sorry, my query knowledge isn't too good, the prob is i don't quite know
the method for doing sub-queries for ms sql. So far i have only found out in
using CREATE VIEW statements to create a temporary table, followed by the
main query after it. I believe my example is not clear enough + the numerous
syntax errors..and i do know i have to separate with commas, somehow i didn't
put that in...

anw back to my question: basically is CREATE VIEW statements the way to go?
another method i found out is using brackets to establish another select
statement but i feel the create view method is more systematic for me and
gives a rather good structure and is somehow less confusing for me. I'll try
to post a better example soon
 
ok here's an example:

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 .....

is the above statement correct? as in the CREATE VIEW statement is above the
main query
 
I'm not sure if that will work or not. Although I do know taht Status = Null
should be Status is Null

If you want advice on creating queries etc for MS SQL, a better forum is one of
the MS SQL forums - probably one for new users.
 
Back
Top