help with the procedure
-
Hi all I am writing a procedure, which looks like this!
CREATE procedure TTT (@TID bigint) AS
SELECT
A.id , S.id , S.amount , A.Tray, XXX
FROM S INNER JOIN
A ON S.ID = A.[id]
inner JOIN
T on A.[id] = T.order_id
WHERE (Tech_ID = @TID) AND (S.Status = 'I') OR
(Tech_ID = @TID) AND (S.Status = 'R')
ORDER BY A.[id] desc
--- I need to insert this in the place of XXX --
select top 1(color)from T,A
where A.[id] = T.order_id
group by color order by count(color) desc
My table T looks like this
order_id color
11 aaa
11 aaa
11 bb
11 cc
12 zzz
12 zzz
12 vv
so, when there is A.id 11, then it should select 'aaa', when 12 then 'zzz'
how can do that? any help will be appreciated.
-
Apart from the apparent logical problems of your database design (T apparently has no key and hence allows duplicates) you could use the query as a derived table.
Note, however that the TOP operator will not reliably return any given color value, it would simply return the first value that is found so to speak. So if order 12 included the 'aaa' color, you might get 'aaa' returned for order 11, but 'zzz' would still be returned for order 12, depending on any indexes that exist and/or the order in which the rows were inserted. If you included an ORDER BY clause you could exercise a bit more control over which color was returned.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
sorry.............. forgot to add order by
select top 1(color)from T,A
where A.[id] = T.order_id
group by color order by count(color) desc
-
This should work. I tried testing it with my own data, so it is hard to know what is in your tables.... I do have difficulty understanding the OR; I have put a comment on that line...
SELECT A.id, S.id, S.amount, A.Tray, TColor.Color
FROM S
INNER JOIN A ON( S.[id] = A.[id])
INNER JOIN T ON( A.[id] = T.order_id)
INNER JOIN ( SELECT TOP 1 Color, OrderID
FROM T
GROUP BY Color, OrderID
ORDER BY COUNT( Color) DESC) TColor
ON( A.[id] = TColor.OrderID)
WHERE( Tech_ID = @TID)
AND( S.Status = 'I')
OR( Tech_ID = @TID) -- why is this repeated as an OR ?
AND( S.Status = 'R')
ORDER BY A.[id] DESC
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply