MySQL Count(Table1.ColId) Where IN (any of Table2.Col1 ,Table2.Col2)

countMySQL

I have 2 Tables

Table 1 has Id, Name
Table 2 has Id, Col1_IdVal, Col2_IdVal,Col3_IdVal,Col4_IdVal

I would like simple solution for

Count(T1.Id) WHERE T1.Id In (T2.Col1_IdVal,T2.Col2_IdVal,T2.Col3_IdVal,T2.Col4_IdVal)

Something I have tried does not return correct result set , too few in the result :

SELECT COUNT(T1.Id) FROM Table1 As T1     
LEFT JOIN Table2 AS T2_1 ON T2_1.T2Column_1 = T1.Id     
LEFT JOIN Table2 AS T2_2 ON T2_2.T2Column_2 = T1.Id    
LEFT JOIN Table2 AS T2_3 ON T2_3.T2Column_3 = T1.Id    
LEFT JOIN Table2 AS T2_4 ON T2_4.T2Column_4 = T1.Id    
LEFT JOIN Table2 AS T2_5 ON T2_5.T2Column_5 = T1.Id;

Table 1 has Unique Values – it is Primary Key, Table 2 can have any number of values MANY. Table2 columns are not foreign keys of Table1 – they are values that must exist in Table1 but other than that – they are not FKeys.

So basically Count(Id) Where Id Value Is in T2, Cols 1..n

Best Answer

Look for

SELECT COUNT(t1.Id) FROM Table1 As t1     
{ INNER | LEFT } JOIN Table2 AS t2 ON t1.Id IN (t2.T2Column_1,
                                                t2.T2Column_2,
                                                t2.T2Column_3,
                                                t2.T2Column_4,
                                                t2.T2Column_5);

Related Solutions

Sql-server – Why aren’t primary key / foreign key matches used for joins

In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.

However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!

Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!

That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!

All this would be acceptable, if performance would be enhanced. However, that's not the case.

Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.

Therefor it is not supprising that most database vendors choose to spend their time on more important things.

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.

Best Answer

Related Solutions

Sql-server – Why aren’t primary key / foreign key matches used for joins

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

Related Question