Cant quite get my head around SQL Inner Join.

[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]

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]

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]

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

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

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