August 5, 2010 at 10:28 am
Brandie Tarvin (8/5/2010)
--This is the hidden INNER JOIN Code
--that will only get me those stores that owe Royalties. Unless all stores in Florida owe,
--then there will be missing records.
Select t1.StoreID, t1.StoreName, t1.GeneralManager, t1.EstablishDate, t3.RoyaltiesDue
from StoreInfo t1
LEFT OUTER JOIN FranchiseDetails t3
on t1.StoreID = t3.StoreID
where t1.StoreState = ‘FL’ and t3.RoyaltiesDue > 0.00;
--Code that will get me all stores in Florida and then print RoyaltiesDue for ONLY
--the stores that owe Royalties
Select t1.StoreID, t1.StoreName, t1.GeneralManager, t1.EstablishDate, t3.RoyaltiesDue
from StoreInfo t1
LEFT OUTER JOIN FranchiseDetails t3
on t1.StoreID = t3.StoreID
and t3.RoyaltiesDue > 0.00
where t1.StoreState = ‘FL’;
Does that make sense to you?
This was perfect Brandie, I was approaching it from the complete inverse situation, where moving my criterion into the JOIN still exploded my resultset, instead of limiting it, but was NOT what I wanted. Apologies for no data, lunch is over, gotta get back to the nosy grindstone...sample of what I mean:
--left join with the argument in the WHERE
SELECT DISTINCT am2.memid,am2.ratecode
FROM dbo.vwActiveMembers AS am2
LEFT JOIN dbo.capterm AS c2 ON am2.ratecode = c2.ratecode
WHERE am2.fullname LIKE 'a%'
AND c2.ratecode='C600A25'--278 rows
ORDER BY am2.memid,am2.ratecode--,c2.capamount
go
--left join with the argument in the JOIN
SELECT DISTINCT am2.memid,am2.ratecode
FROM dbo.vwActiveMembers AS am2
LEFT JOIN dbo.capterm AS c2 ON am2.ratecode = c2.ratecode
AND c2.ratecode='C600A25'
WHERE am2.fullname LIKE 'a%'--10,334 rows
-- essentially returns all values from vwActiveMembers and
-- only returns values from capterm where the ratecode='C600A25'
ORDER BY am2.memid,am2.ratecode--,c2.capamount
go Your example made me consider it from a different angle (as far as when you would WANT to do this), and I think I have a handle on it now...although I'll still go check out that other thread, definitely. ;-):-D
[End technical discussionation/]
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 5, 2010 at 10:28 am
Chris Morris-439714 (8/5/2010)
BUSHY PLANS!! Thank you, Paul. Time to give that another read.
:laugh: :laugh: :laugh: :laugh: :laugh:
Isn't associative memory wonderful?
Stick or branch join...bushy plans...funny!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 5, 2010 at 10:29 am
Jack Corbett (8/5/2010)
Jon,Where you place the criteria for OUTER JOIN's DOES make a difference. Whenever you place criteria in the WHERE clause that references a column in your OUTER table SQL Server will convert that JOIN to an INNER JOIN. This is because of when the criteria is evaluated. I don't have the order of evaluation memorized but it does make a difference. Itzik covers this in one of his books, I think it is Professional T-SQL Programming, but not sure.
Itzik also had a great tips and tricks session where he showed order of JOIN criteria can make a difference as well.
I didn't follow this the first time I read it, but I do now. Thanks Jack!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 5, 2010 at 10:34 am
Paul White NZ (8/5/2010)
I feel a blog entry coming on...
About time, we've only seen what, ten from you in the last two weeks?? :w00t: Don't you sleep?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 5, 2010 at 10:36 am
/* Give me all persons their relationship and the typenote the second join is INNER and the ON for it is defined first and the
ON Clause for the LEFT JOIN is defined second which means
SQL Server does the INNER JOIN first and then the OUTER JOIN
on that Virtual Table */
SELECT
*
FROM
#persons AS P LEFT JOIN
#relationships AS R JOIN
#relationship_types AS RT
ON R.relationship_type_id = RT.relationship_type_Id
ON P.person_id = R.person_id;
That is sick and twisted stuff....I like it!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 5, 2010 at 10:47 am
Paul White NZ (8/5/2010)
I feel a blog entry coming on...
I was just thinking the same thing. I haven't had a truly technical post in a quite awhile.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2010 at 10:57 am
Jack Corbett (8/5/2010)
Paul White NZ (8/5/2010)
I feel a blog entry coming on...I was just thinking the same thing. I haven't had a truly technical post in a quite awhile.
You should!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 5, 2010 at 10:59 am
The more I thought about the clock and metric time, the more confused I got.
Defining a day, hours, and minutes - not so bad.
But then I started thinking there are 10 days in a week, 10 weeks in a month, and 10 months in a year.
At this point, my head started to hurt.
And then there suddenly is some technical discussion happening.
I just hope when we go metric with time, that I only work 5 days a week, and 4 weeks a month.
And can live with the fact that I will short change them by 2 months a year.
Greg E
August 5, 2010 at 11:01 am
Paul White NZ (8/5/2010)
Jack Corbett (8/5/2010)
Paul White NZ (8/5/2010)
I feel a blog entry coming on...I was just thinking the same thing. I haven't had a truly technical post in a quite awhile.
You should!
Both should do it. I could use some reading on the subject :w00t::w00t:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2010 at 11:03 am
Greg Edwards-268690 (8/5/2010)
The more I thought about the clock and metric time, the more confused I got.Defining a day, hours, and minutes - not so bad.
But then I started thinking there are 10 days in a week, 10 weeks in a month, and 10 months in a year.
At this point, my head started to hurt.
And then there suddenly is some technical discussion happening.
I just hope when we go metric with time, that I only work 5 days a week, and 4 weeks a month.
And can live with the fact that I will short change them by 2 months a year.
Greg E
With this, would day and night be on a rotating schedule? Some days at 2pm it would be night and some days it would be day?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2010 at 11:05 am
Paul White NZ (8/5/2010)
Jack Corbett (8/5/2010)
Paul White NZ (8/5/2010)
I feel a blog entry coming on...I was just thinking the same thing. I haven't had a truly technical post in a quite awhile.
You should!
Hey, we wouldn't age as far because our birthdays would only come every 1000 days instead of every 365 days. 😛
(10 day/week x 10 week/month x 10 month = 1000 days)
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 5, 2010 at 11:07 am
Alvin Ramard (8/5/2010)
Hey, we wouldn't age as far because our birthdays would only come every 1000 days instead of every 365 days. 😛
(10 day/week x 10 week/month x 10 month = 1000 days)
Thus the new 40 will be 20. I don't know if I want to fell 40 when I am twenty - that's just not right.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2010 at 11:07 am
Greg Edwards-268690 (8/5/2010)
The more I thought about the clock and metric time, the more confused I got.Defining a day, hours, and minutes - not so bad.
But then I started thinking there are 10 days in a week, 10 weeks in a month, and 10 months in a year.
At this point, my head started to hurt.
And then there suddenly is some technical discussion happening.
I just hope when we go metric with time, that I only work 5 days a week, and 4 weeks a month.
And can live with the fact that I will short change them by 2 months a year.
Greg E
No, that's a metric calendar and you're not going to get that until you live in space. Since we still have to deal with the vagarities of Earth's orbit our best bet with the calendar is the 13 month calendar with 7 days a week and 4 weeks a month.
Once you live in space, feel free to jump to the metric calendar! You'll get to make all kinds of new names up for the days and months. Maybe even the weeks if you're feeling creative!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 5, 2010 at 11:19 am
CirquedeSQLeil (8/5/2010)
Greg Edwards-268690 (8/5/2010)
The more I thought about the clock and metric time, the more confused I got.Defining a day, hours, and minutes - not so bad.
But then I started thinking there are 10 days in a week, 10 weeks in a month, and 10 months in a year.
At this point, my head started to hurt.
And then there suddenly is some technical discussion happening.
I just hope when we go metric with time, that I only work 5 days a week, and 4 weeks a month.
And can live with the fact that I will short change them by 2 months a year.
Greg E
With this, would day and night be on a rotating schedule? Some days at 2pm it would be night and some days it would be day?
I was banking on the day being the same length. I could live easier with the seasons being messed up than not having a clue that in several weeks what I thought would be the middle of the day is the middle of the night.
I think the space I will live in won't be outer space, but a nice padded room.
Greg E
August 5, 2010 at 12:08 pm
Off the technical and metric time topic (I love non-metric cause I'm an American :-D). Just had my PASS NomCom interview. Hard to tell how it went because it's on the phone so you don't get feedback you get in person.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 17,146 through 17,160 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply