CodingForums.com - View Single Post

Old Pedant · 07-12-2012, 08:42 PM

First of all, SQL Server accepts *EITHER*

Code:

    V.voltage AS voltage2
or 
    V.voltage voltage2

Personally, I *always* use AS just to make the code crystal clear.

And the "voltage" that is ambiguous is the one in red below:

Code:

select P2.voltage,V.voltage AS voltage2,count(*) as KOL 
from Lights_America..temp_products AS P2 
left join  voltage AS V on (P2.voltage=V.id) 
left join excludedproducts AS E on (E.storeid=9 and E.id=P2.id)  
WHERE  P2.id > 0  
AND voltage in (1)  
AND manufacturer_name in (143,135,...,140)  
AND isnull(E.exc,0) < 1 
group by P2.voltage,V.voltage 
order by voltage2

BUT...this query has other problems.

AND isnull(E.exc,0) < 1 will effectively convert the LEFT JOIN on E into an INNER JOIN.

See here:
http://www.codingforums.com/showthre...192#post818192

Possibly true of AND manufacturer_name in (143,135,...,140) as well. Can't tell, since the query doesn't say what table that field is coming from.

Oh...and the two period in Lights_America..temp_products *ARE* legal. Normally, in SQL Server, you'd expect to see Lights_America.dbo.temp_products but I believe that when you omit the "owner" [that's what dbo is] (that is, have two periods) it means accept any owner.

07-12-2012, 08:42 PM	PM User \| #10
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,237 Thanks: 59 Thanked 3,998 Times in 3,967 Posts	First of all, SQL Server accepts EITHER Code: V.voltage AS voltage2 or V.voltage voltage2 Personally, I always use AS just to make the code crystal clear. And the "voltage" that is ambiguous is the one in red below: Code: select P2.voltage,V.voltage AS voltage2,count() as KOL from Lights_America..temp_products AS P2 left join voltage AS V on (P2.voltage=V.id) left join excludedproducts AS E on (E.storeid=9 and E.id=P2.id) WHERE P2.id > 0 AND voltage in (1) AND manufacturer_name in (143,135,...,140) AND isnull(E.exc,0) < 1 group by P2.voltage,V.voltage order by voltage2 BUT...this query has other problems. AND isnull(E.exc,0) < 1* will effectively convert the LEFT JOIN on E into an INNER JOIN. See here: http://www.codingforums.com/showthre...192#post818192 Possibly true of AND manufacturer_name in (143,135,...,140) as well. Can't tell, since the query doesn't say what table that field is coming from. Oh...and the two period in Lights_America..temp_products ARE legal. Normally, in SQL Server, you'd expect to see `Lights_America.dbo.temp_products` but I believe that when you omit the "owner" [that's what dbo is] (that is, have two periods) it means accept any owner. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.