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

Eliminating a Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 11:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:54 PM
Points: 157, Visits: 221
A query question...the data below has duplicate RES numbers where one of the records for a dup RES# is flagged in the CALLED field as "YES" where other instances are flagged "NO". What I need to do is query this data so that any RES which has it's called value set to YES results in no instance of that RES being returned in the final data set. Any thoughts on this would be greatly appreciated


RES SEQUENCE Called
R001020460 33075 No
R001020868 23534 No
R001020869 22731 No
R001020869 22730 Yes
R001020870 23174 No
R001020870 23175 Yes
R001020873 22962 No
R001020873 22961 Yes
R001021018 22309 No
R001021018 22310 Yes
R001021020 22551 No
R001021020 22552 No
R001021023 22218 No
R001021023 22220 No
R001021023 22219 No
R001021024 22867 No
R001021024 22866 No
R001021024 22865 Yes
R001021025 23471 No
R001021027 22938 No
R001021027 22940 No
R001021027 22939 No
R001021029 22829 No
R001021029 22828 Yes
Post #1532205
Posted Friday, January 17, 2014 11:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 12,004, Visits: 11,035
No idea what your table name is called or what you are really trying to do here but maybe something like this? (Assuming your table name is #Something)

Select *
from #Something
where RES in
(
select RES
from #Something
group by RES
having MAX(Called) = 'No'
)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1532215
Posted Friday, January 17, 2014 12:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:54 PM
Points: 157, Visits: 221
If I want to use another query in MSAccess as the target of the code you sent instead of pointing it at a table in the database... (query name "remaining".. for example... can I do that?
Post #1532221
Posted Friday, January 17, 2014 12:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:41 AM
Points: 139, Visits: 509
Another method if you are spooked by using a subquery is to create a query which returns all records which have a Yes. Then join that query to the table where your records are and create a new query which gives you the set of records where there is no record returned in the first query by using an outer join. Many Access users find that to be an easier concept as they often work in the designer rather than actually writing SQL statements.

And in answer to your latest question, yes you can do that. In fact that is how the duplicates query in older versions of Access is created. That's the subquery I mentioned above.


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1532222
Posted Friday, January 17, 2014 12:30 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 22,518, Visits: 30,269
Sean Lange (1/17/2014)
No idea what your table name is called or what you are really trying to do here but maybe something like this? (Assuming your table name is #Something)

Select *
from #Something
where RES in
(
select RES
from #Something
group by RES
having MAX(Called) = 'No'
)



Or, in T-SQL, you could do this:


Select s1.* -- really should list the columns returned individually
from #Something s1
where not exists(select 1 from #Something s2 where s1.RES = s2.res and s2.Called = 'Yes')





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 #1532235
Posted Friday, January 17, 2014 12:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:54 PM
Points: 157, Visits: 221
Your ingenious method returns the dataset I need.. but doesn't allow me to update any data in the resulting dataset. What info can I provide to help with sorting that issue out?
Post #1532237
Posted Friday, January 17, 2014 1:01 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 22,518, Visits: 30,269
SQL33 (1/17/2014)
Your ingenious method returns the dataset I need.. but doesn't allow me to update any data in the resulting dataset. What info can I provide to help with sorting that issue out?


Well, hard to say since you didn't say anything regarding this in your original post.

Would help if you provided the entire scenario upfront rather than providing things piece meal. You should read the first article I reference below in my signature block. Follow the instructions in that article regarding what you should post and how you should post it to get the best possible answer(s) to your question(s).



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 #1532247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse