September 2, 2013 at 2:32 am
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
September 2, 2013 at 2:52 am
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
September 2, 2013 at 3:01 am
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/
September 2, 2013 at 3:27 am
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
September 2, 2013 at 3:33 am
babak3334000 (9/2/2013)
MrciYou 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.
September 2, 2013 at 3:52 am
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.
September 2, 2013 at 4:15 am
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)
September 2, 2013 at 4:23 am
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.
September 2, 2013 at 5:27 am
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)
September 2, 2013 at 5:48 am
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.
September 2, 2013 at 6:48 am
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)
September 2, 2013 at 7:33 am
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.
September 5, 2013 at 5:06 am
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.
September 5, 2013 at 5:45 am
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
September 5, 2013 at 5:54 am
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) > 0AND 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