Proplem Put Filter ("And" or "Or") in Query

  • Table With two Feild Name,Tools

    Example Table :

    1-Mike Walker

    2-Mike Cane

    3-Steve Walker

    4-Mitchell Cane

    I want someone who uses both(Walker and Cane ) to exhibit = Mike

    Query : Select Name From TableTools WHERE (Tools = N'Walker') AND (Tools = N'Cane')

    Result=0

    With By Or

    Result=4

    How to write this query

    Plz Help Me

  • Give this a try (included code to create sample data):

    create table #TableTools (id int identity(1,1), name varchar(15), tools varchar(15))

    insert into #TableTools values

    ('mike', 'walker')

    ,('mike', 'cane')

    ,('steve', 'walker')

    ,('mitchel', 'cane')

    ,('dave', 'cane')

    ,('mitchel', 'none')

    -- actual query

    SELECT NAME

    FROM #TableTools

    WHERE tools = 'walker' OR tools = 'cane' -- filter on two tools

    GROUP BY NAME -- only return the distinct name

    HAVING count(NAME) = 2 -- count needs to be 2 because we filter on two tools

    drop table #TableTools

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • create table #TableTools (id int identity(1,1), name varchar(15), tools varchar(15))

    insert into #TableTools values

    ('mike', 'walker')

    ,('mike', 'cane')

    ,('steve', 'walker')

    ,('mitchel', 'cane')

    ,('dave', 'cane')

    ,('mitchel', 'none')

    ;WITH CTE AS

    (

    SELECT NAME, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME)NOS

    FROM #TableTools

    WHERE Tools = 'Walker' OR Tools = 'Cane'

    )

    SELECT * FROM CTE

    WHERE NOS >1

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Mrci

    You are quite right in my Answer

    But I have a view and send it to the different conditions By Collection (Asp.net)

    I added the condition to Having, but will not display anything.

    SELECT FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode

    FROM View_Report_Payesh_Tools

    WHERE (Tools = N'Walker') OR

    (Tools = N'Cane')

    GROUP BY FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode

    HAVING (COUNT(Tools) = 2)

    I Have Two Table ,1-Personal Information,2-Tools Information

  • babak3334000 (9/2/2013)


    Mrci

    You are quite right in my Answer

    But I have a view and send it to the different conditions By Collection (Asp.net)

    I added the condition to Having, but will not display anything.

    SELECT FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode

    FROM View_Report_Payesh_Tools

    WHERE (Tools = N'Walker') OR

    (Tools = N'Cane')

    GROUP BY FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode

    HAVING (COUNT(Tools) = 2)

    What you posted above has additional columns. I would start by looking at your data. Any differences in the data you are grouping on could be part of the problem.

  • If you provide full sample data (all columns included and multiple rows that cover all situations) we can help you locate the problem.

    Like Lynn Pettis allready pointed out: the additional columns in the SELECT and GROUP BY are most likely the cause of your issue. If just a bit of data between the rows is different, these don't add up and won't reflect to the filter in the HAVING clause.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • First Table Personal Information Included Feilds :

    NationalCode(Key), FirstName, LastName, CompanyID, NCode, FatherName, nPercent,

    Second Table Tools Information Included Feilds :

    ID_Tools(key),NationalCode ,TypeTools,NameTools,PriceTools

    Third View(View_Report) Included Feilds :

    NationalCode,FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NameTools

    Now the query is (Posted by Hanshy for a table is queried) :

    SELECT FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode

    FROM View_Report

    WHERE (NameTools = N'Walker') OR

    (NameTools = N'Cane')

    GROUP BY FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode

    HAVING (COUNT(NameTools) = 2)

    Please help me know what to do (For View)

  • Thanks for providing the table definitions. We'd be better off with correct SQL statement, but it's a start. Besides the DDL we also need the data itself (posted as INSERT statements). This data (multiple rows for each table) should cover all situations so it gives us insight in your problem.

    Take a look at the first link in Lynn Pettis signature (see a few posts above). That's an article about how to post your questions and provide the required additional information to get the best answers.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Attach Sample Database (SQl 2008)

    My Query is :

    SELECT NationalCode, CompanyID, nPercent, NCode, FirstName, LastName, FatherName

    FROM View_Report

    WHERE (NameTools = N'walker') OR

    (NameTools = N'Cane')

    GROUP BY NationalCode, CompanyID, nPercent, NCode, FirstName, LastName, FatherName

    Request is consistent with the first post (Only Display Mike)

  • If you run your query from your post (today 12:15:40) on the database you attached, it will return the desired result. In the post in which you attached the database you mention almost the same query but have omitted the HAVING clause. If you add the "HAVING (COUNT(NameTools) = 2)" back again it will also return the desired result.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Hi Test This Query

    Select * From

    (

    SELECT FirstName

    , LastName

    , CompanyID

    , NCode

    , FatherName

    , nPercent

    , NationalCode

    , (select COUNT (*) From Tools_Information K Where K.NationalCode = R.NationalCode And K.NameTools = N'Cane')COUNT_cane

    , (select COUNT (*) From Tools_Information K Where K.NationalCode = R.NationalCode And K.NameTools = N'Walker')COUNT_walker

    FROM View_Report R

    GROUP BY FirstName

    , LastName

    , CompanyID

    , NCode

    , FatherName

    , nPercent

    , NationalCode

    )V

    Where (Isnull(V.COUNT_cane , 0) > 0 And Isnull(V.COUNT_walker,0) > 0)

  • Instead of selecting the specific counts by a sub-select you could also accomplish this by using the CASE statement. This will give better performance because it will be calculated within the same select on the table and not by executing another select on the table.

    SELECT

    NationalCode, CompanyID, nPercent, NCode, FirstName, LastName, FatherName

    , sum(case when NameTools = N'walker' then 1 else 0 end) as Count_walker

    , sum(case when NameTools = N'Cane' then 1 else 0 end) as Count_Cane

    FROM View_Report

    WHERE (NameTools = N'walker') OR

    (NameTools = N'Cane')

    GROUP BY NationalCode, CompanyID, nPercent, NCode, FirstName, LastName, FatherName

    HAVING (COUNT(NameTools) > 1)

    Also notice the change in the HAVING clause to accomidate the posibility of having multiple Canes and/or Walkers.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • excuseme

    Contact information table was forgotten

    Plz Download New Database Attachment

    SELECT FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode

    FROM View_Report

    WHERE (NameTools = N'Walker') OR

    (NameTools = N'Cane')

    GROUP BY FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode

    HAVING (COUNT(NameTools) = 2)

    I want someone who uses both(Walker and Cane ) to exhibit = Mike

    But it does not display anything.

  • You need to change the HAVING clause to get your desired results. With the code below you can check if both 'Walker' and 'Cane' exists at least once.

    HAVING sum(case when NameTools = N'walker' then 1 else 0 end) > 0

    AND sum(case when NameTools = N'Cane' then 1 else 0 end) > 0

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • HanShi (9/5/2013)


    You need to change the HAVING clause to get your desired results. With the code below you can check if both 'Walker' and 'Cane' exists at least once.

    HAVING sum(case when NameTools = N'walker' then 1 else 0 end) > 0

    AND sum(case when NameTools = N'Cane' then 1 else 0 end) > 0

    Thank you so much master:w00t:

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply