SQL Query help......

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!!
arthurh88Asked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
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
 
tel2Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
arthurh88Author Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
arthurh88Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
awking00Commented:
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
 
arthurh88Author Commented:
yes that did work!  thank you
0
 
awking00Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.