Difficult SQL Query? select specific values?

Help!



Is it possible to create a query which joins several tables of data and select specific data?

This is for a manufacturing process where there are three tables. For simplicity I will call then table1, table2, & table3

table1 has several columns: entry_id, customer, weight, type ...etcFrom this table I need, entry_id & customer.

table2 has entry_id, exit_id, process. I need entry&exit id to link table1 to table3 (process is time, this is not needed)

Finally table 3 (this is where I have the problem) has column name exit_id number parameter value distance time

each exit_id has several parameters (temp1, temp2) for each parameter there can be n number of readings, these are measured by number (1,2,3 etc) and distance (e.g 300m, 277m, 222m etc ..for unknown reason this is measured in reverse) Time is just a normal timestamp for each reading. 
The parameters measure when there is a process change (sudden temp rise etc) Therefore there could be 2 readings or 50+ per exit_id.

I only need the final reading, because of a silly glitch this is the highest distance or number to give the process parameter at the point the lab tests the product.
so for one exit_id I need temp1 at 450m (highest number)The next might be for the temp1 at 392m (highest number for that product)

Any suggestions? Is this possible?!

Thanks,
Martyn

 

You could do a normal inner join set and then do a grouping based on your product, a max on the temp1 (if you want the highest number) ... OR do your inner join and then do a select top 1 * and do an order based on your identity seed so that you can get your latest entry.

 

its seem below is the representation of your problem and i have provided some logic and let me know if my understanding is correct i will get you the complete query.

data representation in the table 3 would be like this

Exit ID Parameters Number Distance

1 temp1 1 300
1 temp1 2 277
1 temp1 3 222
2 temp2 1 400
2 temp2 2 500
3 Temp2 3 450

desired out put
temp 1 222
temp 2 450

there could be many approches on of these is explained below

create temporary table with identity column
dump the data from table3 into the temp table.


select *
table1
inner join table2 t1.entry_id=t2.entry_ID
inner join Temporay tble 3 t3 t2.exit_id=t3.exit_id
where
ID column of temp table in
( select max(id) from Temporay tble group by parameter)

 

Hi Randheer,

Your data representation/understanding of the problem is correct

Would it be possible to suggest how to achieve this?

Thanks,
Martyn