What is wrong with this SQL?

Question

I'm sure this is something really simple that I'm overlooking, but MS SQL is new to me -- I am (or at least thought I was) fairly comfortable with basic MySql though.

SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17,   dl.day_19 
FROM lnktrk_links AS l, lnktrk_hourly AS h, lnktrk_daily AS dl 
LEFT JOIN lnktrk_descriptions AS d ON l.link_id = d.link_id 
WHERE l.link_id = h.link_id AND l.link_id = dl.link_id AND l.link_is_click = 1

The error I get is:

'The multi-part identifier "l.link_id" could not be bound.

However l.link_id definitely exists. The following query without the join works as expected:

SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17, dl.day_19 
FROM lnktrk_links AS l, lnktrk_hourly AS h, lnktrk_daily AS dl, lnktrk_descriptions AS d 
WHERE l.link_id = h.link_id AND l.link_id = dl.link_id AND d.link_id = l.link_id AND l.link_is_click = 1

Answer 1

try this :

SELECT 
l.link_id, 
l.link_allcount, 
d.desc_id, 
d.desc_count, 
d.desc_text, 
h.hour_17,   
dl.day_19 

FROM lnktrk_links AS l
inner join lnktrk_hourly AS h
on l.link_id = h.link_id
inner join  lnktrk_daily AS dl
on l.link_id = dl.link_id 
LEFT JOIN lnktrk_descriptions AS d ON l.link_id = d.link_id 
WHERE   l.link_is_click = 1

Answer 2

That LEFT JOIN is joining lnktrk_daily AS dl with lnktrk_descriptions AS d - neither of these are called l, so in the context of that JOINs ON clause, l doesn't mean anything.

It looks like at the moment you are using the 'cross join plus WHERE clauses' style rather than 'inner joins with ON clauses' style. If you switch to the latter then l should have meaning across the whole FROM clause.

Answer 3

Similar question answered here

Answer 4

I think it is that the syntax binds

lnktrk_daily AS dl 
LEFT JOIN lnktrk_descriptions AS d 
    ON l.link_id = d.link_id 

more tightly than the comma-separated clauses so you don't have an l in that part

What you have is grouped as:

SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17,   dl.day_19 
FROM lnktrk_links AS l, lnktrk_hourly AS h, 
( lnktrk_daily AS dl 
  LEFT JOIN lnktrk_descriptions AS d 
  ON l.link_id = d.link_id )
WHERE l.link_id = h.link_id AND l.link_id = dl.link_id AND l.link_is_click = 1

Answer 5

You should join them all - instead of using FROM

Try this

SELECT l.link_id, l.link_allcount, d.desc_id, d.desc_count, d.desc_text, h.hour_17,   dl.day_19 
FROM lnktrk_links AS l
LEFT JOIN lnktrk_hourly AS h ON l.link_id = h.link_id
LEFT JOIN lnktrk_daily AS dl ON l.link_id = dl.link_id
LEFT JOIN lnktrk_descriptions AS d ON l.link_id = d.link_id 
WHERE l.link_is_click = 1