• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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!!
0
arthurh88
Asked:
arthurh88
  • 3
  • 3
  • 2
  • +2
1 Solution
 
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
 
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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
 
Steve WalesSenior 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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now