Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
babak3334000
babak3334000
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 62
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
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 2763
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/
babak3334000
babak3334000
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 62
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24245 Visits: 37978
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.

Cool
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)
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
babak3334000
babak3334000
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 62
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)
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
babak3334000
babak3334000
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 62
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)
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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’! **
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search