>ELSE IF @tcSearchType='010' >/* SEARCH BY MANUFACTURER ONLY */ >SELECT TOP (100) PERCENT MAX(pk_recid) AS pk_recid, MAX(herstnr) AS herstnr, MAX(hersteller) AS hersteller, >COUNT(herstnr) AS itemcount, AVG(einst_prs) AS avg_prs, > MIN(einst_prs) AS min_prs, MAX(einst_prs) AS max_prs, MAX(art_bez1) AS art_bez1, MAX(ean) AS ean, > (SELECT COUNT(*) AS cnt > FROM dbo.vbestbuy_content > WHERE (content_type = 0) AND (herstnr = MAX(dbo.vbestbuy.herstnr))) AS picture_count, > (SELECT COUNT(*) AS cnt > FROM dbo.vbestbuy_content AS vbestbuy_content_1 > WHERE (content_type = 1) AND (herstnr = MAX(dbo.vbestbuy.herstnr))) AS pdf_count >FROM dbo.vbestbuy >WHERE (dbo.vbestbuy.manufacturer_pk_id=@tnLieferantId AND @tnLieferantId IS NOT NULL) >GROUP BY hestnr_ean >ORDER BY herstnr >>
>ELSE IF @tcSearchType='001' >/* SEARCH FULLTEXTSEARCH ONLY*/ >SELECT TOP (100) PERCENT MAX(pk_recid) AS pk_recid, MAX(herstnr) AS herstnr, MAX(hersteller) AS hersteller, >COUNT(herstnr) AS itemcount, AVG(einst_prs) AS avg_prs, > MIN(einst_prs) AS min_prs, MAX(einst_prs) AS max_prs, MAX(art_bez1) AS art_bez1, MAX(ean) AS ean, > (SELECT COUNT(*) AS cnt > FROM dbo.vbestbuy_content > WHERE (content_type = 0) AND (herstnr = MAX(dbo.vbestbuy.herstnr))) AS picture_count, > (SELECT COUNT(*) AS cnt > FROM dbo.vbestbuy_content AS vbestbuy_content_1 > WHERE (content_type = 1) AND (herstnr = MAX(dbo.vbestbuy.herstnr))) AS pdf_count >FROM dbo.vbestbuy >WHERE (CONTAINS(dbo.vbestbuy.fulltextsearch,@tcSearchString) ) >GROUP BY hestnr_ean >ORDER BY herstnr >