April 5, 2011 at 11:31 pm
I need to use the result of a query, call it query 1, as a WHERE criteria in query 2. I tried to do it as a subquery as I got this to work before but this time it doesnt work.
I believe the reason is that query 1 does not return a single line, it returns about 500. I need query 2 to run using each result from query 1 in the where condition.
Can someone help me to do this. I think I read using an array ight be the answer but I dont know, please help.
April 5, 2011 at 11:51 pm
The IN clause perhaps? (SQL doesn't have arrays)
Post the two queries and we may be able to help you better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2011 at 12:00 am
GilaMonster (4/5/2011)
The IN clause perhaps? (SQL doesn't have arrays)Post the two queries and we may be able to help you better.
Ok here is query 1. This query looks up a date range and finds distinct IP addresses that show up on that date range. This particular query returns about 400 distinct addresses from my DB
SELECT DISTINCT dbo.scr_dim_ipaddress.ipaddress
FROM dbo.scr_dim_site_request INNER JOIN
dbo.scr_dim_site ON dbo.scr_dim_site_request.site_id = dbo.scr_dim_site.site_id INNER JOIN
dbo.scr_fct_exact_access ON dbo.scr_dim_site_request.site_request_id = dbo.scr_fct_exact_access.site_request_id INNER JOIN
dbo.scr_dim_ipaddress ON dbo.scr_fct_exact_access.user_ip_id = dbo.scr_dim_ipaddress.ip_id INNER JOIN
dbo.scr_fct_web ON dbo.scr_dim_site_request.site_request_id = dbo.scr_fct_web.site_request_id
WHERE (dbo.scr_dim_site.site_name LIKE '%kcc.authoria.com%') AND (NOT (dbo.scr_fct_exact_access.seconds_since_epoch < 1301616000)) AND
(NOT (dbo.scr_fct_exact_access.seconds_since_epoch > 1301702340))
Then I want to use each returned IP in this query that matches the IP to a user name. The value im referencing is the dbo.scr_dim_ipaddress.ipaddress equaling each value returned by query 1.
SELECT DISTINCT dbo.scr_dim_ipaddress.ipaddress, dbo.scr_dim_user.user_name
FROM dbo.scr_dim_site_request INNER JOIN
dbo.scr_dim_site ON dbo.scr_dim_site_request.site_id = dbo.scr_dim_site.site_id INNER JOIN
dbo.scr_fct_exact_access ON dbo.scr_dim_site_request.site_request_id = dbo.scr_fct_exact_access.site_request_id INNER JOIN
dbo.scr_dim_ipaddress ON dbo.scr_fct_exact_access.user_ip_id = dbo.scr_dim_ipaddress.ip_id INNER JOIN
dbo.scr_fct_web ON dbo.scr_dim_site_request.site_request_id = dbo.scr_fct_web.site_request_id INNER JOIN
dbo.scr_dim_user ON dbo.scr_fct_exact_access.user_id = dbo.scr_dim_user.user_id
WHERE (NOT (dbo.scr_fct_exact_access.seconds_since_epoch < 1301616000)) AND (dbo.scr_dim_ipaddress.ipaddress = N'172.016.107.044') AND
(NOT (dbo.scr_dim_user.user_name = '-')) AND (NOT (dbo.scr_fct_exact_access.seconds_since_epoch > 1301702340))
April 6, 2011 at 1:37 am
Look into temporary (#tbl) tables and table variables (@tbl), they were built to do exactly what you're looking for. You join them back into the next query in the procedure as though they were a standard table.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 6, 2011 at 2:47 am
lance.kentwell (4/5/2011)
I need to use the result of a query, call it query 1, as a WHERE criteria in query 2. I tried to do it as a subquery as I got this to work before but this time it doesnt work.I believe the reason is that query 1 does not return a single line, it returns about 500. I need query 2 to run using each result from query 1 in the where condition.
Can someone help me to do this. I think I read using an array ight be the answer but I dont know, please help.
The two queries are almost identical:
SELECT DISTINCT i.ipaddress
FROM dbo.scr_dim_site_request sr
INNER JOIN dbo.scr_dim_site s
ON sr.site_id = s.site_id
INNER JOIN dbo.scr_fct_exact_access ea
ON sr.site_request_id = ea.site_request_id
AND (NOT (ea.seconds_since_epoch < 1301616000))
AND (NOT (ea.seconds_since_epoch > 1301702340))
INNER JOIN dbo.scr_dim_ipaddress i
ON ea.user_ip_id = i.ip_id
INNER JOIN dbo.scr_fct_web w
ON sr.site_request_id = w.site_request_id
WHERE (s.site_name LIKE '%kcc.authoria.com%')
SELECT DISTINCT i.ipaddress, u.user_name
FROM dbo.scr_dim_site_request sr
INNER JOIN dbo.scr_dim_site s
ON sr.site_id = s.site_id
INNER JOIN dbo.scr_fct_exact_access ea
ON sr.site_request_id = ea.site_request_id
AND (NOT (ea.seconds_since_epoch < 1301616000))
AND (NOT (ea.seconds_since_epoch > 1301702340))
INNER JOIN dbo.scr_dim_ipaddress i
ON ea.user_ip_id = i.ip_id
INNER JOIN dbo.scr_fct_web w
ON sr.site_request_id = w.site_request_id
INNER JOIN dbo.scr_dim_user u
ON ea.user_id = u.user_id
AND (NOT (u.user_name = '-'))
WHERE (i.ipaddress = N'172.016.107.044')
Why would you want to run the same query twice? Can you rephrase the result you want from this?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 6, 2011 at 3:03 am
OK let me explain. In query 1 I am looking for IP addresses for entries where (s.site_name LIKE '%kcc.authoria.com%')
The ultimate goal is to get the username that mapps to the IP address.
In query 1 I am looking for IP addresses for entries where (s.site_name LIKE '%kcc.authoria.com%'
However when I query on %kcc.authoria.com% I know that the username field will always be null. However there are other site_names matching the IP address that will have a username.
So what im doing is first looking up the IP address of a user who accessed the kcc.authoria.com site. Then I need to take that IP and query for entries that do have a username (it will be a differnet site name) and map the IP to the username.
April 6, 2011 at 5:43 am
This should work just fine although it's far from elegant.
SELECT DISTINCT i.ipaddress, u.user_name
FROM dbo.scr_dim_site_request sr
INNER JOIN dbo.scr_dim_site s
ON sr.site_id = s.site_id
INNER JOIN dbo.scr_fct_exact_access ea
ON sr.site_request_id = ea.site_request_id
AND (NOT (ea.seconds_since_epoch < 1301616000))
AND (NOT (ea.seconds_since_epoch > 1301702340))
INNER JOIN dbo.scr_dim_ipaddress i
ON ea.user_ip_id = i.ip_id
INNER JOIN dbo.scr_fct_web w
ON sr.site_request_id = w.site_request_id
INNER JOIN dbo.scr_dim_user u
ON ea.user_id = u.user_id
AND (NOT (u.user_name = '-'))
INNER JOIN (
SELECT DISTINCT i2.ipaddress
FROM dbo.scr_dim_site_request sr2
INNER JOIN dbo.scr_dim_site s2
ON sr2.site_id = s2.site_id
INNER JOIN dbo.scr_fct_exact_access ea2
ON sr2.site_request_id = ea2.site_request_id
AND (NOT (ea2.seconds_since_epoch < 1301616000))
AND (NOT (ea2.seconds_since_epoch > 1301702340))
INNER JOIN dbo.scr_dim_ipaddress i2
ON ea2.user_ip_id = i2.ip_id
INNER JOIN dbo.scr_fct_web w2
ON sr2.site_request_id = w2.site_request_id
WHERE s2.site_name LIKE '%kcc.authoria.com%'
) d ON d.ipaddress = i.ipaddress
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 6, 2011 at 6:15 pm
Thanks I dont care so much if its elegant, just as long a it works 🙂 I'll give it a try. Since im still a newbie I would really appreciate if you could maybe give me some disription on each line as to what your actually doing. I think If I understand it better I can adapt this to a number of things I have to address.
Thanks.
April 6, 2011 at 10:15 pm
OK I think we are almost there but I had some discrepency. In my original query to find distinact IPs that matched the site kcc.authoria.com I got 479 results.
If I cust out your equivalent query (see below) and run it O get the same resutls 479.
However when I run the whole things I get 706 results. I would expect to get the same results. Any idea why they are differenet?
SELECT DISTINCT i2.ipaddress
FROM dbo.scr_dim_site_request sr2
INNER JOIN dbo.scr_dim_site s2
ON sr2.site_id = s2.site_id
INNER JOIN dbo.scr_fct_exact_access ea2
ON sr2.site_request_id = ea2.site_request_id
AND (NOT (ea2.seconds_since_epoch < 1301616000))
AND (NOT (ea2.seconds_since_epoch > 1301702340))
INNER JOIN dbo.scr_dim_ipaddress i2
ON ea2.user_ip_id = i2.ip_id
INNER JOIN dbo.scr_fct_web w2
ON sr2.site_request_id = w2.site_request_id
WHERE s2.site_name LIKE '%kcc.authoria.com%'
April 6, 2011 at 10:21 pm
Dont worry i know why, its becuase there are some cases where a differnet user had the same IP adderss on that day and they would be 2 distinct matches.
April 7, 2011 at 7:08 am
lance.kentwell (4/6/2011)
Thanks I dont care so much if its elegant, just as long a it works 🙂 I'll give it a try. Since im still a newbie I would really appreciate if you could maybe give me some disription on each line as to what your actually doing. I think If I understand it better I can adapt this to a number of things I have to address.Thanks.
Which part would you like more explanation of, Lance?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 8, 2011 at 6:00 am
I'm a little surprised no one has mentioned Common Table Expressions (CTE). They work a bit like temp tables but lack the management issues (dropping, concurrency, etc). Unfortunately I don't have enough time for a full explanation incorporating your code. I would suggest you do a quick web search on "SQL Server CTE" and see if you come up with anything.
Here's a link within this site to a quick tutorial: CTE's in SQL 2005[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply