*sigh* Can someone explain to me where I'm going wrong here. :( Basically its an order system. Products have Colours and electrical specs which define the voltage. Distributors have access to certain Products. I need to find all of the Products in a certain category. Now when you make an Order, an entry is put into OrdProd (a copy of the price, prod id and quantity etc). This means prices can change over time and not effect older orders. Obviously not all Products have been ordered, so there may not be a row in OrdProd...so for this I need a Left join on OrdProd yeah?
Atm the page is alittle slow loading so I was hoping I could cut out these two sub queries to improve performance abit (this query can return 100 odd rows in a single hit or can sometimes be run multiple times for about 10 rows each). I dunno, performance on my lowly little Macbook isn't too bad, but on the server it can be quite a few seconds so perhaps its entirely a server issue.
So, the SQL,
SELECT PROD_ID, DISTPROD_PRODNAME, PROD_EXTRA, COL_NAME, COL_RGBHEX, VOLT_NAME, DISTPROD_PRICE,
isnull((SELECT ORDPROD_QUANTITY FROM ORDPROD WHERE ORDPROD_PROD_ID = PRODUCT.PROD_ID AND ORDPROD_ORD_ID = @OrdID), 0) AS QUANTITY,
isnull((SELECT ORDPROD_PRICE FROM ORDPROD WHERE ORDPROD_PROD_ID = PRODUCT.PROD_ID AND ORDPROD_ORD_ID = @OrdID), 0) AS ORDPROD_PRICE
FROM PRODUCT, DISTPAGE, DISTPROD, COLOUR, VOLTAGE, ELECTSPEC
WHERE DISTPAGE_DIST_ID = @DistID AND DISTPAGE_CAT_ID = PROD_CAT_ID
AND PROD_CAT_ID = @CatID
AND DISTPROD_DIST_ID = DISTPAGE_DIST_ID
AND DISTPROD_PROD_ID = PROD_ID
AND DISTPROD_STATUS = 'ACTIVE'
AND PROD_COL_ID = COL_ID AND PROD_ELECTSPEC_ID = ELECTSPEC_ID
AND ELECTSPEC_VOLT_ID = VOLT_ID
ORDER BY DISTPROD_PRODNAME
if I replace the top few lines with this,
SELECT PROD_ID, DISTPROD_PRODNAME, PROD_EXTRA, COL_NAME, COL_RGBHEX, VOLT_NAME, DISTPROD_PRICE
FROM PRODUCT, DISTPAGE, DISTPROD, COLOUR, VOLTAGE, ELECTSPEC
LEFT JOIN ORDPROD ON PROD_ID = ORDPROD_PROD_ID
SQL Express says,
Msg 207, Level 16, State 1, Line 4
Invalid column name 'PROD_ID'.
Am I making some noob mistake?
If I do PRODUCT.PROD_ID I get
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PRODUCT.PROD_ID" could not be bound.
Tesla now sells Cybertruck with 0% APR, the biggest discount ever by Hamid Ganji
Cybertruck was a highly anticipated Tesla car that could never fulfill its potential. The EV maker is now striving to increase Cybertruck sales by offering interest-free financing to buyers in the United States.
According to Tesla's announcement, US-based customers can now buy a Cybertruck with a 0 percent Annual Percentage Rate (APR). This is one of the biggest discounts Tesla has ever announced for its EV models, and it is given to buyers who choose Tesla's $8,000 Full Self-Driving Package. The offer is valid until the end of this month.
While the Tesla Model 3 and Model Y were already on sale with 0% APR, extending the same subsiding plan to the Cybertruck could potentially cost Tesla significant money. The Tesla Cybertruck with Self-Driving Package currently costs around $88,000. A 0 percent APR means the automaker loses around $10,000 in the financing process, which could affect its bottom line.
Offering financial incentives to buyers is a common practice among automakers. However, Cybertruck sales have declined significantly over the past year, exceeding its US inventory to over 10,000 units in May.
In 2019, after Cybertruck's announcement, Tesla claimed it had received over 1 million requests for its latest EV. At the time, the automaker said each Cybertruck costs $39,900 and makes it to the market in 2021. However, the EV truck finally reached the market in 2023 with a $60,990 price tag.
Also, the tri-motor version of Cybertruck was advertised with a range of 500 miles, but this version arrived at the market with a range of 320 miles.
Following Elon Musk's controversies in the Department of Government Efficiency, Tesla sales dwindled worldwide, and even many people decided to boycott the EV maker. Tesla's report for Q1 2025 shows the company's profit has declined by 71 percent.
Also, after Elon Musk's recent faceoff with US President Donald Trump, Tesla stock fell 14 percent, leading to a $152 billion loss in market cap.
Recent Achievements
EdwardFranciscoVilla earned a badge One Month Later
Question
Pc_Madness
*sigh* Can someone explain to me where I'm going wrong here. :( Basically its an order system. Products have Colours and electrical specs which define the voltage. Distributors have access to certain Products. I need to find all of the Products in a certain category. Now when you make an Order, an entry is put into OrdProd (a copy of the price, prod id and quantity etc). This means prices can change over time and not effect older orders. Obviously not all Products have been ordered, so there may not be a row in OrdProd...so for this I need a Left join on OrdProd yeah?
Atm the page is alittle slow loading so I was hoping I could cut out these two sub queries to improve performance abit (this query can return 100 odd rows in a single hit or can sometimes be run multiple times for about 10 rows each). I dunno, performance on my lowly little Macbook isn't too bad, but on the server it can be quite a few seconds so perhaps its entirely a server issue.
So, the SQL,
if I replace the top few lines with this,
SQL Express says,
Msg 207, Level 16, State 1, Line 4
Invalid column name 'PROD_ID'.
Am I making some noob mistake?
If I do PRODUCT.PROD_ID I get
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "PRODUCT.PROD_ID" could not be bound.
*grumble* Any ideas?
Link to comment
https://www.neowin.net/forum/topic/672492-sql-help-with-left-joins/Share on other sites
8 answers to this question
Recommended Posts