Pulling the same column from multiple tables using SQL on Amazon Quick Sight

Question

I'm working using Amazon Quick Sight pulling data from an MySQL database to generate visual data for my employer. I'm a student and have a little SQL experience but have never had to deal with a database this large before.

The tables I need to grab from are 001_leads to 024_leads. The tables are all designed the same way.

This is what I've currently been doing to no avail. Quick Sight doesn't give back great error alerts so I've been hacking at different solutions.

SELECT 001_leads.lead_received AS "City-A Leads Q1", 002_leads.lead_received AS "City-B Leads Q1" FROM 001_leads, 002_leads WHERE lead_received BETWEEN '2017-01-01' AND '2017-04-01'

I'm fairly positive I need use a join but I have no clue how to join 24 different tables.

Ideally when this query runs it will show every lead from every city.

Thanks for having a look :)

EDIT:

Here is some column names from 001_leads

Example of SQL in Quick Sight. I'd like the results of this query for all 24 different tables

In your where clause, you will need to specify which table ```lead_received``` comes from. — Sloan Thrasher, May 23 '17 at 20:13
In your query, you are only showing the date the lead was received. Also, if you use a join, you will need to specify how the rows are related. Do you want a list with one lead per row as the output? What other columns do you want to show per lead? As @DarbyM asks, edit your question and show the schema of one of the tables. — Sloan Thrasher, May 23 '17 at 20:23
The dates are what I am looking for. Quick Sight will be able to count the leads and I will use that number to compare to other cities. I'm new to stack but I'll try to show the design of the tables. — Brendan, May 23 '17 at 20:27

DarbyM · Accepted Answer · 2017-05-23T21:17:12.597

0

I think Union is what you're after... Not completely sure, with the wording of your question.

Eg.

SELECT 001_leads.lead_received AS Leads
FROM 001_leads
WHERE lead_received BETWEEN '2017-01-01' AND '2017-04-01'

UNION 

SELECT 002_leads.lead_received
FROM 002_leads
WHERE lead_received BETWEEN '2017-01-01' AND '2017-04-01'

UPDATE

The following should do what you're saying you want it to look like.

SELECT l1.lead_received AS LeadsCityA, l2.lead_received AS LeadsCityA, l3.lead_received AS LeadsCityA, l4.lead_received AS LeadsCityA
FROM 001_leads l1
LEFT JOIN 002_leads l2
    ON lead_received BETWEEN '2017-01-01' AND '2017-04-01'
LEFT JOIN 003_leads l3
    ON lead_received BETWEEN '2017-01-01' AND '2017-04-01'
LEFT JOIN 004_leads l4
    ON lead_received BETWEEN '2017-01-01' AND '2017-04-01'
WHERE lead_received BETWEEN '2017-01-01' AND '2017-04-01'

edited May 23 '17 at 21:17

answered May 23 '17 at 20:11

DarbyM

1,173
2
9
25

I believe the OP wanted a pivot table, also, this doesn't indicate which table the row comes from. – Sloan Thrasher May 23 '17 at 20:12
His example kinda points that direction, but making an assumption based on column names, I assume 1000 Columns isn't what he is after... To be honest I'm not yet sure though. – DarbyM May 23 '17 at 20:21
How can I help with clarifying? I tried your method above but instead of making lead_received from 002 it's own column it just added to the bottom of 001. – Brendan May 23 '17 at 20:22
Ah yes.. that is what a union does.... If you're after each having its own row, you're after more of a "Pivot Table" as Sloan mentioned. I would have to re write this. – DarbyM May 23 '17 at 20:43
Please define in your original post, what the 001_leads table looks like.. that will be needed to properly build the joins. – DarbyM May 23 '17 at 20:45
I've added a picture from inside quick sight. – Brendan May 23 '17 at 20:49
Ok, I see that now..... so you want Each lead_received City to be its own column? What would be in Column 1? If so, I'm not sure that is "Really" what you want. one last request.. can you put up a mockup of what you WANT the table to look like? – DarbyM May 23 '17 at 20:53
Added another picture. I'd like 24 columns all looking like that with that cities lead data for the first quarter. – Brendan May 23 '17 at 20:56
Okay, so apparently each "city" has its own table. An you want to Union all tables together, then Pivot them to give each City its own Column... This is doable.. but doesn't makes much sense.. adjusting query now. – DarbyM May 23 '17 at 21:13
The 3 digit code in the table name represents a city. I should have made this clear from the beginning sorry. – Brendan May 23 '17 at 21:16
I added a new query above for you to try. – DarbyM May 23 '17 at 22:59

score 0 · Answer 2 · answered May 23 '17 at 20:27

Unless you really need a pivot table, I would suggest using a union and get one row per lead, like what @Darby suggests:

SELECT *
FROM (
    SELECT 
        'CityA' as `city`,
        a.*
    FROM `001_leads` a
    WHERE a.`lead_received` BETWEEN '2017-01-01' AND '2017-04-01'
    UNION
    SELECT 
        'CityB' as `city`,
        b.*
    FROM `001_leads` b
    WHERE b.`lead_received` BETWEEN '2017-01-01' AND '2017-04-01'
    UNION
    SELECT 
        'CityC' as `city`,
        c.*
    FROM `001_leads` c
    WHERE c.`lead_received` BETWEEN '2017-01-01' AND '2017-04-01'
    UNION
    SELECT 
        'CityD' as `city`,
        d.*
    FROM `001_leads` d
    WHERE d.`lead_received` BETWEEN '2017-01-01' AND '2017-04-01'
    UNION
    SELECT 
        'CityE' as `city`,
        e.*
    FROM `001_leads` e
    WHERE e.`lead_received` BETWEEN '2017-01-01' AND '2017-04-01'
) as `all`
ORDER BY all.`lead_received`,all.`city`;

If this is a query that will be done for different time periods in the future, I would suggest creating a view with the unions, then doing a select against the view.

the OP has no mention of columns named CityA in table 00_1leads. — DarbyM, May 23 '17 at 20:42
No, but since this isn't a pivot table with the column name showing which source table was used, I hard coded a column to indicate which source table supplied the info. — Sloan Thrasher, May 23 '17 at 22:53

Pulling the same column from multiple tables using SQL on Amazon Quick Sight

2 Answers2