January 25, 2010 at 8:54 am
jordon.shaw (1/25/2010)
That's it! You're a genus! Thank you so much for your help!!!
Heh not quite mate, just a jobbing programmer, but thanks.
Do you really need the aggregated columns (the MAXed ones)? If so, would you be better off picking a single row from those which could be returned - say the most / least recent?
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
January 25, 2010 at 8:56 am
Actually, I didn't need those fields. I changed the query to this:
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end
FROM SNP_crs c
INNER JOIN SNP_crsunits u ON u.crs_id = c.crs_id
INNER JOIN SNP_media m ON m.crsunit_id = u.crsunit_id
LEFT JOIN (SELECT ud.crs_id
FROM SNP_crsunits ud
INNER JOIN SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1)
) ex ON ex.crs_id = c.crs_id
WHERE ex.crs_id IS NULL
AND NOT c.sch_start = '-'
GROUP BY c.crs_id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end
ORDER BY c.sch_start
Once again, thank you very much for your help!
January 25, 2010 at 9:04 am
This might run faster:
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end
FROM SNP_crs c
--INNER JOIN SNP_crsunits u ON u.crs_id = c.crs_id
--INNER JOIN SNP_media m ON m.crsunit_id = u.crsunit_id
LEFT JOIN (SELECT ud.crs_id
FROM SNP_crsunits ud
INNER JOIN SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1)
GROUP BY ud.crs_id
) ex ON ex.crs_id = c.crs_id
WHERE ex.crs_id IS NULL
AND NOT c.sch_start = '-'
--GROUP BY c.crs_id,
-- c.crs_name,
-- c.crs_author,
-- c.sch_start,
-- c.sch_end
ORDER BY c.sch_start
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
January 25, 2010 at 9:07 am
Even better! You're awesome!
January 25, 2010 at 12:55 pm
Alright, so now I'm going to add another layer of difficulty. This is for one last report that I have to create. This time, I want the class to show up, when any of the fields are = to 1, but if all the fields for all records are = to 1, then it shouldn't show up. So, I used the same type of query that you wrote and it does show when any of the fields have a 1; however, it doesn't take the class away when all the fields have a 1 and I can't figure out how to do that. Using the same tables and fields as before, here is my code:
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end
FROM SNP_crs c
INNER JOIN (SELECT ud.crs_id
FROM SNP_crsunits ud
INNER JOIN SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1)
GROUP BY ud.crs_id
) ex ON ex.crs_id = c.crs_id
ORDER BY c.sch_start
January 25, 2010 at 1:00 pm
I even tried this:
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end
FROM SNP_crs c
INNER JOIN (SELECT ud.crs_id
FROM SNP_crsunits ud
INNER JOIN SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1) AND
(md.intro = 0 OR
md.recorded = 0 OR
ud.edit_av = 0 OR
ud.sd_transc = 0 OR
md.md_bounced = 0)
GROUP BY ud.crs_id
) ex ON ex.crs_id = c.crs_id
ORDER BY c.sch_start
My thinking behind this was, if any of the fields = 1 and any of the fields = 0, then it will show up and if all the fields =1, then it won't! That actually works; however, if all the fields for only 1 unit is =1, then the class doesn't show up, because I'm grouping the fields together. So, even if unit 2 still has 0's, it won't show up, because it's grouping the crs_id together and unit 1 all has 1's.
January 26, 2010 at 2:15 am
There's another way of writing this type of query which might be more intuitive to you, and help you along with your new query. This replaces the query as in the post "This might work faster":
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end
FROM SNP_crs c
WHERE NOT EXISTS
(
SELECT 1
FROM SNP_crsunits ud
INNER JOIN SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE ud.crs_id = c.crs_id
AND (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1)
)
WHERE NOT c.sch_start = '-'
ORDER BY c.sch_start
Cheers
ChrisM
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
January 26, 2010 at 7:23 am
Thank you very much, once again! For my new query, I used what you gave me and did wrote this:
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start
FROM SNP_crs c
WHERE EXISTS
(
SELECT 1
FROM SNP_crsunits ud
INNER JOIN SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE ud.crs_id = c.crs_id
AND (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1)
)
AND EXISTS
(
SELECT 1
FROM SNP_crsunits ud
INNER JOIN SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE ud.crs_id = c.crs_id
AND (md.intro = 0 OR
md.recorded = 0 OR
ud.edit_av = 0 OR
ud.sd_transc = 0 OR
md.md_bounced = 0)
)
ORDER BY c.sch_start
This seems to be working, just want to see if you see any issues with it?
Thanks,
Jordon
January 26, 2010 at 7:36 am
You mean, include into the report where
Any of the check values is equal to 1
AND
Any of the check values is equal to 0
Is this correct?
Isn't this the same as saying
WHERE the check values are not all the same, either 1 or 0?
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
January 26, 2010 at 7:40 am
Well, the first query that I was working on, which you helped me with, was to see which class has been schedule, but no work has started, meaning nothing is equal to 1.
This query, I want to know, which class has started, meaning something is equal to 1; however, once everything is finished, meaning everything is equal to 1, then I don't want to see it anymore. So, while the work is going on, there will be at least 1 -1 and at least 1 -0; however, before the work is started, everything will be 0, so I don't want to see it in this query and when the work is complete, everything will be 1, so I don't want to see it in this report.
Hopefully this all makes sense? It's seems to be working perfectly.
Thanks,
Jordon
January 26, 2010 at 8:27 am
Yep that makes sense, thanks.
Combine your two subqueries into one, one scan of the tables will almost certainly cost less than two:
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start
FROM SNP_crs c
WHERE EXISTS
(
SELECT 1
FROM SNP_crsunits ud
INNER JOIN SNP_media md
ON md.crsunit_id = ud.crsunit_id
WHERE ud.crs_id = c.crs_id
AND (md.intro = 1 OR
md.recorded = 1 OR
ud.edit_av = 1 OR
ud.sd_transc = 1 OR
md.md_bounced = 1)
AND (md.intro = 0 OR
md.recorded = 0 OR
ud.edit_av = 0 OR
ud.sd_transc = 0 OR
md.md_bounced = 0)
)
ORDER BY c.sch_start
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
January 26, 2010 at 8:30 am
You have been a great help! I've never seen a query written that way; however, it makes total sense when you look at it! Once again, thanks for your help!
Jordon
January 26, 2010 at 1:01 pm
One thing that I've found doing that way, is it only select's 1 record, so if all of that 1 record has 1's, then it doesn't show up on the report. The problem is, there are several records for the same crs_id. Doing it the other way, where it goes through the DB twice, it first looks to see if there are 1's present and then it looks to see if there are 0's present, meaning that even if 1 record has all 1's but another record for the same crs_id has 0's, then it will still show up on the report.
It seems to be working perfectly and is really quick.
Once again, thanks for your help!
Jordon
January 27, 2010 at 2:16 am
You're welcome Jordon, and thanks for your cooperative effort.
Cheers
ChrisM
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
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply