SQL Server Performance

Problems with inner join in stored proc

Discussion in 'General Developer Questions' started by macca1476, Jul 4, 2003.

  1. macca1476 New Member

    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?
  2. bambola New Member

    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.
  3. rushmada New Member

    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
  4. gaurav_bindlish New Member

    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
  5. Chappy New Member

    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.

Share This Page