mysql5 error

sd1

hi all
maybe some one can help
i have this query:

SELECT cars.CarId, cars.MarketPrice, cars.EndTestDate, cars.CarPlate, cars.CustomerId, cars.MerchantPrice, caryears.YaerValue, carsubmodels.SubModelName, carmodels.ModelName, manufactures.ManufactureName, cars.PriceForSale, cars.EndTestDate, cars.Km, cars.Auto, cars.OunnerNo, cars.BuyingDate, cars.Date, cars.SalesDate, cars.SalesPrice, carstatus.carstatusName, carengineval.EngineVal, cars.Sales, colors.ColorName
FROM carstatus
RIGHT JOIN (

(
manufactures
INNER JOIN carmodels
USING ( ManufactureId )
)
INNER JOIN (
carsubmodels
INNER JOIN (
caryears
RIGHT JOIN (
colors
RIGHT JOIN cars
USING ( ColorId )
)
USING ( YearId )
)
USING ( submodelId )
)
USING ( ModelId )
)
USING ( CarStatusId )
INNER JOIN carengineval ON carengineval.EngineValID = cars.CarEngineVal
AND cars.Sales =0
LIMIT 0 , 30

and i get this error:

MySQL said: #1052 - Column 'Priority' in from clause is ambiguous

i have the "priority" field in some of the tables listed in the query but i don't call the field - so why am i getting this error?
i use mysql 5.0.27-community-nt

Stryker250

firstly you are trying to select a lot of columns from 4 different tables. but you are only specifying one table in the FROM clause (carstatus)...you need to specify all the tables that you want to extract info from.

mysql picks up a something.somethingelse as tableName.columnName.

so to solve your problem add all the table names in the FROM clause next to carstatus as follows

$query = "SELECT cars.CarId, cars.MarketPrice, cars.EndTestDate, cars.CarPlate, cars.CustomerId, cars.MerchantPrice, caryears.YaerValue, carsubmodels.SubModelName, carmodels.ModelName, manufactures.ManufactureName, cars.PriceForSale, cars.EndTestDate, cars.Km, cars.Auto, cars.OunnerNo, cars.BuyingDate, cars.Date, cars.SalesDate, cars.SalesPrice, carstatus.carstatusName, carengineval.EngineVal, cars.Sales, colors.ColorName
FROM carstatus, cars, caryears, carsubmodel, carmodels, manufacturer, carengineval, colors
RIGHT JOIN (

(
manufactures
INNER JOIN carmodels
USING ( ManufactureId )
)
INNER JOIN (
carsubmodels
INNER JOIN (
caryears
RIGHT JOIN (
colors
RIGHT JOIN cars
USING ( ColorId )
)
USING ( YearId )
)
USING ( submodelId )
)
USING ( ModelId )
)
USING ( CarStatusId )
INNER JOIN carengineval ON carengineval.EngineValID = cars.CarEngineVal
AND cars.Sales =0
LIMIT 0 , 30";

and also for anything i missed

sd1

hi styker
thanks for your help

