How to exclude the rows which have matching column values using Magento collections

[kalyanchakri]

How to exclude the rows which have matching column values using Magento collections? For example, I have a table as below

coupon_name | coupon_code
C1                   | C1

C2                   | C2

C4                   | C5
C6                   | C6

From the above table I need only the rows which don't have matching column values that is C4 as it is not matching with C5. Rest of the rows are equal to the other column value. Please help me how to do this using Magento collections or at least using sql query.

[Rakesh Jesadiya]

You can create below sql for your requirements,

select * from `tablename` where a1 != b1

Where a1 and b1 are the column name and tablename is your table name.

You can use direct sql query to use in magento 2.

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('tablename');
 
//Select Data from table
$query = 'select * from `tablename` where a1 != b';
$results = $connection->fetchAll($query);
var_dump($results);

[Sunil Patel]

Hello @kalyanchakri

If you have collection obect let's e.g collection name is $collection

$data  = $collection->getSelect()->where('column2 != ?', 'colum1');
echo "<prE>";
print_R($data->getData());

Hope it will help you.