5

I want to join two table using same storage plugin. But One Of the Column showing 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 Storage Plugin Name, test_mopslive is Database Name, reports, reportsetting are Table Names.

While executing this query T_ID showing Null.

But If I am using two different storage plugin name with same credential it works properly.

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 table using same storage plugin name? If yes,then What am I doing wrong in this query?

Sanjiv
  • 980
  • 2
  • 11
  • 29
  • @dev:- Is it possible to join two table using same storage plugin name? – Sanjiv Mar 30 '16 at 08:06
  • yes it's possible. I have not tried with SQL server. But in general, it should work – Dev Mar 30 '16 at 08:16
  • @dev:- Would you please tell what i am doing wrong in this query..? – Sanjiv Mar 30 '16 at 08:24
  • if it's working with different storage plugin name, it should work with same names as well. You can ask on drill community. Maybe somebody else faced such kind of issue – Dev Mar 30 '16 at 08:34
  • @dev:- I already mail them... bt didn't get any reply.. IF Possible would you please run this query in your system.? – Sanjiv Mar 30 '16 at 11:56
  • share some data. I will try this at my end – Dev Mar 31 '16 at 06:36
  • @dev:- I just updated my question and posted one image having all the data for creating table and join query.I am facing this problem in SQLSERVER & MYSQL. I am posting mysql query. Please check it out – Sanjiv Mar 31 '16 at 07:25
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/107817/discussion-between-dev--and-sanjiv-kumar). – Dev Mar 31 '16 at 07:26
  • @dev:-ok dev.. lets continue in chat.. – Sanjiv Mar 31 '16 at 07:50
  • @ dev ツ:- Would please check the message in chat.. – Sanjiv Mar 31 '16 at 12:29
  • @devツ:- Have got any solution regarding `Join Query using same storage plugin.` – Sanjiv Apr 28 '16 at 13:30
  • I am not using drill now a days. So, I did not try it. – Dev Apr 28 '16 at 16:08
  • Can you post what the two tables look like (with some example data) and the result set you would expect? I have a feeling this is related to your use of the RIGHT OUTER JOIN but I can't be sure without seeing what the data looks like. I do not use drill but I can help determine if the SQL query is valid. – Eric Jun 28 '16 at 00:54
  • @Eric:- I have updated my question having table and query. Please check it out Eric. – Sanjiv Jun 28 '16 at 06:16
  • Well I don't see any column called T_ID now? Do you mean P_ID? – Eric Jun 28 '16 at 17:20

1 Answers1

0

You keep changing the text of your question and code - you were using a RIGHT OUTER JOIN before and now, you're using plain JOIN which is the same as INNER JOIN.

Since you're using an INNER JOIN, you will not get any rows from either table that do not fulfill 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, nor will you have any rows where either of them are null. Meaning, if a Report does not have any entries in the ReportSetting table, that Report does not show up in your result set, and, if a ReportSetting does not have a match in the Reports table, it will not show up in your result set.

If you were using a RIGHT OUTER JOIN, you would get all the rows in the JOIN target table (ReportSetting), with data from the JOIN source (Reports) where available or else null in those fields.

If you were using a LEFT OUTER JOIN, you would get all the rows in the JOIN source table (Reports), with data from the JOIN target (Report Settings) where available or else null in those fields.

If you were using a FULL OUTER JOIN, you will get all rows from both tables. with nulls in fields where there is not a match.

Eric
  • 673
  • 5
  • 18