Originally posted by Durbs
View Post
- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Collapse
You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:
- You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
- You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
- If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.
Logging in...
Previously on "Cant quite get my head around SQL Inner Join."
Collapse
-
-
Originally posted by Spacecadet View Post
for a simple inner join like yours, you could have them either way round
TBH I only use basic SQL queries so although your further examples may make things easier, it tends to go over my head.
Cheers though.
Leave a comment:
-
And that works too. I think I actually understand that now. Thanks.
Leave a comment:
-
Originally posted by VectraMan View PostExcellent, that works. The only snag is that if there is no discount (the id is zero) it doesn't return anything, but I can work around that with a dummy entry in the discounts table.
You'll want to use this
Code:select prices.price, products.product_name, discounts.discount_name from prices inner join products on prices.product_id = products.product_id inner join basket on basket.product_id = products.product_id left outer join discounts on discount.discount_id = baskets.discount_id
Leave a comment:
-
Excellent, that works. The only snag is that if there is no discount (the id is zero) it doesn't return anything, but I can work around that with a dummy entry in the discounts table.
Big thanks, and I promise to answer any C++ or x86 assembler questions you may have in future.
Leave a comment:
-
Code:select prices.price, products.product_name, discounts.discount_name from prices inner join products on prices.product_id = products.product_id inner join basket on basket.product_id = products.product_id inner join discounts on discount.discount_id = baskets.discount_id
and every product is in the basket table
and every basket has an associated discount.
you'll probably want to use LEFT OUTER join rather than INNER JOIN, depends on the database design.
Leave a comment:
-
I also have an INNER JOIN confusion to do with nesting.
This works:
SELECT * FROM prices INNER JOIN (products INNER JOIN basket ON products.product_id = basket.product_id) WHERE basket.id = 123 AND prices.region_id = 2 AND prices.discount_id = basket.discount_id
The prices table contains a price based on the product, region, and discount. The nested join is there to get the name of the product from the products table at the same time.
But I need a third thing which is the name of the discount associated with basket.discount_id from the discounts table, but I can't figure out how or where I get that in.
So the records I need to end up with are prices.price, products.product_name, and discounts.discount_name, based on basket.product_id, basket.discount_id, and basket.region_id.
Can anybody help?
Leave a comment:
-
Originally posted by Durbs View PostAnd dont do what the tossrag who wrote the procs i'm currently looking at and alias everything with incrementing letters of the alphabet so in a big procedure i'm constantly looking back to remember what h.code is (its the Country table, course it is, silly me should have realised that from the 'h'!)
Leave a comment:
-
Originally posted by Spacecadet View PostAs chicane pointed out, you may want to look up aliases.
Leave a comment:
-
Originally posted by Spacecadet View PostHaving grown up using the older method and having to use the horrible plus bracket things ( (+) )when defining outer joins I can definitely say that the INNER/LEFT/RIGHT/CROSS/FULL JOIN syntax is a lot more intuitive
Last year, though, I worked on a project where the other guy who had been there for 3 years insisted that my code was no good because I used the ANSI syntax (as recommended by Oracle!) instead of the + notation. His reasoning was twofold. 1 - he didn't like it. 2 - it made the code longer.
But, he was a ****.
Leave a comment:
-
Originally posted by chicane View PostAs a sidenote, if you find the INNER JOIN syntax unintuitive, you might consider using the older SQL-89 syntax. It makes more sense to me, but I understand that it's sadly being phased out in favour of the newer syntax.
Leave a comment:
-
Originally posted by BolshieBastard View PostIm struggling with part of the SQL statement for an inner join.
Suppose we have 2 tables, Employee and Training Taken.
The Employee table has the following columns; EmployeeID, Name, Telephone, StartedEmployment. There are 4 records in the table ie EmpID numbers 1, 2, 3 & 4.
The Training Taken table has the following columns; TrainingTakenID, EmployeeID, TrainingTitle, TrainingDate. There are also 4 records in the table for employees with the ID 1, 2 & 3.
A straightforward statement to retrieve a list of employee names along with the title and date of any training they have been on would seem to be along the lines of
SELECT Employee.Name, TrainingTaken.TrainingTitle, TrainingTaken.TrainingDate
FROM Employee
INNER JOIN TrainingTaken ON Employee.EmployeeID = TrainingTaken.EmployeeID
Originally posted by BolshieBastard View PostNow, I can understand the identification of the table and column names and just about get my head around the inner join.
But what I dont understand is where does the FROM Employee part of the statement is there or needed since the Employee table is identified by Employee.Name isnt it?
As chicane pointed out, you may want to look up aliases.
The query you have could be written as
Code:SELECT Emp.Name, Trn.TrainingTitle, Trn.TrainingDate FROM Employee Emp INNER JOIN TrainingTaken Trn ON Emp.EmployeeID = Trn.EmployeeID
now
Code:SELECT HRDatabase.dbo.Employee.Name, TrainingDatabase.dbo.TrainingTaken.TrainingTitle, TrainingDatabase.dbo.TrainingTaken.TrainingDate FROM HRDatabase.dbo.Employee INNER JOIN TrainingDatabase.dbo.TrainingTaken ON HRDatabase.dbo.Employee.EmployeeID = TrainingDatabase.dbo.TrainingTaken.EmployeeID
Code:SELECT Emp.Name, Trn.TrainingTitle, Trn.TrainingDate FROM HRDatabase.dbo.Employee Emp INNER JOIN TrainingDatabase.dbo.TrainingTaken Trn ON Emp.EmployeeID = Trn.EmployeeID
Originally posted by BolshieBastard View PostWhy would FROM Employee be used instead of FROM TrainingTaken?
Why does FROM Employee appear and not FROM TrainingTaken??Last edited by Spacecadet; 9 March 2010, 10:22.
Leave a comment:
-
Wouldn't you actually have three tables: emp, training, empTraining so that you only have to record the training title in one record?
Leave a comment:
-
Code:SELECT Employee.Name, TrainingTaken.TrainingTitle, TrainingTaken.TrainingDate FROM Employee, TrainingTaken WHERE Employee.EmployeeID = TrainingTaken.EmployeeID
Leave a comment:
-
Originally posted by BolshieBastard View PostBut what I dont understand is where does the FROM Employee part of the statement is there or needed since the Employee table is identified by Employee.Name isnt it?
Why would FROM Employee be used instead of FROM TrainingTaken?
Why does FROM Employee appear and not FROM TrainingTaken??
1) Most people writing SQL wouldn't prefix each column name with the full table name - it would make the query unnecessarily long. It's therefore necessary to specify the table name in full following the FROM clause. You might consider to reading up on table aliases to get a better understanding of how queries are written in the real world.
2) The query should work in exactly the same way if you were to select FROM TrainingTaken and INNER JOIN with Employee. It's a matter of personal preference and shouldn't affect the performance or behaviour of the query.
3) See answer to previous question.
As a sidenote, if you find the INNER JOIN syntax unintuitive, you might consider using the older SQL-89 syntax. It makes more sense to me, but I understand that it's sadly being phased out in favour of the newer syntax.
Leave a comment:
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Leave a comment: