MYSQL: Values of a column where two other columns have same value

MySQLselect

I have a table say table1 with three columns lets name them col1 col2 and col3. I have a suspicion that col2 and col3 have some values that are identical i.e. to say there are some rows where col2 = col3. In case, if for these rows col1 is the same I will have no problem in my application. But how to check for those where col1 = col2 but they don't point to the same col1 value.

I mean SELECT * FROM table1 WHERE col2 = col3 gives me records for col2 = col3 in same rows. what I want for each col2 value search col3 if it is same send those records

Something like this (this is pseudo SQL) but in MYSQL

SELECT DISTINCT col2 FROM table1 foreach col2 same_records = SELECT DISTINCT col1 FROM table1 WHERE col2 = col3 records UNION same_records end foreach SELECT records

Is there is a way to get these results (if they exist)? I believe that I have to use self-join on the table.

Best Answer

You mean something like

SELECT col2, col3 FROM table WHERE col2 = col3 GROUP BY col2, col3 
HAVING COUNT(DISTINCT col1) > 1;

Would show you the col2/col3 combinations where col2 = col3 but col1 differs between those rows.

Related Solutions

MySQL Query: Delete from ‘table2’ where column = ‘value’ IF column in ‘table1’ = ‘value’

Your statement seems to be correct. It could also be written like this:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
      WHERE c.characterid = ii.characterid
        AND EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 1
            )
    ) ;

One thing that may be causing this is if you have a character related to many accounts and one of them has banned = 1 while the other have banned = 0. I assume you want the deletion to happen (not with just one but) only if all the related accounts have banned = 1. We can modify the above code to:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
      WHERE c.characterid = ii.characterid
        AND EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 1
            )
        AND NOT EXISTS
            ( SELECT *
              FROM accounts AS a
              WHERE a.id = c.accountid 
                AND a.banned = 0
            )
    ) ;

or simpler to:

DELETE ii
FROM inventoryitems AS ii 
WHERE ii.itemid = 2340000
  AND EXISTS  
    ( SELECT * 
      FROM characters AS c
        JOIN accounts AS a
          ON a.id = c.accountid
      WHERE c.characterid = ii.characterid 
      HAVING MIN(a.banned) = 1
    ) ;

After the clarifications, all the above are void. The problem was that characters table does not have characterid column but only id. So the statement used by the OP was translated/parsed as:

DELETE FROM inventoryitems
WHERE characterid IN 
    (SELECT inventoryitems.characterid         -- notice this
     from characters
     WHERE accountid IN 
        (SELECT id 
         from accounts 
         WHERE banned = '1'
        )
    ) 
  AND itemid = '2340000';

which makes the subquery uncorrelated and means "delete all rows with itemid = 2340000" if there exists at least one row (any row, not necessarily related) in the accounts with banned=1"

That's one reason why it's good to always (*) write columns with their full name as tablename.columnname or tablealias.columnname (an error would have been thrown if you had done this and problem would have been solved faster.)

(*) Unless one wants this behaviour to occur, which is a rather extreme case.

Postgresql – Find all values not present for other values of other column

If I interpret your question correctly:

"Pick all rows where a has one of two given values, and b only exists in combination with one of them."

You can use any of the standard techniques laid out in the referenced answer.

Select rows where value of second column is not present in first column

Just restrict your base table to the two given a. For instance

SELECT *
FROM   tbl t1
WHERE  a IN (1,2)
AND    NOT EXISTS (
   SELECT 1
   FROM   tbl t2
   WHERE  a IN (1,2)
   AND    t2.b =  t1.b
   AND    t2.a <> t1.a
   );

SQL Fiddle

This also works with duplicates on (a,b). Multiple identical rows would be returned.

Best Answer

Related Solutions

MySQL Query: Delete from ‘table2’ where column = ‘value’ IF column in ‘table1’ = ‘value’

Postgresql – Find all values not present for other values of other column

Related Question