2 tables: proprietors &lifier cars

The owner might have many cars. A vehicle could be marked as functional_offroad, functional_onroad, or both. The cars table has functional offroad and functional_onroad fields which may be set to or 1 (no or yes)

Think about the following query:

SELECT *
FROM owners
LEFT JOIN cars on cars.owner_id = owners.id
GROUP BY owners.id
ORDER BY owners.last_name

Let me return a listing of proprietors, and whether each is the owner of a an onroad or offroad vehicle, or both:

    Last Name    First Name    Has Offroad Car      Has Onroad Car
----------------------------------------------------------------------
    Smith        Todd          Yes                   No
    Smith        Tom           Yes                   Yes
    Test         Sue           No                    Yes
    Thumb        Joe           No                    No
    White        Al            Yes                   No

How do you query this? Was considering using ROLLUP, but would rather when the summary wasn't an appended row but a real area around the already arranged owner row rather.

Use:

   SELECT DISTINCT
          o.lastname,
          o.firstname,
          CASE WHEN COALESCE(y.num_offroad, 0) > 0 THEN 'yes' ELSE 'no' END AS "Has Offroad Car"
          CASE WHEN COALESCE(x.num_onroad, 0) > 0 THEN 'yes' ELSE 'no' END AS "Has Onroad Car"
     FROM OWNERS o
LEFT JOIN (SELECT c.owner_id,
                  COUNT(*) AS num_onroad
             FROM CARS c
            WHERE c.usable_onroad = 1
         GROUP BY c.owner_id) x ON x.owner_id = o.id
LEFT JOIN (SELECT c.owner_id,
                  COUNT(*) AS num_offroad
             FROM CARS c
            WHERE c.usable_offroad = 1
         GROUP BY c.owner_id) y ON y.owner_id = o.id

Utilizing a sub-query to sum all of the flags, after which checking if your are > 1 should get the job done:

SELECT last_name, first_name,
       CASE WHEN usable_offroad_count > 0 THEN 'Yes' ELSE 'No' END has_offroad_car,
       CASE WHEN usable_onroad_count > 0 THEN 'Yes' ELSE 'No' END has_onroad_car
FROM (
    SELECT owners.last_name, owners.first_name,
           SUM( cars.usable_offroad ) usable_offroad_count,
           SUM( cars.usable_onroad ) usable_onroad_count
    FROM owners
    LEFT JOIN cars on cars.owner_id = owners.id
    GROUP BY owners.id
)
ORDER BY last_name
SELECT * ,
if ( cars.usable offroad = 1 and  usable_onroad= 1 , 'Both' 
     , if( cars.usable offroad = 1 and  usable_onroad= 0  , 'Offroad' , 'Onroad')
   )  as Status
FROM owners
LEFT JOIN cars on cars.owner_id = owners.id
GROUP BY owners.id
ORDER BY owners.last_name

Do this:

SELECT
    T1.lastname,
    T1.firstname,
    T1.id in (SELECT owner_id FROM cars WHERE usable_offroad) AS `Has Offroad Car`,
    T1.id in (SELECT owner_id FROM cars WHERE usable_onroad) AS `Has Onroad Car`
FROM owners T1
ORDER BY T1.lastname, T1.firstname

Results:

'Smith', 'Todd', 1, 0
'Smith', 'Tom', 1, 1
'Test', 'Sue', 0, 1
'Thumb', 'Joe', 0, 0
'White', 'Al', 1, 0

Here's my test data:

CREATE TABLE owners (id INT NOT NULL, firstname NVARCHAR(100) NOT NULL, lastname NVARCHAR(100) NOT NULL);
INSERT INTO owners (id, firstname, lastname) VALUES
(1, 'Todd', 'Smith'),
(2, 'Tom', 'Smith'),
(3, 'Sue', 'Test'),
(4, 'Joe', 'Thumb'),
(5, 'Al', 'White');

CREATE TABLE cars (id INT NOT NULL, owner_id INT NOT NULL, usable_onroad INT NOT NULL, usable_offroad INT NOT NULL);
INSERT INTO cars (id, owner_id, usable_offroad, usable_onroad) VALUES
(1, 1, 1, 0),
(2, 2, 1, 0),
(3, 2, 0, 1),
(4, 3, 0, 1),
(5, 3, 0, 1),
(6, 5, 1, 0);