March 5, 2002 at 9:07 pm
I have two SQL Queries that I am trying to combined into one because I want to repeate the row region that the queries are placed and as far as I know, you can only have one recordset in a repeate region. Besides, it is more efficient to have one query instead of two. How do you combind these queries:
----Query 1----
SELECT req_id, r.client_id, c.client_name, req_short_description, req_city, req_state FROM requirement r INNER JOIN client c ON r.client_id = c.client_id INNER JOIN supplier_contact s ON r.sup_contact_id = s.sup_contact_id WHERE s.sup_contact_email = [SessionVar] AND ((SELECT count (*) FROM submission WHERE sup_contact_id = r.sup_contact_id) > 0)
Note: [Session Var] is just the session variable used. I don't actually have
the format in the code. Just know that it is a variable.
----Query 2----
SELECT COUNT (*) as "NumofSubs" FROM submission WHERE sup_contact_id = (SELECT s.sup_contact_id FROM submission s INNER JOIN supplier_contact r ON
s.sup_contact_id = r.sup_contact_id WHERE r.sup_contact_email = [SessionVar])
Note: Again, [Session Var] is a session variable used, but I know that the
format is wrong.
If anyone knows how to combind these queries, I would greatly appreciate it.
Thanks for your time.
March 6, 2002 at 4:28 am
One way to combine the two is this:
DECLARE @Count int
SELECT @Count = COUNT (*) FROM submission WHERE sup_contact_id = (SELECT s.sup_contact_id FROM submission s INNER JOIN supplier_contact r ON
s.sup_contact_id = r.sup_contact_id WHERE r.sup_contact_email = [SessionVar])
SELECT req_id, r.client_id, c.client_name, req_short_description, req_city, req_state, @Count as "NumofSubs" FROM requirement r INNER JOIN client c ON r.client_id = c.client_id INNER JOIN supplier_contact s ON r.sup_contact_id = s.sup_contact_id WHERE s.sup_contact_email = [SessionVar] AND ((SELECT count (*) FROM submission WHERE sup_contact_id = r.sup_contact_id) > 0)
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 6, 2002 at 4:31 am
Try this, this joing the two together to perform one select, barring any misunderstandings I have about your data this should work or at least give you some idea how to get down the path.
SELECT
req_id,
r.client_id,
c.client_name,
req_short_description,
req_city,
req_state,
count(*) as "NumOfSubs"
FROM
requirement r
INNER JOIN
client c
ON
r.client_id = c.client_id
INNER JOIN
supplier_contact sc
INNER JOIN
submission s
ON
s.sup_contact_id = sc.sup_contact_id
ON
r.sup_contact_id = sc.sup_contact_id
WHERE
sc.sup_contact_email = [SessionVar]
GROUP BY
req_id,
r.client_id,
c.client_name,
req_short_description,
req_city,
req_state
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 6, 2002 at 10:04 pm
quote:
One way to combine the two is this:DECLARE @Count int
SELECT @Count = COUNT (*) FROM submission WHERE sup_contact_id = (SELECT s.sup_contact_id FROM submission s INNER JOIN supplier_contact r ON
s.sup_contact_id = r.sup_contact_id WHERE r.sup_contact_email = [SessionVar])
SELECT req_id, r.client_id, c.client_name, req_short_description, req_city, req_state, @Count as "NumofSubs" FROM requirement r INNER JOIN client c ON r.client_id = c.client_id INNER JOIN supplier_contact s ON r.sup_contact_id = s.sup_contact_id WHERE s.sup_contact_email = [SessionVar] AND ((SELECT count (*) FROM submission WHERE sup_contact_id = r.sup_contact_id) > 0)
Yeah that worked, but I still have a problem. I tested your code in Query Analyzer and it works fine. But I have a problem when it place online.
First, I was using Dreamweaver UD and it don't like the code. It was told I must have a SELECT statement or call a stored procedure. So I placed the everything except the "DECLARE @Count int" in UD, and then manually added the DECLARE part before the core that UD wrote. I'm not sure if this is valid anyway but I received an ASP error: "expected end of statement"
Do you have any suggestions?
Robert Marda
March 7, 2002 at 12:38 am
I don't know what Dreamweaver UD is? I do know that the code won't work without the DECLARE statement. Can't you place the DECLARE statement in UD? What does UD stand for?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 7, 2002 at 4:54 am
quote:
First, I was using Dreamweaver UD and it don't like the code. It was told I must have a SELECT statement or call a stored procedure. So I placed the everything except the "DECLARE @Count int" in UD, and then manually added the DECLARE part before the core that UD wrote. I'm not sure if this is valid anyway but I received an ASP error: "expected end of statement"
Sounds like you placed the entire code from Robert in the execution object on the ASP side, the problem is this is actually a batch type item and they don't work in ASP. If you create a procedure on the server say like
CREATE PROCEDURE up_DoWhatIWant
AS
ThenAllTheSQLCodeHere
Then make sure your user for the connection has execute rights on this Procedure. Now just call up_DoWhatIWant from you execute object (The place where the SQL goes) and it shoudl work fine.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply