I came across many solutions like the one in this question. However, they all are restricted to data from one table. I'm very very new to SQL. I'd like to get the same results as in the linked question with two tweaks.

  1. The column 'User' is from table1, 'Date' from table2, the rest from table3.
  2. I need to input a list of 'User'(s) for which the data is to be extracted.

I have really huge tables on which I need to work. I've tried distinct and group by but they are taking forever. I really appreciate any help.

Sample data:


Global_id       User    User_id     table_id    table_type  Disease         source
1               TT964   -1          555         1           Breast cancer   1
3               TT2345  -1          876         1           Lung cancer     2
5               TT083   TT083-01    123         1           Brain tumor     4
87              TT4456  -1          4843        1           Cardiac arrest  7
65              TT6688  -1          5850        1           Brain tumor     4
350             TT4678  -1          969         1           Cardiac arrest  7
678             TT4567  -1          3985        1           Lung cancer     2


fid gid gene                
3   1   BRCA1               
6   23  TP53                
7   4   APO             
1   6   APC             
6   72  HFE             
4   96  KRT5


table_id    m_id    f_id                
555         1       3               
876         1       1               
123         1       6               
875         2       6               
409         1       4               
3985        2       3   

Tried this query the latest:

SELECT distinct a.User, f.fid, table_type, f.gene, s.table_id, s.m_id, Disease
FROM data03.table3 s
inner JOIN data01.table1 a
inner join data01.table2 f
where f.fid in 
  (SELECT s.f_id FROM data03.table3 s
   where s.table_id in 
     (SELECT a.table_id FROM data01.table1 a 
      where a.User in ("TT964","TT083","TT6688"))
   and s.m_id in (1,2,3,4,5,6)
   and table_type = 1 
   and Disease like "Brain%" 
   and fid = 623 
   and a.User in ("TT964","TT083","TT6688"))

where fid and f_id are the same. I need to get data where the table_ids match for table_type 1 and disease brain cancer for users and a few other conditions mentioned in the query. My code could be quite messy as I'm pretty new to SQL again.

Here's the show create table data:

CREATE TABLE `table1` (
  `Global_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `User` varchar(128) NOT NULL,
  `User_id` varchar(255) NOT NULL,
  `table_id` bigint(20) NOT NULL,
  `table_type` smallint(6) NOT NULL,
  `Disease` varchar(255) NOT NULL,
  `source` int(11) DEFAULT NULL,
  PRIMARY KEY (`Global_id`),
  KEY `fk_table_id_table_type` (`table_id`,`table_type`),
  KEY `fk_User` (`User`)

CREATE TABLE `table2` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `gid` int(11) DEFAULT NULL,
  `gene` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`fid`),
  KEY `ix_gid` (`gid`),
  KEY `ix_gene` (`gene`)

CREATE TABLE `table3` (
  `table_id` bigint(20) NOT NULL,
  `m_id` smallint(6) DEFAULT NULL,
  `f_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`m_id`),
  KEY `idx_f_id_table_id` (`f_id`,`table_id`)
  • Post the SHOW CREATE TABLE .. of your 3 tables. Mar 16, 2015 at 23:16
  • @ypercube Would posting sample tables work?
    – abn
    Mar 16, 2015 at 23:25
  • Do you mean sample rows from the tables? Or the output of SHOW CREATE TABLE tablename; but with table/column names altered? Mar 16, 2015 at 23:30
  • I mean sample rows from the tables. @ypercube
    – abn
    Mar 16, 2015 at 23:31
  • Samples are good but create table scripts are better. We get to see data types, indexes, primary and foreign key constraints etc. You could also setup a sqlfiddle and show both ;) Mar 16, 2015 at 23:31


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.