Simple select query, maybe? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple select query, maybe?

Author

Topic

Directlinq
Starting Member

8 Posts

Posted - 2014-08-06 : 03:08:59

Hi, really hope you can help me.
I have a table called RM_Areas with 2 columns in it, Name and Ptype. In Name there are all different names and some of the same. In Ptype there is one of the following numbers 1,2,3,4,5,6,7,8.
What I need to do is write a select query for, if there is an identical name with a ptype of 3 and 1 or 2.

example of table:-


Name		ptype
Apple		3
Banana		3
Pear		3
Banana		2
Apple		1
grape		3

Example of result:- 

Name		ptype
Apple		3
Banana		3
Banana		2
Apple		1

Here is how i need it to look after the select query.
It shows all names with a ptype of 3 and 1 or 2.

I hope this is clear ?
Thanks, any help would be greatly appreciated.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2014-08-06 : 05:30:10

Hey try this may be help you,
--Create Table #TEst ( Name nvarchar(25), ptype int)

--truncate table #Test

--Insert into #TEst Select 'Apple',3 union
--Select 'Banana',3 union
--Select 'Pear',3 union
--Select 'Banana',2 union
--Select 'Apple',1 union
--Select 'grape',3

Select * from #test
Select * from #test where name in
(Select Name from (Select row_number() over (partition by Name order by name) AS rwrNumber,* from #test ) as t
where t.rwrNumber in (2,3))

In Love... With Me!

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-08-06 : 05:55:57

Probably a cleaner example that this can be achieved but this does the job:

select a.name, a.ptype FROM
(select *
from rm_areas 
where ptype in (1,2))a 
INNER JOIN
(select * 
from rm_areas 
where ptype = 3)b ON a.name = b.name

union all

select b.name, b.ptype FROM
(select *
from rm_areas 
where ptype in (1,2))a 
INNER JOIN
(select * 
from rm_areas 
where ptype = 3)b ON a.name = b.name

Directlinq
Starting Member

8 Posts

Posted - 2014-08-06 : 07:33:45

Wow Grifter that is great. Is it possible to turn this select query into an update query? I have another column called disabled and all entry's are currently false. I would like to change all false's into true's but only on the rows displayed by the previous query you helped me with.

Thanks so much for this.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-08-06 : 10:23:28

quote:
Originally posted by Directlinq

Wow Grifter that is great. Is it possible to turn this select query into an update query? I have another column called disabled and all entry's are currently false. I would like to change all false's into true's but only on the rows displayed by the previous query you helped me with.

Thanks so much for this.

To do an update you need like a key to match such as

update x
from b
where b.y = 4

In this case we don't have direct access to a key and therefore would be a bit of pain to be able to write an update. I think it would need another piece of code to be compiled before going for an update.

I'll try and have another look but can't promise anything

G

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2014-08-06 : 19:20:10

If you are with version 2012, use lag and lead for better performance. The solution accesses the table only 1 time to get the correct result.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-07 : 01:36:08

[code]DECLARE @Sample TABLE
(
Name VARCHAR(10) NOT NULL,
pType INT NOT NULL
);

INSERT @Sample
(
Name,
pType
)
VALUES ('Apple', 3),
('Banana', 3),
('Pear', 3),
('Banana', 2),
('Apple', 1),
('grape', 3);

SELECT s.Name,
s.pType
FROM @Sample AS s
INNER JOIN (
SELECT Name
FROM @Sample
WHERE pType = 3

INTERSECT

SELECT Name
FROM @Sample
WHERE pType BETWEEN 1 AND 2
) AS w ON w.Name = s.Name
WHERE s.pType BETWEEN 1 AND 3;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources