Tables connection not reflect all data

i have 2 tables connected together first table called “visits” that include all visits and deals views to my store and second table called “deals” that include all orders for each deal i connect 2 tables and all orders in " deals" table should have views for “visits” table but actually there is some views is missing after connection and not reflect all deals views that recorded in “visits” table

Can someone support me on that?

It seems you are doing a left join and rows in the left side of the join do not have a corresponding row in the right side of the join (view). So either ensure that you have the correct join conditions so that all rows in both tables can be joined together or else switch to an Inner Join to ensure only matching rows are included.

Thanks darcoli for your usual support
actually i checked both tables and all related items is existing in both tables.
when i make inner joining for these 2 tables rows number of first table “deals” decreasing alot which mean that there is rows disappear after inner join but there is no empty cells in “views” table… so problem solved if i make inner join but some rows disappear from original table

An inner joins will only keep rows where the join condition is satisfied - all non matching rows from either table is dropped.

A left join will have the same rows as an inner join plus any other row from the table on the left side of the join but which does not satisfy the join condition. So, you will have some rows with null (empty) values for columns coming from the table on the right side of the join.

A right join is basically the opposite of a left join.

A full outer join will include all rows from both tables (even those that dont satisfy the join condition). So it is like a left join and a right join together.

You can see a practical example in QuickSight here Joining data - Amazon QuickSight

So, you just need to determine from which table you want to keep rows when the join condition is not satisfied and then choose the corresponding join condition. Additionally, you’d need to double check the join criteria to ensure that it makes sense for the tables you are joining.

Ok that’s good… i already checked all joining type but i think the problem due to edit SQL code that i used during connection data it self… let me inform you how i have connected both table from scratch

the first table “dealcodes” include all order_ids in rows…
second table include each view happened in row. So for each order there is many repeated views rows
when i connect table using edit SQL i used the code mentioned in photo to group all related views for each order_id in this case when i connect 2 tables will reflect accumulated views for each order_id group and when i do that already successes for order_ids and failed for other some order_id althought both tables have both order_id numbers so it should be matching together…
Do you think the problem in edit SQL code?

Is this SQL for the CountViews table in your first visual?
I’m having some difficulty understanding how your last post fits into your design. Can you show the whole picture of the tables joined in the designer (and indicate clearly in which part the problem lies)?

Were you able to resolve the issue?

Hi @kareem. Has this been resolved? If so, please help the community by marking the appropriate post as “Solution”. If you still need assistance, please provide additional detail. Thanks!

Since we have not received a response from you on this question, we are going to archive the question. If you still need this question answered, please create a new question topic.