SQL Server Performance

What am i doing wrong here ??? please help me with t-sql

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by raagi2000, Feb 29, 2008.

  1. raagi2000 New Member

    here i am trying to get the count of both match ((substring(aecprda_1.upc_1,1,11) =
    substring(z.upc,1,11) + (AECPRDA_1.product_id = z.vendorcode)... probably AND won't do the trick.
    i believe my below query (3) is wrong. what changes should i make to get both the match and continue further.
    3 should be atleast greater than (1) or (2)1. select count (*) FROM ((select * from aecprda whereAECPRDA
    .sales_cat_cd in ('02','10') and(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
    ) AS AECPRDA_1
    left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on AECPRDA_1.product_id = z.vendorcodeand z.Vendorname = N'Alliance'
    LEFT OUTER JOIN AECMCAT aecmcat_c3
    ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (only the first match)
    2. select count (*) FROM ((select * from aecprda whereAECPRDA
    .sales_cat_cd in ('02','10') and(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
    ) AS AECPRDA_1
    left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on substring(aecprda_1.upc_1,1,11) =substring(z.upc,1,11) and z.Vendorname = N'Alliance'
    LEFT OUTER JOIN AECMCAT aecmcat_c3
    ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 2183 (only the second match)
    3. select count (*) FROM ((select * from aecprda whereAECPRDA
    .sales_cat_cd in ('02','10') and(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
    ) AS AECPRDA_1
    left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on ((substring(aecprda_1.upc_1,1,11) =
    substring(z.upc,1,11)) and (AECPRDA_1.product_id = z.vendorcode) )and z.Vendorname = N'Alliance'
    LEFT OUTER JOIN AECMCAT aecmcat_c3
    ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (1st & 2nd match.. i expect the count to be higher than 2)
    what i am doing wrong here...
  2. Adriaan New Member

    In natural language, you can say "How many blue and red cars are in the parking lot?" In propositional logic (like SQL) the question is "How many cars in the parking lot are blue or red?"
    If you put AND between two conditions, then both conditions must be true. Use OR if it is sufficient when at least one of the two conditions is true.
    It is weird that you should have a problem with these basic concepts when you are already using joins and derived tables.

Share This Page