Solved

SQL Query help......

Posted on 2014-01-14
10
244 Views
Last Modified: 2014-01-15
so here is my select statement:

SELECT     UserID, city, state, [KEY],   ProfileID
FROM         MoreCities
 

Each USERID may have multiple Cities (i.e. multiple rows.)
FOR EXAMPLE, this is typical data:

1159	Burnaby		British Columbia	10195	LEGACY
1160	Edmonton	Alberta			439	LEGACY
1160	Spruce Grove	Alberta			1299	I-ZZUUDDASD
1160	Beaumont	Alberta			1300	I-EEF7834KDFJA
1165	Langley		British Columbia	441	LEGACY
1165	Abbotsford	British Columbia	15488	LEGACY

Open in new window



  The ProfileID will always be either LEGACY or a unique character string.

A userID may have only 1 city assiged, or multiple cities assigned.

What I want to do is select * of all the USERID's who have at least 1 Legacy AND at least 1 non-legacy profileID.

How can I do this?

 so in the data above, 1160 gets flagged

The reason is because we need to audit our code to make sure that every userID is either all Legacy or has ProfileID's.  There should be no "mixed modes" and if there is, I want to locate them.

Thanks!!
0
Comment
Question by:arthurh88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 12

Expert Comment

by:tel2
ID: 39781374
Have a look a SQL's "HAVING" clause, Arthur.

It allows you to do things like:
SELECT ticket_type, COUNT(*) AS cnt
  FROM ticket GROUP BY ticket_type
  HAVING COUNT(*) > 2000

Open in new window

I don't know SQL Server, sorry.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39781376
In the limited testing I did, this should do the trick

Join the table to itself.

"a" contains all the profiles with LEGACY
"b" contains all the profiles not LEGACY

Any hits on an inner join means that it exists in both

 select a.userid, a.city, a.state, a.[KEY],   a.ProfileID
from MoreCities a 
join MoreCities b 
on a.userid = b.userid 
where a.profileID = 'LEGACY' 
and b.profileID <> 'LEGACY';

Open in new window


Possibly not the most efficient, but seems to work :)
0
 

Author Comment

by:arthurh88
ID: 39781380
hi sjwales.  it was very clever, but it isn't pulling in any record that has a non-legacy profileID.  I need to see the full rows of all USERID entries where there is at least 1 "LEGACY" and 1 non"LEGACY" for the userID.  WHen I ran that query, I only saw "LEGACY" results.

So if UserID 1160 has at least 1 of each, then I want to see *all*  records for USERID 1160. make sense?  

maybe this is beyond a query?  :(
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 22

Expert Comment

by:Steve Wales
ID: 39781383
Ah, OK, I think I can still make that work:

select userid, city, state, [KEY],  ProfileID
from MoreCities
where userid in 
(
select a.userid 
from MoreCities a 
join MoreCities b 
on a.userid = b.userid 
where a.profileID = 'LEGACY' 
and b.profileID <> 'LEGACY'
) as c;

Open in new window


Inner select just gives us the list of the userID's affected.

Then we select all the data from your table where it matches those userID's.

I'm sure there's a more efficient way of doing it but that should give you what you need.
0
 

Author Comment

by:arthurh88
ID: 39781393
i tried it, but got "incorrect syntax near AS".  I deleted the semicolon but it still spat back the same error.  using SQL Mgt Studio

i appreciate your efforts!  I'm truly stuck.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39781396
Actually, drop the "as c" entirely.

Just completely re-did the testing in SQL Server:

Setup:

create table MoreCities
(userid numeric, city char(20), state char(20), [key] numeric, profileid char(20));

insert into MoreCities values (1159, 'Burnaby','BC',10195,'LEGACY');
insert into MoreCities values (1160, 'Edmonton','AB',439,'LEGACY');
insert into MoreCities values (1160, 'Spruce Grove','AB',1299,'I-ZZ');
insert into MoreCities values (1160, 'Beaumont','AB',1300,'I-EE');
insert into MoreCities values (1165, 'Langley','BC',441,'LEGACY');
insert into MoreCities values (1165, 'Abbotsford','BC',15488,'LEGACY');

Open in new window


Query:

select userid, city, state, [KEY],  ProfileID
from MoreCities
where userid in 
(
select a.userid 
from MoreCities a 
join MoreCities b 
on a.userid = b.userid 
where a.profileID = 'LEGACY' 
and b.profileID <> 'LEGACY'
);

Open in new window

                                           
Results:

userid city          state KEY   ProfileID
------ ------------- ----- ----- ---------
1160   Edmonton      AB    439   LEGACY              
1160   Spruce Grove  AB    1299  I-ZZ                
1160   Beaumont      AB    1300  I-EE                

(3 row(s) affected)

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39782820
SELECT mc.*
FROM MoreCities mc
INNER JOIN (
    SELECT userid
    FROM MoreCities
    GROUP BY userid
    HAVING
        MAX(CASE WHEN ProfileID = 'Legacy' THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN ProfileID <> 'Legacy' THEN 1 ELSE 0 END) = 1
) AS userids_with_diff_ProfileIDs ON
    userids_with_diff_ProfileIDs.userid = mc.userid
0
 
LVL 32

Expert Comment

by:awking00
ID: 39783034
select userid, count(*) from
(select userid, row_number() over (partition by userid order by profileid) rn
 from MoreCities where Profileid = 'LEGACY'
 union all
 select userid, row_number() over (partition by userid order by profileid) rn
 from MoreCities where Profileid <> 'LEGACY') as x
where rn = 1
group by userid
having count(*) > 1;
0
 

Author Closing Comment

by:arthurh88
ID: 39783492
yes that did work!  thank you
0
 
LVL 32

Expert Comment

by:awking00
ID: 39783605
Just for your edification, you could have used my query as a common table expression and selected all of the records from morecities where the userid exists in the expression -
with cte as
(select userid, count(*) from
 (select userid, row_number() over (partition by userid order by profileid) rn
  from MoreCities where Profileid = 'LEGACY'
  union all
  select userid, row_number() over (partition by userid order by profileid) rn
  from MoreCities where Profileid <> 'LEGACY')
 where rn = 1
 group by userid
 having count(*) > 1)
select * from morecities
where userid in
(select userid from cte);
Just another way to do it.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question