CODEWITHSUNDEEP
mysqlsqldatabase
Smartelf
Smartelf

Reputation: 879

MYSQL Tuple in IN CLause with wildcard matching

I have the following problem. suppose I have a table containing an inventory of cars:

VIN, MAKE, MODEL, PRICE

AND I have another table containing various car classes( eg, compact, mid-sized, suv, etc)

MAKE, MODEL, CLASS
JEEP, NULL , SUV
FORD, EXPLORER, SUV
TOYOTA, YARIS, COMPACT
NULL, CUBE, COMPACT
...

I am wondering how can I select all cars in the database of a certain class? Perhaps, I want to know how many cars of each class exists?

I can write a query like this.

SELECT COUNT(*) FROM CARS WHERE (MAKE, MODEL) IN (SELECT MAKE, MODEL FROM CLASSES WHERE CLASS = 'SUV')

The problem here, I wont actually be able to deal with NULLs in my data. I want to say that all models of JEEP are SUVs, or any MAKE car that's called CUBE would be a compact car.

Could this be done assuming there are no conflicts? Can I have a priority set up, like All Porches are CARS except the Cayenne which is an SUV by doing something like this:

MAKE, MODEL, CLASS
PORCHE, NULL , CAR
PORCHE, CAYENNE, SUV

If this isn't possible with MySQL, is there a better DB technology out there that would be good at this. Lets assume that The CLASSES Table would Contain 50K+ Rows and The CARS Table would contain 5M+ Rows. I am looking for a fast way of performing such a query in the database, and not needing to fetch millions of rows to process in a script? Also What if its not just Make and Model, but also sub-model, engine, etc..

Also, I simplified the data quite a bit, there are 4 levels of hierarchy.

Upvotes: 0

Views: 1393

Answers (3)

kasi
kasi

Reputation: 774

Assuming that make and model cannot be both null for a given class. You can use the ifnull() function in MySQL:

select cl.class, count(*)
from cars c inner join class cl
   on c.make = ifnull(cl.make,c.make)
   and c.model=ifnull(cl.model,c.model)
group by cl.class

You may want to add an index on columns makes and model for faster access. Since classes have fewer rows using an inner join (as above) will restrict the number of rows returned.

Upvotes: 1

amphibient
amphibient

Reputation: 31308

SELECT s.class, COUNT(*) FROM car c, car_class s WHERE c.make = s.make AND c.model = s.model GROUP BY s.class

should give you something like

SUV 14 COMPACT 32 TRUCK 11 etc.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can write your query like this:

SELECT COUNT(*)
FROM CARS c join
     classes cl
     on (c.make = cl.make or cl.make is null) and
        (c.model = cl.model or cl.model is null) and
        cl.class = 'SUV'

The problem is that you might get duplicates. So the better count starts with:

select count(distinct c.vin)
. . .

This should work for the two cases you gave, for JEEP and CUBE.

Upvotes: 1

Related Questions