Mysql multiple condition one column subquery

Question

I have a table with firms that rent cars, with columns like

Firms | Car 
GD1   | CAR1 
GD1   | CAR2 
GD2   | CAR3 
GD3   | CAR4

And also other one with the cars rented by the clients, like this:

Client | Car Rented
C1     | CAR1
C1     | CAR2
C1     | CAR3
C1     | CAR4
C2     | CAR4
C3     | CAR1
C3     | CAR2
C4     | CAR1
C5     | CAR2

I need to know all the clients that rent all the cars from an specific firm. The only input that I have is the firm name (for instance GD1) If I use GD1 the output should be this one:

Client
C1
C3

It may seem easy, but I'm really having trouble with this one...

Thanks for the time

rcs · Accepted Answer · 2015-11-26 02:02:57Z

1

To know whether a client rents all the car from a particular firm, you need to know how many cars owned by the firm and how many cars rented from the firm by the client. To know how many cars owned by the firm, you can get that by this query:

SELECT      Firms, COUNT(*) AS NUM_OF_CAR
FROM        FirmTable
GROUP BY    Firms

And to know how many cars rented from the firm by the client, you can get by this query:

SELECT      Client, Firms, COUNT(*) AS NUM_OF_CAR
FROM        FirmTable a
INNER JOIN  ClientTable b ON a.Car = b.Car
GROUP BY    Client, Firms

And now you need to get the client that rents all the car, meaning that the NUM_OF_CAR from the first query and the second query must match for the respective firm. Combining both queries above, you can use:

SELECT      DISTINCT Client
FROM        (
            SELECT      Firms, COUNT(*) AS NUM_OF_CAR
            FROM        FirmTable
            GROUP BY    Firms
            ) t
INNER JOIN  (
            SELECT      Client, Firms, COUNT(*) AS NUM_OF_CAR
            FROM        FirmTable a
            INNER JOIN  ClientTable b ON a.Car = b.Car
            GROUP BY    Client, Firms
            ) u
ON          t.Firms = u.Firms AND t.NUM_OF_CAR = u.NUM_OF_CAR
WHERE       t.Firms = 'GD1'

edited Nov 26, 2015 at 2:02

answered Nov 26, 2015 at 1:39

rcs

7,31713 gold badges61 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Soborno Over a year ago

Thanks, just what I need, also thanks for the two steps explanation that you edited later. Regards

E.Solicito · Accepted Answer · 2015-11-26 01:45:41Z

0

I'm also new at mysql but you can try this.

SELECT DISTINCT Client from ClientTable where CarRented=(SELECT Car from FirmTable where Firms='GD1');

answered Nov 26, 2015 at 1:45

E.Solicito

919 bronze badges

1 Comment

Soborno Over a year ago

Thanks, but have in mind that in this case the subquery will have more than 1 row, so your solution wouldn´t work...

Collectives™ on Stack Overflow

Mysql multiple condition one column subquery

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related