Select statements, Joins, and Repurpose Query in SQL

Question

I have a constant set of users that I want to filter out and apply to each query to look at what they are doing on an app. I have been poking and probing around here to get a better sense of how to do this, but it is still unclear to me. I am a newbie with SQL statements and JAVA. Any help, particularly explanation is highly welcomed.

I have been trying to figure it out using these two articles to no avail: 1 & 2; these examples show how you can create a query and use it in another query, which is what I am trying to do.

Someone mentions a wrap (see ref. 1) which is what i attempt to do:

Sure, wrap your two set operations in outer queries with selects that include fixed columns called Source, i.e. SELECT 'horrible_query_1' AS Source, * and SELECT 'ugly_query_2' AS Source, *.

That way your UNION will give you all the columns from your query plus the source identifier.

My question: is it possible to repurpose these queries into separate queries without having to do joins with the variable? I don't understand how that works (I'm not sure I am saying it right either).

This is the User group and filter I want to repurpose across all queries:

<select id="get_num_x_users" resultClass="java.lang.Integer">
select count(distinct user_id) from positions sp
    join position_definitions sd on sp.position_id = sd.id
    where sd.position like '%catcher%' 
            or lower(sd.position) like '%pitcher%' 
            or lower(sd.position) like '%dh%';
    </select>

Repurpose into this query (among a few others):

<select id="get_u_counts" resultClass="java.lang.Integer">
    SELECT COUNT(DISTINCT u.id)
    FROM searches s
    JOIN users u
    ON s.user_id            = u.id
    WHERE search_date       > DATE_SUB(curdate(), INTERVAL 3 MONTH)
    AND LENGTH(search_term) > 0
    AND search_term NOT LIKE ' LIC:  SPEC: %'
    AND u.type != 'coach';
</select>

My attempt (That does not work when I am in mysql database):

 with get_x_users as (
        select count(distinct user_id) from positions sp
        join position_definitions sd on sp.position_id = sd.id
        where sd.position like '%catcher%' 
            or lower(sd.position) like '%pitcher%' 
            or lower(sd.position) like '%dh%';),

(SELECT COUNT(get_x_users)
FROM searches s
JOIN users u
ON s.user_id = u.id
AND get_x_users
WHERE search_date       > DATE_SUB(curdate(), INTERVAL 3 MONTH)
AND LENGTH(search_term) > 0
AND u.type != 'coach');

Why do you return a count "select count(distinct user_id) from positions sp..."? You can't join on that. You should return a distinct list of user_id "select distinct user_id from positions sp..." and join user_id to the u.id in your main query. — strattonn, Feb 15 '17 at 23:16

score 0 · Answer 1 · answered Feb 28 '17 at 10:36

In SQL we can join tables together using a common field. For instance, if you have a user_id field in two different tables you can match the records using that common field. You can also create a join that gives you all the records in one table and only those that match in the second table, that's what LEFT JOIN does.

Using this principle, you do a little reverse logic and create a query which gives you all the records from your search query (ExcludeQuery) and join it to the users whom you want to exclude. This will give you a list of records with and without matches to the excluded users. What you do then is use a where to only include the records that haven't matched an excluded user, WHERE ExcludeQuery.user_id IS NULL.

SELECT s.*
FROM searches s
JOIN users u
ON s.user_id = u.id
WHERE search_date       > DATE_SUB(curdate(), INTERVAL 3 MONTH)
AND LENGTH(search_term) > 0 AND u.type != 'coach'
LEFT JOIN
(select user_id from positions sp
    join position_definitions sd on sp.position_id = sd.id
    where sd.position like '%catcher%' 
    or lower(sd.position) like '%pitcher%' 
    or lower(sd.position) like '%dh%') AS ExcludeQuery
ON ExcludeQuery.user_id=s.user_id
WHERE ExcludeQuery.user_id IS NULL

Select statements, Joins, and Repurpose Query in SQL

1 Answers1