Problems with inner join in stored proc

I have a stored procedure that retrieves data from a number of tables. See below snippet of code. I need to retrieve all records from tbl_customer regardless of whether they appear in the tbl_vehicle or tbl_vehiclesupplment. my code throws up an error i havent seen before "ERROR 1013: Tables or functions 'tbl_vehicle' and 'tbl_vehicle' have the same exposed names. Use correlation names to distinguish them"


select distinct tbl_customer.ccd_id, title,forename,surname,
companyname,address_l1,address_l2, address_l3,address_l4,address_l5,town,county,postcode,
make,model from
tbl_customer
left outer join tbl_vehicle on tbl_customer.ccd_id=tbl_vehicle.ccd_id,
tbl_vehicle
left outer join tbl_vehiclesupplement on tbl_vehicle.vehicle_id=tbl_vehiclesupplement.vehicle_id

Any ideas?

You have a syntax error. It causes an extra join for table tbl_vehicle.


select distinct tbl_customer.ccd_id, title,forename,surname,
companyname,address_l1,address_l2, address_l3,address_l4,address_l5,town,county,postcode,
make,model from
tbl_customer
left outer join tbl_vehicle
on tbl_customer.ccd_id=tbl_vehicle.ccd_id
, tbl_vehicle
left outer join tbl_vehiclesupplement on tbl_vehicle.vehicle_id=tbl_vehiclesupplement.vehicle_id
Bambola.

U try with the following query

select distinct tbl_customer.ccd_id, title,forename,surname,
companyname,address_l1,address_l2, address_l3,address_l4,address_l5,town,county,postcode,
make,model from
tbl_customer
right outer join tbl_vehicle on tbl_customer.ccd_id=tbl_vehicle.ccd_id
left outer join tbl_vehicle left outer join tbl_vehiclesupplement on tbl_vehicle.vehicle_id=tbl_vehiclesupplement.vehicle_id

For more info ref outer joins - From tsql in Sql server books online.

Thanks.



Rushendra

I agree with Bambola. The query should be
select distinct tbl_customer.ccd_id, title,forename,surname,
companyname,address_l1,address_l2, address_l3,address_l4,address_l5,town,county,postcode,
make,model from
tbl_customer
left outer join tbl_vehicle on tbl_customer.ccd_id=tbl_vehicle.ccd_id
left outer join tbl_vehiclesupplement on tbl_vehicle.vehicle_id=tbl_vehiclesupplement.vehicle_id
HTH.

Gaurav

Also be sure to check that you really need the distinct, as performance will be improved if you c an remove it without affecting the result set.