June 10, 2015 at 10:54 am
Hi, I am really not sure where I had to post this so I'm sorry if this isn't the right place
So I'm creating a query to extract data, and I have a little problem
Let's say after some manipulation, I create a database that I'll call c, that depends from other databases
c contains: c.BRAND, c.STATE, c.YEAR, c.ID and c.PAID
I would like to extract data only for IDs that have a c.PAID of at least 10000 for any combination of (BRAND STATE YEAR ID). Example:
If ID 132 has those values:
115 Yes 2012 132 10245.12
I want to have all the information about 132, even if the other "c.PAID" are not over 10000.
Therefore, I cannot just do this:
SELECT c.BRAND, c.STATE, c.YEAR, c.ID, c.PAID
FROM (...) c
WHERE c.PAID > 10000
GROUP BY c.BRAND, c.STATE, c.YEAR, c.ID
Because then if 132 has another line that looks like this:
115 Yes 2011 132 8512.76
I won't have it. Is there a way I could change my condition so it takes all the IDs that have at least one PAID that exceeds 10000?
Thank you!
June 10, 2015 at 11:02 am
jodevil99 (6/10/2015)
Hi, I am really not sure where I had to post this so I'm sorry if this isn't the right placeSo I'm creating a query to extract data, and I have a little problem
Let's say after some manipulation, I create a database that I'll call c, that depends from other databases
c contains: c.BRAND, c.STATE, c.YEAR, c.ID and c.PAID
I would like to extract data only for IDs that have a c.PAID of at least 10000 for any combination of (BRAND STATE YEAR ID). Example:
If ID 132 has those values:
115 Yes 2012 132 10245.12
I want to have all the information about 132, even if the other "c.PAID" are not over 10000.
Therefore, I cannot just do this:
SELECT c.BRAND, c.STATE, c.YEAR, c.ID, c.PAID
FROM (...) c
WHERE c.PAID > 10000
GROUP BY c.BRAND, c.STATE, c.YEAR, c.ID
Because then if 132 has another line that looks like this:
115 Yes 2011 132 8512.76
I won't have it. Is there a way I could change my condition so it takes all the IDs that have at least one PAID that exceeds 10000?
Thank you!
Something like this:
SELECT
c.BRAND,
c.STATE,
c.YEAR,
c.ID,
c.PAID
FROM
(...) c
WHERE
EXISTS(SELECT 1 FROM (...) c1 where c1.ID = c1.ID and c1.PAID > 10000)
ORDER BY
c.ID,
c.BRAND,
c.STATE,
c.YEAR;
June 10, 2015 at 11:10 am
EDIT: I'm still testing but I think I answered my question, by using CTE. Thanks!
_________________________________________
Ok thanks I will try that, another question though:
I told it came from:
FROM (...) c
Let's say for example that (...) was another query:
FROM (SELECT [stuff] FROM @db WHERE [stuff] GROUP BY [stuff])
Is there another way I could write "(...) c"? Because my query in (...) is pretty long and if I write it all over again, it seems to take a very long time to do the whole query; however SQL already did it once so I guess there has to be a way to call it?
I don't know if that's clear enough, but in other words:
I have:
SELECT ...
FROM (Pretty long code) c
WHERE EXISTS(SELECT 1 FROM (Pretty long code) c1 where... )
GROUP BY...
Is there a way I wouldn't have to write (Pretty long code) twice?
Thanks!
June 10, 2015 at 11:30 am
You could use a CTE to do that. Something like this:
WITH VeryLongCode AS
(SELECT ID=132,Brand=115,Year=2012,Paid=10245.12,State='Yes'
UNION ALL
SELECT ID=132,Brand=115,Year=2011,Paid=8512.76,State='Yes'
UNION ALL
SELECT ID=112,Brand=113,Year=2012,Paid=1245.12,State='Yes'
UNION ALL
SELECT ID=133,Brand=115,Year=2012,Paid=2245.12,State='Yes'
UNION ALL
SELECT ID=142,Brand=115,Year=2011,Paid=8112.76,State='Yes'
UNION ALL
SELECT ID=142,Brand=113,Year=2012,Paid=11245.12,State='Yes'
)
SELECT VeryLongCode.BRAND, VeryLongCode.STATE, VeryLongCode.YEAR, VeryLongCode.ID, VeryLongCode.PAID
FROM VeryLongCode
WHERE EXISTS (SELECT 1 FROM VeryLongCode AS InnerQuery WHERE InnerQuery.Paid>10000 AND VeryLongCode.ID=InnerQuery.ID)
The performance of such a query might not be wonderful, depending on what the long code in your subquery is doing, but it will prevent you from typing it twice 🙂
Cheers!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply