CODEWITHSUNDEEP
qtsqliteqt5
Gurushant
Gurushant

Reputation: 942

SQLite query to get records with certain condition

My SQLite table name is Invoices, having columns Part Number and Manufacturer.

My problem is to query the table in such a manner that it shows only records where part number have at least 2 different unique manufacturers.

I researched the stack over flow and I tried this solution

QString Filter = "PART_NUMBER in (select PART_NUMBER FROM Invoices GROUP BY "
         "PART_NUMBER HAVING count(PART_NUMBER)>1)";
model->setFilter(Filter);
model->select();

But this solution's problem is it shows part number having same manufacturer also.

Edit:

enter image description here

In this example it should return part 2 only

Upvotes: 0

Views: 56

Answers (1)

Martin Hennings
Martin Hennings

Reputation: 16846

You need to count Manufacturer: 

select PART_NUMBER FROM Invoices GROUP BY "
     "PART_NUMBER HAVING count(MANUFACTURER)>1

Ok, so you're saying that your data looks like this:

PART_NUMBER | MANUFACTURER
      1     |      A
      2     |      A
      2     |      A (duplicate entry)
      3     |      A
      3     |      B
      4     |      A
      4     |      B

Then you'd need to select HAVING COUNT(DISTINCT(MANUFACTURER)).

In sqlite, this looks a bit more complex:

SELECT COUNT(MANUFACTURER) FROM (SELECT DISTINCT MANUFACTURER FROM Table WHERE ...);

See this blog post.

But that's more than QSqlQueryModel can do with setFilter(...).

This problem looks like a database design issue. Do you know about database normalization?

When you've normalized your tables, the problem becomes significantly simplified.

Upvotes: 2

Related Questions