Reply to: Cant quite get my head around SQL Inner Join.

[TheFaQQer]

And 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'!)

I worked with a guy who always gave the table a really meaningful alias. Which most of the time was longer than the name of the table in the first place.

[BolshieBastard]

for a simple inner join like yours, you could have them either way round

Many thanks! That was the real question I wanted answering.

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.

[VectraMan]

And that works too. I think I actually understand that now. Thanks.

[Spacecadet]

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.

STOP!! I covered this eventuality in my earlier post.

You'll want to use this

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

[VectraMan]

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.

[Spacecadet]

 
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

But that will only work if every price has an associated product
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.

[VectraMan]

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?

[Spacecadet]

And 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'!)

Even the scripts I wrote that build queries for me know better than that

[Durbs]

As chicane pointed out, you may want to look up aliases.

And 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'!)

[TheFaQQer]

Having 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

I agree.

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 ****.

[Spacecadet]

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.

Having 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

[Spacecadet]

Im 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

You might want to use a left outer join instead in this sort of scenario, so that you can easily see which employee's haven't had any training

Now, 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?

SQL is a declarative language, the order in which items appear in the statement isn't necessarily the order in which they are processed.
As chicane pointed out, you may want to look up aliases.

The query you have could be written as

SELECT Emp.Name, Trn.TrainingTitle, Trn.TrainingDate
FROM Employee Emp
    INNER JOIN TrainingTaken Trn ON Emp.EmployeeID = Trn.EmployeeID

This might look a bit pointless, untill you start dealing with self join (tables joined to themselves) and distributed queries (SQL Statements that get data from other databases/servers) or just very long table names

now

 
 
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

becomes:

SELECT Emp.Name, Trn.TrainingTitle, Trn.TrainingDate
FROM HRDatabase.dbo.Employee Emp
    INNER JOIN TrainingDatabase.dbo.TrainingTaken Trn ON Emp.EmployeeID = Trn.EmployeeID

Why would FROM Employee be used instead of FROM TrainingTaken?

Why does FROM Employee appear and not FROM TrainingTaken??

for a simple inner join like yours, you could have them either way round

[TheFaQQer]

Wouldn't you actually have three tables: emp, training, empTraining so that you only have to record the training title in one record?

[doodab]

SELECT Employee.Name, TrainingTaken.TrainingTitle, TrainingTaken.TrainingDate
  FROM Employee, TrainingTaken 
 WHERE Employee.EmployeeID = TrainingTaken.EmployeeID

Make any more sense now?

[chicane]

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?

Why would FROM Employee be used instead of FROM TrainingTaken?

Why does FROM Employee appear and not FROM TrainingTaken??

To answer your questions in the order presented:

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.