drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Charuta Rajopadhye <charuta...@gmail.com>
Subject Newbie: Join queries in MySQL.
Date Thu, 12 Oct 2017 09:16:10 GMT
Hi Team,


I have created a configuration for JDBC storage plugin in Drill, for a
MySQL database.

I am trying to fire join query on 2 tables. Both my tables have a column
called 'id'/

Table defination:

CREATE TABLE `simpleone` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `name` text NOT NULL,

 PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `simpletwo` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `city` text NOT NULL,

 PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1


because of the redundant ‘id’ column, i get values from one ‘id’ column as
NULL.

I tried using a few permutations to fire my queries:

0: jdbc:drill:zk=local>* select * from **pluginjdbc**.drillTest.simpleone, *
*pluginjdbc**.drillTest.simpletwo where simpleone.id <http://simpleone.id>
= simpletwo.id <http://simpletwo.id>;*

+-----+-------------------+-------+----------------+

| id  |       name        |  *id0*  |      city      |

+-----+-------------------+-------+----------------+

| 1   | J. D Salinger     | *null*  | New York City  |

| 2   | Charlotte Bronte  | *null*  | Thornton       |

+-----+-------------------+-------+----------------+

2 rows selected (0.224 seconds)


0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id>, tb2.id
<http://tb2.id> from pluginjdbc.drillTest.simpleone as tb1,
pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> = tb2.id
<http://tb2.id>;*

+-----+-------+

| id  |  id0  |

+-----+-------+

| 1   | null  |

| 2   | null  |

+-----+-------+

2 rows selected (0.368 seconds)


0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id> as col1, tb2.id
<http://tb2.id> as col2 from pluginjdbc.drillTest.simpleone as tb1,
pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> = tb2.id
<http://tb2.id>;*

+-------+-------+

| col1  | col2  |

+-------+-------+

| 1     | null  |

| 2     | null  |

+-------+-------+

2 rows selected (0.236 seconds)


Could somebody please guide me through this? Please let me know if i am
missing something.

Thanks,

Charuta

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message