Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have the following query : 

SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs, 
 num_bases_in_singletons 
FROM stats_assembly WHERE ws_id IN (
  SELECT workflow_steps.id 
  FROM workflow_steps, workflows 
  WHERE workflow_steps.workflow_id = workflows.id 
      AND workflows.sample_id = (
            SELECT workflows.sample_id FROM workflows WHERE workflows.id = (
                SELECT workflow_id FROM workflow_steps WHERE id = 462
            )
            AND workflow_steps.submodule_id = 2
      )
)

And I want to rewrite it without using subqueries. So far I have been able to do only this: 

SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs, 
  num_bases_in_singletons 
FROM stats_assembly 
INNER JOIN workflow_steps ON ( stats_assembly.ws_id = workflow_steps.id ) 
INNER JOIN workflows ON ( workflow_steps.workflow_id = workflows.id )

And I have no idea how to finish it.

Furthermore, this query potentially returns multiple rows but I want to get the row which has MAX(ws_id). Any tips will be greatly appreciated.

share|improve this question
What are the Primary Keys of the tables? Is id the Primary Key in every table? – ypercube May 23 '12 at 15:33
ws_id is the primary in stats_assembly and id is the primary in every other table, yes – tyler May 23 '12 at 15:36

1 Answer

up vote 3 down vote accepted

I think this will get you there... cavaet: I'm a SQL Server person...

SELECT 
  ws_id
, n50
, num_singletons
, num_segments_less_100
, kmer
, num_all_reads
, number_bases_in_contigs
, num_bases_in_singletons 
FROM 
 stats_assembly sa
 join workflow_steps ws1 
   on sa.ws_id = ws1.id 
   and ws1.submodule_id = 2
 join workflows wf1 
   on ws1.workflow_id = wf1.id 
 join workflows wf2 
   on wf1.sample_id = wf2.sample_id 
 join workflow_steps ws2
   on wf2.id = ws2.workflow_id 
   and ws2.id = 462
share|improve this answer
I think this is correct. It needs a GROUP BY sa.ws_id to remove duplicates but I think it's correct. – ypercube May 23 '12 at 15:39
Yep, it is working for returning the all the records. Now I just need to extract the MAX which can be done with an ORDER BY and LIMIT 1 but I wonder if there is a better approach? – tyler May 23 '12 at 15:42
1  
ORDER BY ws_id DESC LIMIT 1 – ypercube May 23 '12 at 15:55
1  
^ I did it that way - works like a charm. – tyler May 23 '12 at 16:11
@tyler - Please click the checkmark below the voting arrows if this solves the problem at hand. This lets others know that you have found your solution, so they won't keep trying to solve an already solved problem. It also lets future users (who may find this same question through Google or Bing, etc) know what the solution was and move on more quickly with their day, instead of having to troubleshoot each given answer. In addition to checking off accepts, we also encourage upvoting for helpful answers. Thanks again! See the FAQ for more. – JHFB May 23 '12 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.