update thread set locked = 'True'; (SQL - Solved)

whuppins

Okay, our Oracle guy is out for the next week and a half and I need to generate a very simple report in SQL, ASAP. I'll try to keep things simple.

Our database keeps track of samples. These samples have names and ID numbers. Hence the "Sample" table:

[html]
SAMPLE

---

SAMPLE_ID SAMPLE_NAME

---

---

427 Apple
428 Banana
429 Orange
[/html]

Batches of samples can be grouped into racks. The database stores both the rack that a given sample is in, and its individual position within the rack:

[html]
RACK_LAYOUT

---

RACK_NAME POSITION SAMPLE_ID

---

---

---

Rack Q 1 429
Rack Q 2 427
Rack Q 3 428
[/html]

As shown, the two tables are tied together via the sample ID. In this example, Rack Q contains samples Orange, Apple, and Banana -- in that order.

What I need to do is generate a list of sample names from a given rack, ordered by their position in the rack. If I could include both SAMPLE.NAME and RACK_LAYOUT.POSITION in the Select statement, it would be no problem for me. The catch is that the report must contain sample names and sample names only. I don't know how to order by a column that doesn't appear in the selection. I know it can be done, though, because I've seen it before.

So, the best I can do is generate a list with no sorting, with the following SQL:

[html]
SELECT sample_name
FROM sample
WHERE sample_id IN (SELECT sample_id
FROM rack_layout
WHERE rack_name = 'Rack Q')
[/html]
I know I need to throw in an ORDER BY clause in there somewhere, and also probably tie in the Rack_Layout table somewhere else too, but I haven't the foggiest idea how it's done. Anyone halfway decent at SQL care to help me out?

Oh, also, this is a super-duper-oversimplification of the problem and our DB structure. For a number of reasons, I can't go creating new fields or otherwise messing with how the data is arranged. This is the layout I have to work with, and all the 'magic' has do be done by the query.

And by the way, if you're wondering why I used HTML formatting, it's because it preserves spacing and [code] doesn't always.

neth

SELECT sample_name
  FROM sample
 WHERE sample_id IN (SELECT sample_id
                       FROM rack_layout
                      WHERE rack_name = 'Rack Q') Order By RACK_LAYOUT.POSITION DESC

doesn't work? i was able to do that within a single table. gimme a sec and i'll try with 2 tables.

edit: might need a join on the tables after playing around, still checking.

neth

SELECT sample_name
  FROM sample inner join RACK_LAYOUT
  ON table1.sampleid = RACK_LAYOUT.sampleid
 WHERE rack_name = 'Rack Q' Order By POSITION DESC

alright here's a shot, i'm not sure if this will work with your "more complicated" version since i'm not sure what you are doing with it.

FunkyWaltDogg

You will probably want to do a join as neth has suggested. ORDER BY will sort your output, and the fields you sort on don't have to be in the output table.

JHunz

Well, that definitely won't work with Oracle, I can tell you that. Join syntax is completely different.

Try this:

select sample_name 
  from sample, rack_layout
  where rack_name='Rack Q'
  and sample.sampleid=rack_layout.sampleid
  order by rack_layout.position asc;

Incidentally, given the database description, you may work for one of my company's competitors.

a penguin

JHunz wrote: »

Well, that definitely won't work with Oracle, I can tell you that. Join syntax is completely different.

Try this:

select sample_name 
  from sample, rack_layout
  where rack_name='Rack Q'
  and sample.sampleid=rack_layout.sampleid
  order by rack_layout.position asc;

Incidentally, given the database description, you may work for one of my company's competitors.

yeah that looks right to me.

Also coincidentally, the rack description is making me raise an eyebrow as well. Looks familiar...

I am probably just reading into things though.

whuppins

JHunz wrote: »

Well, that definitely won't work with Oracle, I can tell you that. Join syntax is completely different.

Try this:

select sample_name 
  from sample, rack_layout
  where rack_name='Rack Q'
  and sample.sampleid=rack_layout.sampleid
  order by rack_layout.position asc;

Incidentally, given the database description, you may work for one of my company's competitors.

WINNAR

Thanks, stupid joins. I should have remembered that bit.

Incidentally, I do work for a lab that handles samples, but we're employees of the state government on a more or less fixed budget, so we don't really have any competitors per se. At least until the contract runs out in 10 years or so and they start requesting bids again. The "competition" you may have been thinking of is a LIMS, Thermo's Nautilus, but we're just end-users, not the developer. Anyway, I love to talk to people in the informatics industry. Shoot me a line if you do, indeed, work for one of Nautilus' competitors; I'd love to hear about the other stuff people are doing with LIMS.