Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Proplem Put Filter ("And" or "Or") in Query Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 2:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
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
Post #1490544
Posted Monday, September 2, 2013 2:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:50 AM
Points: 2,262, Visits: 2,725
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’! **
Post #1490550
Posted Monday, September 2, 2013 3:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1490553
Posted Monday, September 2, 2013 3:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
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
Post #1490558
Posted Monday, September 2, 2013 3:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 23,299, Visits: 32,046
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1490562
Posted Monday, September 2, 2013 3:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:50 AM
Points: 2,262, Visits: 2,725
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’! **
Post #1490567
Posted Monday, September 2, 2013 4:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
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)
Post #1490578
Posted Monday, September 2, 2013 4:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:50 AM
Points: 2,262, Visits: 2,725
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’! **
Post #1490580
Posted Monday, September 2, 2013 5:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
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)
Post #1490589
Posted Monday, September 2, 2013 5:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:50 AM
Points: 2,262, Visits: 2,725
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’! **
Post #1490593
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse