SQL…What’s That?

Actually it’s a type of language for interacting with a database, Structured Query Language. But…more importantly…it’s a way to manage and interact with relational databases in ways that can be way more complex than just using a spreadsheet. Take a hike Excel.

Why is SQL relevant on this rainy Sunday evening? Because it’s cool. And because it makes life easier when working with a bunch of records that may need imported and exported.

Our subject for the evening…

So I’ve manipulated the address book a little more. In a good way. The cool thing about SQL (and frankly I’m only skimming the surface at my level of understanding) is that it can make huge datasets easily accessible and manipulate those datasets in ways that a spreadsheet may not be able to handle.

The goal, mainly, is to make an interface for the dataset that is user friendly, easy to visualize (personally, I’m not really a fan of large spreadsheets), and can be filtered and manipulated easily (again, doable with spreadsheets but not “easy”).

With spreadsheets it was getting to be a problem to handle households and relationships, especially grandchildren and great-grandchildren have started moving out and living on their own. That’s where SQL queries to a database come in. We already had all the info in a spreadsheet, so the next step was to bring that into a database that we could then query and manipulate.

So how does that work…

We’ve actually got 5 tables. The main table of course is where all the juicy details live. But…that table both feeds and gets info from other tables now. Other tables feed drop-downs that help provide contextual information, especially for the export tables.

The power of queries…

Okay, so here’s where it gets a bit heady. We’ve got the main table. It’s an easy query:

SELECT *,
CASE
    WHEN PersonID = Household THEN '*'
    ELSE ''
END AS HOH
FROM family_address_book

This tells us to select all from the family address book, with the additional caveat that if the personID matches the householdID, we’ll add a “*” to the HoH column to help flag that person.

HoH…

So now we have a big list of people. How do we get a drop-down to select the HoH and create another unique index? I can’t create a dropdown that references the same table…but I can create a new table, build a list, and then reference that table in the original dropdown. So:

SELECT PersonID, CONCAT(`Last Name`, ', ', `First Name`) AS FullName
FROM family_address_book
ORDER BY `Last Name`, `First Name`

All that query does is take each person in the original table, grabs the first and last name and formats it as a new record called “FullName”. The table is indexed by HouseholdID, but notice we grabbed the personID too? Those two numbers should always match.

Back on the original table, we just create a new field from the second table indexed by a “foreign key” (or, householdID). Now, when we have households assigned, if the PersonID = HouseholdID in the main table, we know that person is the HoH so he/she gets a “*”.

Exports

The ‘something’ family

This is a little more fun, but it takes the HoH flag a bit further to create a nice record that can be imported into address books neatly:

SELECT 
    CASE 
        WHEN COUNT(*) = 1 THEN CONCAT(MAX(`First Name`), ' ', MAX(`Last Name`))
        ELSE CONCAT('The ', MAX(CASE WHEN PersonID = Household THEN `Last Name` END), ' Family')
    END as 'Name',
    MAX(CASE WHEN PersonID = Household THEN Address END) as 'Street 1',
    MAX(CASE WHEN PersonID = Household THEN `Address 2` END) as 'Street 2',
    MAX(CASE WHEN PersonID = Household THEN City END) as 'City',
    MAX(CASE WHEN PersonID = Household THEN State END) as 'State',
    MAX(CASE WHEN PersonID = Household THEN Zip END) as 'Postal Code',
    'United States' as 'Country',
    MAX(CASE WHEN PersonID = Household THEN Email END) as 'Email',
    MAX(CASE WHEN PersonID = Household THEN Mobile END) as 'Phone'
FROM family_address_book
WHERE Household IS NOT NULL
GROUP BY Household

This export table takes data from the main table, looks for the HoH and how many are in the household, and creates an address record under “The ‘something’ family”, with the HoH last name taking priority. Households with 1 member only get listed by that member’s full name. Importantly, flagging a person as HoH in the main table will create a record for them (or their household) in both export tables (useful for college peeps that may want their own cards…).

Listed by names

And the last export table…lists each member by name. It has 2 queries; a stored SQL function query (to make it play nice with the data tables GUI), and the actual visible table query that references the SQL function. Since “and” or “&” is a hard concept to add in neatly…we have to do it this way.

DELIMITER $$

CREATE FUNCTION format_family_names(household_id INT)
RETURNS VARCHAR(255)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE name_list VARCHAR(255);
    DECLARE name_count INT;
    DECLARE last_name VARCHAR(100);
    
    -- Get the count of people in this household
    SELECT COUNT(*), MAX(CASE WHEN PersonID = household_id THEN `Last Name` END)
    INTO name_count, last_name
    FROM family_address_book
    WHERE Household = household_id;
    
    -- Format based on count
    IF name_count = 1 THEN
        SELECT CONCAT(`First Name`, ' ', `Last Name`)
        INTO name_list
        FROM family_address_book
        WHERE Household = household_id;
        
    ELSEIF name_count = 2 THEN
        SELECT CONCAT(GROUP_CONCAT(`First Name` ORDER BY Age DESC SEPARATOR ' and '), ' ', last_name)
        INTO name_list
        FROM family_address_book
        WHERE Household = household_id;
        
    ELSE
        -- For 3+ people, build the list with Oxford comma
        SELECT GROUP_CONCAT(`First Name` ORDER BY Age DESC SEPARATOR '|')
        INTO name_list
        FROM family_address_book
        WHERE Household = household_id;
        
        -- Replace the last separator with ", and "
        SET name_list = REPLACE(name_list, '|', ', ');
        SET name_list = INSERT(name_list, 
            CHAR_LENGTH(name_list) - LOCATE(',', REVERSE(name_list)) + 1,
            1,
            ', and');
        SET name_list = CONCAT(name_list, ' ', last_name);
    END IF;
    
    RETURN name_list;
END$$

DELIMITER ;

SELECT 
    CASE 
        WHEN COUNT(*) = 1 THEN CONCAT(MAX(`First Name`), ' ', MAX(`Last Name`))
        ELSE CONCAT('The ', MAX(CASE WHEN PersonID = Household THEN `Last Name` END), ' Family')
    END as 'Name',
    MAX(CASE WHEN PersonID = Household THEN Address END) as 'Street 1',
    MAX(CASE WHEN PersonID = Household THEN `Address 2` END) as 'Street 2',
    MAX(CASE WHEN PersonID = Household THEN City END) as 'City',
    MAX(CASE WHEN PersonID = Household THEN State END) as 'State',
    MAX(CASE WHEN PersonID = Household THEN Zip END) as 'Postal Code',
    'United States' as 'Country',
    MAX(CASE WHEN PersonID = Household THEN Email END) as 'Email',
    MAX(CASE WHEN PersonID = Household THEN Mobile END) as 'Phone'
FROM family_address_book
WHERE Household IS NOT NULL
GROUP BY Household

So, like the previous table, we check for HoH and group people by the HoH, but in this case the first function formats it so that it comes out grammatically nice with all the member names plus the Oxford comma. Neat, right?

Author: Zack

Pharmacist, tech guy, gamer, fixer. Good at a little bit of everything.