SQLite - Joining 2 tables excluding certain rows based on a partial string match

Imagine I have two tables:

Table A

Names Sales Department
Dave 5 Shoes
mike 6 Apparel
Dan 7 Front End

Table B

Names SALES Department
Dave 5 Shoes
mike 12 Apparel
Dan 7 Front End
Gregg 23 Shoes
Kim 15 Front End

I want to create a query that joins the tables by names and separates sum of sales by table. I additionally want to filter my query to remove string matches or partial matches in this case by certain names.

What I want is the following result

Table C:

A Sales Sum B Sales Sum
18 24

I know I can do this with a query like the following:

SELECT SUM(A.sales) AS 'A Sales Sum', SUM(B.sales) AS 'B sales Sum' FROM A
JOIN B
ON B.names = A.Names
WHERE Names NOT LIKE '%Gregg%' OR NOT LIKE '%Kim%'

The problem with this is the WHERE clause doesn't seem to apply, or applies to the wrong table. Since the Names column doesn't exactly match between the two, what I think is happening is when they are joined 'ON B.names = A.Names', the extras from B are being excluded? When I flip things around though I get the same result, which is no filter being applied. The wrong result I am getting is the following:

Table D:

A Sales Sum B Sales Sum
18 62

Clearly I have a syntax issue here since I'm pretty new to SQL. What am I missing? Thanks!

2 answers

  • answered 2021-11-23 01:39 Tim Biegeleisen

    I think you want a union approach here:

    SELECT
        SUM(CASE WHEN src = 'A' THEN sales ELSE 0 END) AS "A Sales Sum",
        SUM(CASE WHEN src = 'B' THEN sales ELSE 0 END) AS "B Sales Sum"
    FROM
    (
        SELECT sales, 'A' AS src FROM A WHERE Names NOT IN ('Gregg', 'Kim')
        UNION ALL
        SELECT sales, 'B' FROM B WHERE Names NOT IN ('Gregg', 'Kim')
    
    ) t;
    

    Here is a demo showing that the above query is working.

  • answered 2021-11-23 07:02 forpas

    You don't need a join or a union of the tables and you shouldn't do it.
    Aggregate in each table separately and return the results with 2 subqueries:

    SELECT 
      (SELECT SUM(Sales) FROM A WHERE Names NOT LIKE '%Gregg%' AND Names NOT LIKE '%Kim%') ASalesSum,
      (SELECT SUM(Sales) FROM B WHERE Names NOT LIKE '%Gregg%' AND Names NOT LIKE '%Kim%') BSalesSum
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum