MySQL - Get results that don't match [duplicate]

Tags: php mysql
Question!

I am trying to build a query that will return all rows that's not like a query.

I just cant get it to work.

The query is:

SELECT  *
FROM  EmailHistory
WHERE Subject not like
(
SELECT Date, Subject, Sender, Checks.Check FROM EmailHistory JOIN Checks ON EmailHistory.Subject LIKE CONCAT(  '%', Checks.Check,  '%' )
)

So basically I want to get all rows from EmailHistory, that I do not get when running:

SELECT Date, Subject, Sender, Checks.Check FROM EmailHistory JOIN Checks ON EmailHistory.Subject LIKE CONCAT(  '%', Checks.Check,  '%' ) 

I get the following error when I try to run the query: #1241 - Operand should contain 1 column(s)

The query

SELECT Date, Subject, Sender, Checks.Check FROM EmailHistory JOIN Checks ON EmailHistory.Subject LIKE CONCAT(  '%', Checks.Check,  '%' ) 

is working as it should, I get query of all queries where the subject matches the fields that I check from the table Checks.



Answers

You can't use LIKE to match with a table. The nested query returns a table with multi columns.

You are trying to match the column 'Subject' with a table.

The query you need is:

SELECT  *
FROM  EmailHistory
WHERE Subject NOT IN
(
SELECT Subject FROM EmailHistory JOIN Checks ON EmailHistory.Subject LIKE CONCAT(  '%', Checks.Check,  '%' )
)
By : Mojtaba


This can be done, please see below for a very simple (and rough - rotation does not work properly!) example, which allows you to update the size of the table view by entering a number in the text field and resetting with a button.

import UIKit

class ViewController: UIViewController {

    var tableViewController : FlexibleTableViewController!
    var textView : UITextView!
    var button : UIButton!
    var count : Int! {
        didSet {
            self.refreshDataSource()
        }
    }
    var dataSource : [Int]!
    let rowHeight : CGFloat = 50

    override func viewDidLoad() {
        super.viewDidLoad()

        // Configure

        self.tableViewController = FlexibleTableViewController(style: UITableViewStyle.plain)

        self.count = 10
        self.tableViewController.tableView.backgroundColor = UIColor.red

        self.textView = UITextView()
        self.textView.textAlignment = NSTextAlignment.center
        self.textView.textColor = UIColor.white
        self.textView.backgroundColor = UIColor.blue

        self.button = UIButton()
        self.button.setTitle("Reset", for: UIControlState.normal)
        self.button.setTitleColor(UIColor.white, for: UIControlState.normal)
        self.button.backgroundColor = UIColor.red
        self.button.addTarget(self, action: #selector(self.updateTable), for: UIControlEvents.touchUpInside)

        self.layoutFrames()

        // Assemble
        self.view.addSubview(self.tableViewController.tableView)
        self.view.addSubview(self.textView)
        self.view.addSubview(self.button)
    }

    override func didReceiveMemoryWarning() {
        super.didReceiveMemoryWarning()
        // Dispose of any resources that can be recreated.
    }

    func refreshDataSource() -> Void {
        if let _ = self.dataSource {
            if !self.dataSource.isEmpty {
                self.dataSource.removeAll()
            }
        }
        else
        {
            self.dataSource = [Int]()
        }

        for count in 0..<self.count {
            self.dataSource.append(count)
        }

        self.tableViewController.dataSource = self.dataSource
        self.tableViewController.tableView.reloadData()
        if let _ = self.view {
            self.layoutFrames()
            self.view.setNeedsDisplay()
        }
    }

    func updateTable() -> Void {
        guard let _ = self.textView.text else { return }
        guard let validNumber = Int(self.textView.text!) else { return }

        self.count = validNumber
    }

    func layoutFrames() -> Void {

        if self.tableViewController.tableView != nil {
            self.tableViewController.tableView.frame = CGRect(origin: CGPoint(x: self.view.frame.width / 2 - 100, y: 100), size: CGSize(width: 200, height: CGFloat(self.dataSource.count) * self.rowHeight))
            NSLog("\(self.tableViewController.tableView.frame)")
        }

        if self.textView != nil {
            self.textView.frame = CGRect(origin: CGPoint(x: 50, y: 100), size: CGSize(width: 100, height: 100))
        }

        if self.button != nil {
            self.button.frame = CGRect(origin: CGPoint(x: 50, y: 150), size: CGSize(width: 100, height: 100))
        }
    }
}

class FlexibleTableViewController : UITableViewController {

