Solved

SQL Query help......

Posted on 2014-01-14
10
238 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
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 11

Expert Comment

by:tel2
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
Comment Utility
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:ScottPletcher
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
yes that did work!  thank you
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now