i came up with that query:
SELECT DISTINCT cars.inSale, cars.CarId, cars.MarketPrice, cars.EndTestDate, cars.CarPlate, cars.CustomerId, cars.MerchantPrice, caryears.YaerValue, carsubmodels.SubModelName, carmodels.ModelName, manufactures.ManufactureName, cars.PriceForSale, cars.EndTestDate, cars.Km, cars.Auto, cars.OunnerNo, cars.BuyingDate, cars.Date, cars.SalesDate, cars.SalesPrice, cars.KeyNum, carstatus.carstatusName, carengineval.EngineVal, cars.Sales, cars.Remarks, colors.ColorName
FROM carstatus, cars, caryears, carsubmodel, carmodels, manufacturer, carengineval, colors
RIGHT JOIN (

but got this error: "#1066 - Not unique table/alias: 'carmodels' "

what is that?

Stryker250

Not 100% sure as to what the problem is but try this
and if you want you can check this link out for more info.

it should explain it a little better. but what ive deduced is that its because you cant use a table name twice in a query which is why you give it an alias...ie something AS something_else.

$query = "SELECT DISTINCT 
	cars.inSale, 
	cars.CarId, 
	cars.MarketPrice, 
	cars.EndTestDate, 
	cars.CarPlate, 
	cars.CustomerId, 
	cars.MerchantPrice, 
	caryears.YaerValue, 
	carsubmodels.SubModelName, 
	carmodels.ModelName, 
	manufactures.ManufactureName, 
	cars.PriceForSale, 
	cars.EndTestDate, 
	cars.Km, 
	cars.Auto, 
	cars.OunnerNo, 
	cars.BuyingDate, 
	cars.Date, 
	cars.SalesDate, 
	cars.SalesPrice, 
	cars.KeyNum, 
	carstatus.carstatusName, 
	carengineval.EngineVal, 
	cars.Sales, 
	cars.Remarks, 
	colors.ColorName
	FROM 
	carstatus, 
	cars, 
	caryears, 
	carsubmodel, 
	carmodels, 
	manufactures, 
	carengineval, 
	colors
RIGHT JOIN (

(
manufactures
INNER JOIN carmodels AS car_models
USING ( ManufactureId )
)
INNER JOIN (
carsubmodels
INNER JOIN (
caryears
RIGHT JOIN (
colors
RIGHT JOIN cars
USING ( ColorId )
)
USING ( YearId )
)
USING ( submodelId )
)
USING ( ModelId )
)
USING ( CarStatusId )
INNER JOIN carengineval ON carengineval.EngineValID = cars.CarEngineVal
LEFT JOIN images ON cars.CarID = images.CarID
WHERE cars.CustomerId =7
AND cars.Sales =0
LIMIT 0 , 30";

sd1

well, after solving the unique problem - it came back to the ambigous filed problem

here is my query:

SELECT DISTINCT cars.inSale, cars.CarId, cars.MarketPrice, cars.EndTestDate, cars.CarPlate, cars.CustomerId, cars.MerchantPrice, cars.PriceForSale, cars.EndTestDate, cars.Km, cars.Auto, cars.OunnerNo, cars.BuyingDate, cars.Date, cars.SalesDate, cars.SalesPrice, cars.KeyNum, carstatus.carstatusName, carengineval.EngineVal, cars.Sales, cars.Remarks, caryears.YaerValue, carsubmodels.SubModelName, carmodels.ModelName, manufactures.ManufactureName, colors.ColorName
FROM carstatus, cars AS cr1, caryears AS cyr1, carsubmodels AS submdl, carmodels AS cmdl1, manufactures, carengineval AS ceng1, colors
RIGHT JOIN (

(
manufactures AS manu1
INNER JOIN carmodels AS car_models
USING ( ManufactureId )
)
INNER JOIN (
carsubmodels AS sub1
INNER JOIN (
caryears AS carye1
RIGHT JOIN (
colors AS colo1
RIGHT JOIN cars
USING ( ColorId )
)
USING ( YearId )
)
USING ( submodelId )
)
USING ( ModelId )
)
USING ( CarStatusId )
INNER JOIN carengineval ON carengineval.EngineValID = cars.CarEngineVal
LEFT JOIN images ON cars.CarID = images.CarID
WHERE cars.CustomerId =7
AND cars.Sales =0
LIMIT 0 , 30

and here is the error:
#1052 - Column 'Priority' in from clause is ambiguous

Stryker250

what do you get if you try this?

<?
$query = "SELECT DISTINCT cars.inSale, cars.CarId, cars.MarketPrice, cars.EndTestDate, cars.CarPlate, cars.CustomerId, cars.MerchantPrice, cars.PriceForSale, cars.EndTestDate, cars.Km, cars.Auto, cars.OunnerNo, cars.BuyingDate, cars.Date, cars.SalesDate, cars.SalesPrice, cars.KeyNum, carstatus.carstatusName, carengineval.EngineVal, cars.Sales, cars.Remarks, caryears.YaerValue, carsubmodels.SubModelName, carmodels.ModelName, manufactures.ManufactureName, colors.ColorName
FROM carstatus, cars, caryears, carsubmodels, carmodels, manufactures, carengineval, colors
RIGHT JOIN (

(
manufactures AS manu1
INNER JOIN carmodels AS car_models
USING ( ManufactureId )
)
INNER JOIN (
carsubmodels AS sub1
INNER JOIN (
caryears AS carye1
RIGHT JOIN (
colors AS colo1
RIGHT JOIN cars
USING ( ColorId )
)
USING ( YearId )
)
USING ( submodelId )
)
USING ( ModelId )
)
USING ( CarStatusId )
INNER JOIN carengineval ON carengineval.EngineValID = cars.CarEngineVal
LEFT JOIN images ON cars.CarID = images.CarID
WHERE cars.CustomerId =7
AND cars.Sales =0
LIMIT 0 , 30";

?>

sd1

i get this:
Not unique table/alias: 'cars'

Stryker250

alright..try this. the last 4 cars that i changed to car you might have to change back if it does not work.

<?
$query = "SELECT DISTINCT cars.inSale, cars.CarId, cars.MarketPrice, cars.EndTestDate, cars.CarPlate, cars.CustomerId, cars.MerchantPrice, cars.PriceForSale, cars.EndTestDate, cars.Km, cars.Auto, cars.OunnerNo, cars.BuyingDate, cars.Date, cars.SalesDate, cars.SalesPrice, cars.KeyNum, carstatus.carstatusName, carengineval.EngineVal, cars.Sales, cars.Remarks, caryears.YaerValue, carsubmodels.SubModelName, carmodels.ModelName, manufactures.ManufactureName, colors.ColorName
FROM carstatus, cars, caryears, carsubmodels, carmodels, manufactures, carengineval, colors
RIGHT JOIN (

(
manufactures AS manu1
INNER JOIN carmodels AS car_models
USING ( ManufactureId )
)
INNER JOIN (
carsubmodels AS sub1
INNER JOIN (
caryears AS carye1
RIGHT JOIN (
colors AS colo1
RIGHT JOIN cars AS car
USING ( ColorId )
)
USING ( YearId )
)
USING ( submodelId )
)
USING ( ModelId )
)
USING ( CarStatusId )
INNER JOIN carengineval ON carengineval.EngineValID = car.CarEngineVal
LEFT JOIN images ON car.CarID = images.CarID
WHERE car.CustomerId =7
AND car.Sales =0
LIMIT 0 , 30";

?>

sd1

well, i think the aliases are not the problem. because when i set correct aliases they dissapear and then i get the MySQL said: #1052 - Column 'Priority' in from clause is ambiguous although i don't use this field (i have it in all of those tables)

Stryker250

that is odd....
question....is DISTINCT really what you want to use? what happens if you dont use SELECT DISTINCT but just plain SELECT?

here is a link for the DISTINCT info

Roger_Ramjet

You are getting that error because you are not writing your joins correctly. Personally I've never seen anything like this query and don't know why you are using it or why other people are encouraging you to continue with it.

Start from scratch, or from your select list and use explicit joins between the tables in the FROM clause in the form of

FROM cars INNER JOIN carstatus USING(CarStatusID)
INNER JOIN carmodels ON cars.ModelID=carmodels.ModelID
etc

Just be as obvious and explicit as possible - avoid all short-cuts like using commas instead of INNER JOIN and try reading the manual about the changes in ver 5

TRouble with early versions of mysql was that they allowed very sloppy sql syntax that would not wash in any other db, so if you learnt mysql first you got into all sorts of funny habits that are now going to trip you up in the newer versions.
Start this query over from scratch and you'll get it sorted.

PS your error is precisely because you are joining with commas first and mysql gets confused as to which column to join on because the same column name is in every table so when it gets to the third table it has 2 choices from the previous join.