August 20, 2018 at 7:29 am
NikosV - Thursday, August 16, 2018 7:46 AMsgmunson - Thursday, August 16, 2018 6:47 AMNikosV - Thursday, August 16, 2018 6:29 AMsgmunson - Thursday, August 16, 2018 6:12 AMYou've placed the SumInsured query inside of a LEFT JOIN, but not done any JOINs within that SumInsured query to ensure it's the Client class 1 type. You can add joins within your SumInsured query for that purpose... But if you get incorrect results because there may be more than 1 row in one of the tables, then you need criteria that can limit the rows returned from the JOINed tables that you add, which would be best as JOIN conditions.OK. I got the gist now. Tried it and so far it's looking good. Hopefully the user will agree on Monday. Fingers crossed.
Please, tell me that Joins are a general pain in the arse and that it's not just my arse.
One last question, what do you mean by join condition?
If the join condition is after the ON keyword then I guess I need common ground. ClientDetails which contains ClientClass can't join onto PolicyCover. How would I add an extra join condition?
Otherwise, I don't know what a Join condition is...JOINs are NOT the pain in the arse.... It's dealing with people who don't understand the nature of their data that is the largest source of pain. You're just learning the JOIN stuff, and it's not easy when you're just getting started. Maybe it will help to just recognize that learning SQL as opposed to a procedural language like VB or C#, is a paradigm shift, requiring you to think not about what you can do to a row, but instead, think about what you can do to a column...
Thats good to know. What about the question on the join condition I asked?
The last bit you said was right. I was always using c# and vb so that shift is quite tricky.
A JOIN condition is simply a temporary relationship (for the duration of your query) between two tables. It is specified with ON, followed by any number of conditional phrases, which may or may not specify both tables, although in most cases, that WILL take place. These phrases will be separated by AND or OR, and you can use parentheses to group sets of conditions together. Anything you could specify in a WHERE clause can be used as part of a JOIN, but you probably don't want sub-queries involved in a JOIN.
When you say "ClientDetails which contains ClientClass can't join onto PolicyCover", I have to ask WHY? You don't HAVE to use a foreign-key relationship, but there does have to actually BE a relationship of some kind within the data in order to create a useful JOIN. If there is a column that both tables have in common, then that's a good place to start. You would need to know if the data relationship, based on the common column, is one to one (in terms of rows with a specific value in one table versus the number of rows in the other table with the same specific value), or is one to many (or many to one), or is many to many...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 29, 2018 at 2:04 am
Nikos, you have a Policy Header record and 5 Policy Detail records, presumably each detail record contains different information (e.g. items covered, bike, car, house, tv, phone)
if you only want 1 row returned, which one do you want, which value are you looking for? a value which is the same on all 5 rows?
if you don't care which row then take top 1, but it doesn't seem to make sense...if all the info you need is on the header you do not need a join, but if you think joins are a pain in the arse you will find sql development very painful indeed
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply