Problem connecting to SQL Server tables with the same plugin and the same data type

Question

I want to join two tables using the same storage plugin. But one of the columns shows a null value. I am using this query: -

select * from SqlServer.test_mopslive.reports as Reports join SqlServer.test_mopslive.reportsetting as ReportSetting on Reports.ID = ReportSetting.ID 

Here SqlServer is the name of the storage plugin, test_mopslive is the name of the database, reports , reportsetting are the names of the tables.

When executing this request, T_ID indicates Null.

But if I use two different names for the storage plugin with the same credentials, it works correctly.

TABLE 1: -

 create table Reports (ID bigint, Name varchar(25)); insert into Reports values (29, 'SqlThree'); insert into Reports values (30, 'SqlTwo'); insert into Reports values (31, 'SqlThree'); 

TABLE 2:-

 CREATE TABLE ReportSetting ( P_id bigint not null auto_increment primary key, Name varchar(25), ID bigint, CONSTRAINT fk_ID FOREIGN KEY (ID) REFERENCES Reports(ID)); insert into ReportSetting values (1,'My_Sreekant1', 29); insert into ReportSetting values (2,'My_Sreekant2', 30); insert into ReportSetting values (3,'My_Sreekant3', 31); 

Is it possible to join two tables using the same plugin name for storage? If so, what am I doing wrong in this request?

Answer 1

You continue to change the text of your question and code - you used the CORRECT MUTUAL WORK before and now, you use a simple JOIN that matches the INNER JOIN.

Since you are using an INNER JOIN, you will not get any rows from any table that do not match your join condition:

 join SqlServer.test_mopslive.reportsetting ReportSetting on Reports.ID = ReportSetting.ID 

If you run this query (against SQL Server - I don’t know how Drill works), you will not have any rows where Reports.ID or ReportSetting.ID are not equal and you will not have rows, they are zero. Value, if there are no records in the ReportSetting table in the report, this report is not displayed in your result set, and if there is no correspondence in the ReportSetting table in the Reports table, it will not be displayed in your result set.

If you used the CORRECT MUTUAL WORK, you will get all the rows in the JOIN target (ReportSetting) table with data from the JOIN source (Reports) where available, or null in these fields.

If you used the LEFT OUTER JOIN, you will get all the rows in the JOIN source (Reports) table, with data from the JOIN target , where available, or null in this field.

If you used FULL OUTER JOIN, you will get all rows from the tables . with zeros in fields where there is no match.