SQL Finding Multiple Values

Question

I have a table with fields named SVCode and LocationCode. I want to be able to focus on one all of the SVCodes listed in the column and check to see if their LocationCode is different.

Example:

LocationCode:         

107654403      
107654403    
107653802   
107653802   
107656502  
126515001  
128030852  
126515001

SVCode:  

STN10  
STN10  
STN10  
STN10  
STN10  
STN10  
GIN04      
GIN04  
GIN04

Each row matches up. LocationCode 107654403 with STN10 and so forth.

I want to basically create a new column called MultipleLocations and if a SVCode has more than one distinct Location code then set the MultipleLocation column = 1 else 0

Any help on doing so?

Are LocationCode and SVCode columns in different tables? If so, how do you know which one corresponds with which? Also, can you provide an example of what the output should be for your sample data above? — mellamokb
– mellamokb, Commented Jun 25, 2013 at 16:01
I was modifying an existing database to add a new multiplelocations column in. I have two tables, one named dbo.IndxServ and one named dbo.Service. Assuming i need to inner join IndxServ and Service on the SVCode (they both have it). dbo.IndxServ has Svcode, Description and the MultipleLocation column in it, but it is all set 0 since i havent modified anything yet. Dbo.Service has the LocationCode and the SVCode in it as well. — user659745
– user659745, Commented Jun 25, 2013 at 16:06
Could you please edit your question to show your tables structure, some of your data and the expected output? — Sylvain Leroux
– Sylvain Leroux, Commented Jun 25, 2013 at 16:26

Hart CO · Accepted Answer · 2013-06-25 16:01:19Z

1

You can use something like this to identify those with multiple Locations:

SELECT SVCode
FROM (SELECT DISTINCT LocationCode, SVCode
      FROM Table
      )sub
GROUP BY SVCode
HAVING COUNT(*) >1

answered Jun 25, 2013 at 16:01

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grim... · Accepted Answer · 2013-06-25 16:02:53Z

0

Taking some liberties with your column names, but

SELECT l.*, s.*, IF(COUNT(s.id) > 1,1,0) AS multipleLocation FROM LocationCode l
INNER JOIN SVCode s ON s.locationCodeId = l.id
GROUP BY l.id

answered Jun 25, 2013 at 16:02

Grim...

17k7 gold badges47 silver badges62 bronze badges

Comments

cosmos · Accepted Answer · 2013-06-25 16:06:04Z

0

select  distinct SVCode 
from Table1 t1
where exist
(select LocationCode from table1 where t1.SVCode  = SVCode and 
 LocationCode != t1.LocationCode );

this will give you list of all SVcode with multiple locations.

answered Jun 25, 2013 at 16:06

cosmos

2,3131 gold badge18 silver badges32 bronze badges

Comments

Mic1780 · Accepted Answer · 2013-06-25 16:16:08Z

0

This should work.

UPDATE t1
SET MultipleLocation = 1
WHERE SVCode IN (
    SELECT DISTINCT SVCode FROM t1
    WHERE COUNT(SVCode) > 1
    GROUP BY SVCode
)

Good luck.

answered Jun 25, 2013 at 16:16

Mic1780

1,80410 silver badges23 bronze badges

Collectives™ on Stack Overflow

SQL Finding Multiple Values

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related