|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
JOIN questionCODE in ORDERNOTE tells me if a certain condition is true. So I might have this: ORDER1 / note1-code12 / note2-code7 ORDER2 ORDER3 / note3-code7 I'm trying to obtain a derived column that is 1 if an order has a code7 in any note, otherwise 0. I tried this: SELECT WORKORDER.ORDER_ID, CASE WHEN ORDERNOTE.CODE=7 THEN 1 ELSE 0 END AS HAS7 FROM ORDER LEFT JOIN ORDERNOTE ON ORDERNOTE.ORDER_ID=ORDER.ORDER_ID What I wanted to see was this: ORDERID HAS7 ------------------- 1 1 2 0 3 1 But due to the LEFT JOIN what I see is this (doubled ORDER1): ORDERID HAS7 ------------------- 1 0 1 1 2 0 3 1 I see why, but I don't see whether I can construct a SQL query to give me what I wanted - "if an order has ANY note with a code7, set has7=1 else 0". Anybody know a way to accomplish that in SQL? Basically I'm trying to get this info into a single resulting datatable to send down to a PDA via a webservice so I didn't want to have to also send all the notes down and make this determination on the PDA. Thanks. Tim Try,
SELECT ORDER_ID, CASE WHEN exists(select * from ORDERNOTE where ORDERNOTE.ORDER_ID=ORDER.ORDER_ID and ORDERNOTE.CODE=7) then 1 else 0 end AS HAS7 FROM ORDER -- or SELECT ORDER_ID, coalesce(cast(t.order_id as bit), 0) FROM ORDER left join ( select distinct ORDER_ID from ORDERNOTE where CODE=7 ) as t on order.order_id = t.order_id AMB Show quote "Tim Johnson" wrote: > I have ORDER records that can have 0 or more associated ORDERNOTE records. A > CODE in ORDERNOTE tells me if a certain condition is true. So I might have > this: > > ORDER1 / note1-code12 / note2-code7 > ORDER2 > ORDER3 / note3-code7 > > I'm trying to obtain a derived column that is 1 if an order has a code7 in > any note, otherwise 0. I tried this: > > SELECT WORKORDER.ORDER_ID, > CASE WHEN ORDERNOTE.CODE=7 THEN 1 ELSE 0 END AS HAS7 > FROM ORDER > LEFT JOIN ORDERNOTE ON ORDERNOTE.ORDER_ID=ORDER.ORDER_ID > > What I wanted to see was this: > > ORDERID HAS7 > ------------------- > 1 1 > 2 0 > 3 1 > > But due to the LEFT JOIN what I see is this (doubled ORDER1): > > ORDERID HAS7 > ------------------- > 1 0 > 1 1 > 2 0 > 3 1 > > I see why, but I don't see whether I can construct a SQL query to give me > what I wanted - "if an order has ANY note with a code7, set has7=1 else 0". > Anybody know a way to accomplish that in SQL? Basically I'm trying to get > this info into a single resulting datatable to send down to a PDA via a > webservice so I didn't want to have to also send all the notes down and make > this determination on the PDA. > > Thanks. > > Tim > SELECT WORKORDER.ORDER_ID,
CASE WHEN ORDERNOTE.CODE=7 THEN 1 ELSE 0 END AS HAS7 FROM ORDER LEFT JOIN (SELECT ORDER_ID,CODE FROM ORDERNOTE WHERE CODE=7) ORDERNOTE ON ORDERNOTE.ORDER_ID=ORDER.ORDER_ID -- Show quoteThanks Ravi "Tim Johnson" wrote: > I have ORDER records that can have 0 or more associated ORDERNOTE records. A > CODE in ORDERNOTE tells me if a certain condition is true. So I might have > this: > > ORDER1 / note1-code12 / note2-code7 > ORDER2 > ORDER3 / note3-code7 > > I'm trying to obtain a derived column that is 1 if an order has a code7 in > any note, otherwise 0. I tried this: > > SELECT WORKORDER.ORDER_ID, > CASE WHEN ORDERNOTE.CODE=7 THEN 1 ELSE 0 END AS HAS7 > FROM ORDER > LEFT JOIN ORDERNOTE ON ORDERNOTE.ORDER_ID=ORDER.ORDER_ID > > What I wanted to see was this: > > ORDERID HAS7 > ------------------- > 1 1 > 2 0 > 3 1 > > But due to the LEFT JOIN what I see is this (doubled ORDER1): > > ORDERID HAS7 > ------------------- > 1 0 > 1 1 > 2 0 > 3 1 > > I see why, but I don't see whether I can construct a SQL query to give me > what I wanted - "if an order has ANY note with a code7, set has7=1 else 0". > Anybody know a way to accomplish that in SQL? Basically I'm trying to get > this info into a single resulting datatable to send down to a PDA via a > webservice so I didn't want to have to also send all the notes down and make > this determination on the PDA. > > Thanks. > > Tim > This can work too.
SELECT WORKORDER.ORDER_ID , CASE WHEN EXISTS ( SELECT * FROM ORDERNOTE WHERE ORDERNOTE.ORDER_ID = ORDER.ORDER_ID AND ORDERNOTE.CODE=7 ) THEN 1 ELSE 0 END AS HAS7 FROM ORDER Show quote "Tim Johnson" <TimJohn***@discussions.microsoft.com> wrote in message news:AA4D26B5-3DA6-427A-BBDF-F584D0AF82F1@microsoft.com... >I have ORDER records that can have 0 or more associated ORDERNOTE records. >A > CODE in ORDERNOTE tells me if a certain condition is true. So I might > have > this: > > ORDER1 / note1-code12 / note2-code7 > ORDER2 > ORDER3 / note3-code7 > > I'm trying to obtain a derived column that is 1 if an order has a code7 in > any note, otherwise 0. I tried this: > > SELECT WORKORDER.ORDER_ID, > CASE WHEN ORDERNOTE.CODE=7 THEN 1 ELSE 0 END AS HAS7 > FROM ORDER > LEFT JOIN ORDERNOTE ON ORDERNOTE.ORDER_ID=ORDER.ORDER_ID > > What I wanted to see was this: > > ORDERID HAS7 > ------------------- > 1 1 > 2 0 > 3 1 > > But due to the LEFT JOIN what I see is this (doubled ORDER1): > > ORDERID HAS7 > ------------------- > 1 0 > 1 1 > 2 0 > 3 1 > > I see why, but I don't see whether I can construct a SQL query to give me > what I wanted - "if an order has ANY note with a code7, set has7=1 else > 0". > Anybody know a way to accomplish that in SQL? Basically I'm trying to get > this info into a single resulting datatable to send down to a PDA via a > webservice so I didn't want to have to also send all the notes down and > make > this determination on the PDA. > > Thanks. > > Tim > Thanks for all the excellent suggestions, I'll start trying them out. I
wasn't familiar enough with all the ways to nest statements inside a SELECT so these were all good for my general education! Tim Show quote "Farmer" wrote: > This can work too. > > SELECT WORKORDER.ORDER_ID > , > CASE WHEN EXISTS > ( > SELECT * > FROM ORDERNOTE > WHERE ORDERNOTE.ORDER_ID = ORDER.ORDER_ID > AND ORDERNOTE.CODE=7 > ) > THEN 1 ELSE 0 END AS HAS7 > FROM ORDER > > > "Tim Johnson" <TimJohn***@discussions.microsoft.com> wrote in message > news:AA4D26B5-3DA6-427A-BBDF-F584D0AF82F1@microsoft.com... > >I have ORDER records that can have 0 or more associated ORDERNOTE records. > >A > > CODE in ORDERNOTE tells me if a certain condition is true. So I might > > have > > this: > > > > ORDER1 / note1-code12 / note2-code7 > > ORDER2 > > ORDER3 / note3-code7 > > > > I'm trying to obtain a derived column that is 1 if an order has a code7 in > > any note, otherwise 0. I tried this: > > > > SELECT WORKORDER.ORDER_ID, > > CASE WHEN ORDERNOTE.CODE=7 THEN 1 ELSE 0 END AS HAS7 > > FROM ORDER > > LEFT JOIN ORDERNOTE ON ORDERNOTE.ORDER_ID=ORDER.ORDER_ID > > > > What I wanted to see was this: > > > > ORDERID HAS7 > > ------------------- > > 1 1 > > 2 0 > > 3 1 > > > > But due to the LEFT JOIN what I see is this (doubled ORDER1): > > > > ORDERID HAS7 > > ------------------- > > 1 0 > > 1 1 > > 2 0 > > 3 1 > > > > I see why, but I don't see whether I can construct a SQL query to give me > > what I wanted - "if an order has ANY note with a code7, set has7=1 else > > 0". > > Anybody know a way to accomplish that in SQL? Basically I'm trying to get > > this info into a single resulting datatable to send down to a PDA via a > > webservice so I didn't want to have to also send all the notes down and > > make > > this determination on the PDA. > > > > Thanks. > > > > Tim > > > > >  |
|||||||||||||||||||||||
Other interesting topics