Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
monkey
Starting Member
3 Posts |
 Posted - 2006-07-12 : 02:34:17
|
| HI,I have two tables, egOPERATION(op_refno, f1, f2, .....)OPERATION_NOTE(note_refno, op_refno, note, condition, ....)an OPERATION stands for an operation in hospital, and an OPERATION_NOTE stands for a note describing the operation.Now OPERATION is the main one I am interested, an OPERATION may or may not have a matching OPERATION_NOTE, so what I want to achieve is to return the OPERATION row, plus the note, if there is no corresponding OPERATION_NOTE (op_refno links them) for the OPERATION, then display for the note field simply returns empty, for example the returned rows might look like:op_refno f1 f2 note1 xx xx 2 xx xx some note3 xx xx ie the operation may or may not have a note, but I want to display it anyway... since using an inner join on op_refno will result in failing to return the row if there is no such op_refno in OPERATION_NOTE, my approach so far was to use a nested selectSELECT OP_REFNO, F1, F2, (SELECT NOTE FROM OPERATION_NOTE WHERE OPERATION_NOTE.OP_REFNO=OPERATION.OP_REFNO AND CONDITION='y')FROM OPERATIONalso note that I also have a CONDITION field in OPERATION_NOTE that determines if the NOTE should be returned or not but since nested select appears to be extremely slow, especially on reporting services in VISUAL STUDIO, I am wondering if there are any other ways of doing it that achieves the same effect?THANKS :) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-12 : 02:54:42
|
| [code]SELECT op.OP_REFNO, op.F1, op.F2, CASE WHEN opn.Condition = 'y' THEN opn.Note ELSE '' END NoteFROM OPERATION opLEFT JOIN OPERATION_NOTE opn ON opn.OP_REFNO = op.OP_REFNO[/code]The code in red is optional.Peter LarssonHelsingborg, Sweden |
 |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-07-12 : 06:30:56
|
I assume you're aware that this logic will only work if the relationship between OPERATION and OPERATION_NOTE is 1 to 0 or 1, and not 1 to many. And hopefully you have constraints in place to enforce it (e.g. a unique constraint on OPERATION_NOTE.op_refno).If so, Peso's suggestion is good  Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
monkey
Starting Member
3 Posts |
Posted - 2006-07-12 : 20:39:58
|
| Thanks a lot! :)Now the situation is a bit more complicated, suppose the 'note' column I want from OPERATION_NOTE can not be got directly from op_refno (ie there is no op_refno in OPERATION_NOTE) but instead have to link through another table, say LINK (op_refno, note_refno) and suppose LINK and OPERATION_NOTE is 1:1, how do I proceed? is it correct to doLINK INNER JOIN OPERATION_NOTE ON note_refno RIGHT JOIN OPERATION on op_refno ??What if another situation arise, such as tables A, B, C, Dand I want to inner join A an B, inner join C and D, then do a left join in between, how can this be achieved? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-12 : 21:38:49
|
| It would be better if you posted the actual problem you want to solve, rather than posting one thing, and when you get an answer, saying that wasn't the real problem you want to solve.Just explain the problem clearly one time, and post the structure of the tables you are working with, including primary keys, unique constraints, and foreign key constraints. You can use Enterprise Manager to generate the DDL statements for all the tables.All this may be explained better in Brett's blog:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxCODO ERGO SUM |
|
|
|
|
|
|
|
|
alternatives to nested select?