    var dataSource : [Int]!

    override init(style: UITableViewStyle) {
        super.init(style: style)
    }

    required init?(coder aDecoder: NSCoder) {
        fatalError("init(coder:) has not been implemented")
    }

    override func numberOfSections(in tableView: UITableView) -> Int {
        return 1
    }

    override func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
        return self.dataSource.count
    }

    override func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {
        self.tableView.register(UITableViewCell.self, forCellReuseIdentifier: "cell")

        let cell = tableView.dequeueReusableCell(withIdentifier: "cell") ?? UITableViewCell()

        cell.frame = CGRect(origin: CGPoint(x: 10, y: 5), size: CGSize(width: 180, height : 40))
        cell.backgroundColor = UIColor.green

        return cell
    }

}

Whether it is a good idea or not, is, as has been pointed out, another question! Hope that helps!

By : Sparky


The majority of your concerns seem to boil down to either misuse or misunderstanding.

  • much bigger codesize

    This is usually a result of properly respecting both the Single Responsibility Principle and the Interface Segregation Principle. Is it drastically bigger? I suspect not as large as you claim. However, what it is doing is most likely boiling down classes to specific functionality, rather than having "catch-all" classes that do anything and everything. In most cases this is a sign of healthy separation of concerns, not an issue.

  • ravioli-code instead of spaghetti-code

    Once again, this is most likely causing you to think in stacks instead of hard-to-see dependencies. I think this is a great benefit since it leads to proper abstraction and encapsulation.

  • slower performance Just use a fast container. My favorites are SimpleInjector and LightInject.

  • need to initialize all dependencies in constructor even if the method I want to call has only one dependency

    Once again, this is a sign that you are violating the Single Responsibility Principle. This is a good thing because it is forcing you to logically think through your architecture rather than adding willy-nilly.

  • harder to understand when no IDE is used some errors are pushed to run-time

    If you are STILL not using an IDE, shame on you. There's no good argument for it with modern machines. In addition, some containers (SimpleInjector) will validate on first run if you so choose. You can easily detect this with a simple unit test.

  • adding additional dependency (DI framework itself)

    You have to pick and choose your battles. If the cost of learning a new framework is less than the cost of maintaining spaghetti code (and I suspect it will be), then the cost is justified.

  • new staff have to learn DI first in order to work with it

    If we shy away from new patterns, we never grow. I think of this as an opportunity to enrich and grow your team, not a way to hurt them. In addition, the tradeoff is learning the spaghetti code which might be far more difficult than picking up an industry-wide pattern.

  • a lot of boilerplate code which is bad for creative people (for example copy instances from constructor to properties...)

    This is plain wrong. Mandatory dependencies should always be passed in via the constructor. Only optional dependencies should be set via properties, and that should only be done in very specific circumstances since oftentimes it is violating the Single Responsibility Principle.

  • We do not test the entire codebase, but only certain methods and use real database. So, should Dependency Injection be avoided when no mocking is required for testing?

    I think this might be the biggest misconception of all. Dependency Injection isn't JUST for making testing easier. It is so you can glance at the signature of a class constructor and IMMEDIATELY know what is required to make that class tick. This is impossible with static classes since classes can call both up and down the stack whenever they like without rhyme or reason. Your goal should be to add consistency, clarity, and distinction to your code. This is the single biggest reason to use DI and it is why I highly recommend you revisit it.

By : David L


This video can help you solving your question :)
By: admin