CODEWITHSUNDEEP
mysql
Sport50
Sport50

Reputation: 21

Select rows with same ID but different values

I would like to have a list from the table USERS that gives me all USERS that have the same COMPANYID but where the address 1 or address 2 not match.

| USER  |COMPANYID| ADDRESS 1     | ADDRESS 2
|     1 | A       | Street A     | 11
|     2 | A       | Street A     | 11
|     3 | B       | Street B     | 12
|     4 | B       | Street B     | 13
|     5 | C       | Street C     | 14
|     6 | C       | Street C     | 14
|     7 | C       | Street C     | 15
|     8 | D       | Street C     | 15

I would like to get all the rows where the COMPANYID exists more than one time but the address between the users in the company is not matching. This is what I would like to get:

| userID| COMPANYID| ADDRESS 1    | ADDRESS 2
|     3 | B        | Street B     | 12
|     4 | B        | Street B     | 13
|     5 | C        | Street C     | 14
|     6 | C        | Street C     | 14
|     7 | C        | Street C     | 15

I can get the first part with the query:

SELECT *FROM USERS WHERE comapnyID IN (SELECT companyID FROM USERS GROUP BY companyID HAVING COUNT(*)>1) ORDER BY companyID

But I don't want to include those where the address already matches.

Upvotes: 2

Views: 87

Answers (5)

Sport50
Sport50

Reputation: 21

This one should work:

SELECT * FROM USERS WHERE COMPANYID IN (SELECT COMPANYID FROM USERS WHERE userID!=0 GROUP BY COMPANYID HAVING COUNT()>1) AND COMPANYID NOT IN ( SELECT COMPANYID FROM USERS GROUP BY COMPANYID,address1,address2 HAVING COUNT() > 1 ) ORDER BY COMPANYID

Upvotes: 0

starko
starko

Reputation: 1149

It's difficult :)

select * from USERS where companyid in (
    select t1.companyid  from (
        SELECT n.companyid,n.address1,n.address2,
            CASE WHEN n.address1=o.address1 AND n.address2=o.address2 THEN "No Change"
            ELSE "Changed"
            END as ChangeFlag
        FROM USERS n
        LEFT OUTER JOIN USERS o ON n.companyid=o.companyid
        ) t1
    where ChangeFlag="Changed")
 order by 1,2

Upvotes: 1

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

SELECT T.* FROM
USERS T
INNER JOIN
(
  SELECT DISTINCT T1.COMPANYID FROM
  (
    SELECT COMPANYID,COUNT(*) AS CADDRESS2 
    FROM USERS 
    WHERE COMPANYID IN (SELECT COMPANYID FROM USERS GROUP BY COMPANYID HAVING COUNT(*)>1) 
    GROUP BY COMPANYID
  ) AS T1
    INNER JOIN
  (
    SELECT COMPANYID,COUNT(*) CADDRESS2 
    FROM USERS 
    WHERE COMPANYID IN (SELECT COMPANYID FROM USERS GROUP BY COMPANYID HAVING COUNT(*)>1) 
    GROUP BY COMPANYID,ADDRESS2
  ) AS T2
  ON T1.COMPANYID = T2.COMPANYID
  AND T1.CADDRESS2 != T2.CADDRESS2
) AS T4
ON T.COMPANYID = T4.COMPANYID

OUTPUT

USER    COMPANYID   ADDRESS1    ADDRESS2
3       B           Street B    12
4       B           Street B    13
5       C           Street C    14
6       C           Street C    14
7       C           Street C    15

LIVE DEMO

http://sqlfiddle.com/#!9/9a153b/35

Upvotes: 0

Satish Gupta
Satish Gupta

Reputation: 333

try this query

SELECT 
    A.*
FROM
    USERS AS A
        JOIN
    USERS AS B ON A.COMPANYID = B.COMPANYID
       and A.userId = B.userId
        AND A.ADDRESS2 <> B.ADDRESS2
        AND A.ADDRESS1 <> B.ADDRESS1

Upvotes: 0

Arunkumar Muthuvel
Arunkumar Muthuvel

Reputation: 433

try the below query

SELECT * FROM USERS WHERE comapnyID IN (SELECT COUNT(companyID) FROM USERS GROUP BY companyID HAVING COUNT(companyID)>1) group by comapnyID ORDER BY companyID

Upvotes: 0

Related Questions