[lcfjoertoft]

Hi all.
I'm having trouble combining two tables, where I need the top from table 2.

Table 1 (t1)
ID
901
901
901
902
902
902

Table 2 (t2)
ID Pos Nr
901 A01 123
901 A02 321
901 A03 222
902 A01 214
902 A02 666
902 A03 466

The result I'm after is:
ID POS Nr
901 A01 123
902 A01 214

I'm all out of ideas. Anyone know how to extract the above result?

[ForcedSterilizationsForAll]

Look at joining the tables on the ID and the min of your number column.

[lcfjoertoft]

ForcedSterilizationsForAll, on 27 January 2012 - 08:50 AM, said:

Look at joining the tables on the ID and the min of your number column.

Don't work.
This is a part of a vb.net query, so I solved it temporary by querying the first list, then do a loop with the result in a new query. I am trying to do those two operasjons in one query.

[modi123_1]

why does table 1 even matter?

Are you trying to take the the minimum "pos" value for each distinct "column"?

[lcfjoertoft]

modi123_1, on 27 January 2012 - 02:47 PM, said:

why does table 1 even matter?

Are you trying to take the the minimum "pos" value for each distinct "column"?

The real setup is realy much more complex than what I have presented here. This is just a the part I'm having trouble with.

I am actually trying to get each distinct value from table 1, match it to the ID of table 2. Then get the first AND the last "pos" value coresponding to each distinct value from table 1. But since I know how to get the last value by sorting the solution I'm hoping to get here as DESC, I have left that part out in the question.

As I wrote two posts up,I'm trying to set up a query that gets the distinct values from table 1 (wich is 901 and 902). Then do the equivelent of a loop where each value (eg 901 and 902) are pared with the correct "TOP 1" from table 2.

No matter how I try it, I only manage to get the result from 901.
I have tried all sort of ways to put table 1 into the where statement.
Here is a short query that presents the results for 901 and 902, but it wil obviously not work when the values in table 1 changes, and the TOP 1 command returns only 901.

SELECT DISTINCT TOP 1 t1.ID,t2.POS,t2.Nr
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID = '901' OR t1.ID = '902'

Or another way to get the same stupid result

SELECT t1.Nr,t2.tube
FROM
(SELECT DISTINCT ID
FROM Table1
WHERE ID = '901' OR ID = '902') as t1
JOIN
(SELECT TOP 1 ID,POS,Nr
FROM Table2
JOIN Table1 ON Table1.ID = Table2.ID
WHERE ID = '901' OR ID = '902') as t2
ON t1.ID = t2.ID

These two very easy ways to query the tables gives:

ID POS Nr
901 A01 123


Since I manage to get only the top one result this way, I used it in a loop in vb.net, and stored it in a datatable. Each loop creating a row, and adding the result. This will not sufice, so I need to do this IN the query not as part of a series of queries in a application.

This post has been edited by lcfjoertoft: 27 January 2012 - 04:28 PM

[e_i_pi]

Alright, I'll post a solution, with the caveat that it hasn't been tested. Also, these are the assumptions I'll make:

• You need to get the distinct IDs from table 1
  
• With those distinct IDs, select the rows from table 2 where the POS is the minimum OR the maximum value

So, here's what I believe the solution is:

SELECT
	t1.ID,
	tMin.Pos AS MinPos,
	tMin.Nr AS MinNr,
	tMax.Pos AS MaxPos,
	tMax.Nr AS MaxNr
FROM (SELECT DISTINCT ID FROM t1) AS t1
INNER JOIN (
	SELECT
		ID,
		MIN(Pos) AS Pos
	FROM t2
	GROUP BY ID
) AS tMinBridge ON tMinBridge.ID = t1.ID
INNER JOIN t2 AS tMin
	ON tMin.ID = tMinBridge.ID
	AND tMin.Pos = tMinBridge.Pos
INNER JOIN (
	SELECT
		ID,
		MAX(Pos) AS Pos
	FROM t2
	GROUP BY ID
) AS tMaxBridge ON tMaxBridge.ID = t1.ID
INNER JOIN t2 AS tMax
	ON tMax.ID = tMaxBridge.ID
	AND tMax.Pos = tMaxBridge.Pos

See if that works, if it doesn't I'll whip up a test DB and fix it up. I don't think it's exactly optimised, so I may have a look into it anyhow. I believe it can be done with only two INNER JOINs, with some HAVING clauses thrown in there for row determination.