Joined tables does not show one table data

Question & Answer
,
1

Hello,

after three days of investigations and testing I am fully lost with join feature. I just joined two tables but once i join first table (transactions) with the second one (mbs) it does not fill/show the data from second table(mbs). Both tables are in one mysql database on aws. So I use both from same data source.

Few details about tables:
transactions - it has about 3 mil rows, 30 columns, size about 3gb
mbs - about 30k rows, 15 columns, size about 20mb

Both tables includes value “tid” with different column name (terminalId or tid_with_zeros), so i am joining the tables using this columns, left join used. In “transaction” dataset the value “tid/transactionId” can repeat, so more rows may have same “tid” value. in “mbs” dataset the “tid/tid_with_zeros” value is unique. Once i finish it (i tried direct query and also spice storage), i get the result overview (see picture), but there are data missing from “mbs” table - columns are empty even that value “terminalId” from “transaction” dataset can be found in “mbs” dataset and column “tid_with_zeros”.
I checked in both tables the column type (both are string, in DB as varchar(10)) and also checked that there are no empty spaces.

I tried plenty of combinations and tests but still the data are not visible and once i try to generate analysis using these data the tables does not show joined data from mbs.

Also read the whole community, guides, but did not found the issue. Will really appreciate any help there!

Thanks in advance

Rado

quicksight_data not visible
quicksight_data not visible3186×2901 407 KB

2

Hi @Rady_Bryx - Welcome to AWS QuickSight community and thanks for posting the question. Can you please check whether you have any data with the joining condition. You can connect to database with any client tool and write the sql with the join and see whether any result is coming or not.

I assume here you are left join that means there are records which are in Table A not present in table B. You can change to inner join ( for testing purpose) to see whether you have any records common to both tables or not.

Regards - Sanjeeb

1 Like
3

Thanks Sanjeeb for reply. Once I apply the inner join the view is empty - which is strange as the values should match here.

To your first paragraph - my database has only the inbound rules access - so does the quicksight need to have access to write to the source database? I can easily access and change the database using mysql workbench.
or can you elaborate more what do you mean by “write sql with the join”

thanks for help

4

Hi @Rady_Bryx - Can you please test the sql in SQL work bench and confirm there are records common to both table. Please test the sql in SQL workbench first and the same sql can be used as custom sql in QuickSight as well.

Regards - Sanjeeb

1 Like
5

I just run this command:
SELECT * FROM transactions
INNER JOIN mbs
ON transactions.terminalId = mbs.tid_with_zeros

but the result is empty as well…

UPDATE - as tid_with_zeros was last table column and i was importing data from csv file, there was error in import formula where there was "LINES TERMINATED BY ‘\n’ " which i changed to "LINES TERMINATED BY ‘\r\n’ " which removes the next row char from fields and now everything works properly.

but anyway the inner join includes not all records, so continue with investigation.

Thanks

2 Likes
6

Hi @Rady_Bryx - Thanks. Once you will fix the issue at database side and verify the result, then you can easily replicate it in QuickSight. If you will require any additional help, let me know.

Regards - Sanjeeb