1

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:

table1

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

table2

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

table3

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. – ypercubeᵀᴹ Mar 16 '15 at 23:16
  • @ypercube Would posting sample tables work? – abn Mar 16 '15 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? – ypercubeᵀᴹ Mar 16 '15 at 23:30
  • I mean sample rows from the tables. @ypercube – abn Mar 16 '15 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 ;) – ypercubeᵀᴹ Mar 16 '15 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.