Sql statement teaser...

Question

ok so this is what I am trying to achieve is to have two SELECT statements and then join results of each so I have something like this

SELECT table.ID, tst.Value 
FROM blah AS table JOIN results AS tst ON tst.RUNID = table.RUNID
WHERE table.RUNID IN
(
  ...// nothing important but in the end i get my tst.Value  
)

second statement is almost identical

SELECT table.ID, tst2.Value 
FROM blah AS table JOIN results AS tst2 ON tst2.RUNID = table.RUNID
WHERE table.RUNID IN
(
  ...// nothing important but in the end i get my tst2.Value differently
)

I need to combine these two results in format of

SELECT table.ID, tst.Value, tst2.Value
...... // Somehow using those two statements

So anyone fluent in SQL language who could tell me how to do this or what statements should I use...Join sound like a good place to start but they use tables. I guess I could use a CREATE TABLE from SELECT and join but as I said I am not fluent in SQL so wondering if thats good idea or there is a better idea...

Thanks :)

Answer 1

This should work for you:

SELECT table.ID, tst.Value, tst2.Value 
FROM blah AS table 
Inner JOIN results AS tst ON tst.RUNID = table.RUNID
Inner JOIN results AS tst2 ON tst2.RUNID = table.RUNID
WHERE table.RUNID IN
(  
  ...// nothing important but in the end i get my tst2.Value differently
)

Answer 2

It is possible to use more than one JOIN in a query. This should solve your question.

Answer 3

The naive approach, assuming table.ID is what relates the two value fields:

SELECT first_subquery.ID, first_subquery.Value, second_subquery.Value
FROM (
  SELECT table.ID, tst.Value 
  FROM blah AS table JOIN results AS tst ON tst.RUNID = table.RUNID
  WHERE table.RUNID IN
  (
    ...// nothing important but in the end i get my tst.Value  
  )
) first_subquery
INNER JOIN (
  SELECT table.ID, tst2.Value 
  FROM blah AS table JOIN results AS tst2 ON tst2.RUNID = table.RUNID
  WHERE table.RUNID IN
  (
    ...// nothing important but in the end i get my tst2.Value differently
  )
) second_subquery
ON first_subquery.ID = second_subquery.ID

This query is based on both of yours, joined together.

Answer 4

try something like:

SELECT table.ID, tst.Value, dt.Value
    FROM blah AS table 
        JOIN results AS tst ON tst.RUNID = table.RUNID
        left outer join (
                         SELECT table.ID, tst2.Value
                             FROM blah AS table 
                                 JOIN results AS tst2 ON tst2.RUNID = table.RUNID
                             WHERE table.RUNID IN(  ...// nothing important but in the end i get my tst2.Value differently)
                        ) dt ON table.id=dt.ID
    WHERE table.RUNID IN(  ...// nothing important but in the end i get my tst.Value  )

Answer 5

Try this...

SELECT T1.ID, R1.Value,
       T2.RunId, R2.Value
FROM (blah AS T1 JOIN results AS R1
         ON R1.RUNID = table.RUNID
           And T1.RUNID IN ( /... / ))
    Cross Join (blah AS T2  JOIN results AS R2 
         ON R.RUNID = T2.RUNID
           And T2.RunId In (/... /))

Answer 6

Try this:

select a.ID, a.Value, b.Value from 
(
  SELECT table.ID, tst.Value 
  FROM blah AS table JOIN results AS tst ON tst.RUNID = table.RUNID
  WHERE table.RUNID IN
  (
    ...// nothing important but in the end i get my tst.Value  
  ) 
 ) a, 
(
  SELECT table.ID, tst2.Value 
  FROM blah AS table JOIN results AS tst2 ON tst2.RUNID = table.RUNID
  WHERE table.RUNID IN
  (
    ...// nothing important but in the end i get my tst2.Value differently
  )
 ) b

Answer 7

The easiest method (from what you've described) would be to move the IN clause into your join condition and do this all in one query. Like this:

select
    table.ID,
    tst.Value as Value1,
    txt2.Value as Value2

from blah table

left join results tst on tst.RUNID = table.RUNID and tst.RUNID in (...first conditions...)
left join results tst2 on tst.RUNID = table.RUNID and tst2.RUNID in (...second conditions...)

This structure is a little nonsensical to me (since you're filtering the values from your root table differently each time). Could you post the actual structure and SQL for us to look at? That may make it more clear.