Geek Answers Handbook

Equivalent to SQL Server IN IN INvalid Subquery

I am new to sql server, so I am really trying to translate my oracle sql address in this area. Normally in oracle sql I would use two elements in my "in", but I think this might not work so well on sql server?

Here are my details:

notes_table

a_id     |     idxno     |     note_text

1              0               text 1 for item b_id = 61
2              1               text 2 for item b_id = 61
3              0               text 1 for item b_id = 71
4              1               text 2 for item b_id = 71
5              2               text 3 for item b_id = 71
6              0               text 1 for item b_id = 81
7              0               text 1 for item b_id = 91
8              1               text 2 for item b_id = 91

notes_bridge_table

a_id     |     b_id     

1              61       
2              61       
3              71       
4              71       
5              71       
6              81       
7              91       
8              91

(** Note: I am not guaranteed that max (a_id) is max (idxno) from notes_table)

item_table

b_id     |     item_desc
61             desc of item 61
71             desc of item 71
81             desc of item 81
91             desc of item 91

My desire is to display a report of the items with the largest entry from the notes table. So something like:

results

b_id     |     item_desc         |    note
61             desc of item 61        text 2 for item b_id = 61
71             desc of item 71        text 3 for item b_id = 61
81             desc of item 81        text 1 for item b_id = 61
91             desc of item 91        text 2 for item b_id = 61

What I tried:

select item_table.b_id, item_table.item_desc, 
from item_table, notes_bridge_table
where item_table.b_id = notes_bridge_table.b_id
and notes_bridge_table.a_id in
(select a_id from notes_table
 where notes_table.a_id = notes_bridge_table.a_id
 and notes_table.idxno, notes_table.a_id in
 (select max(idxno), a_id from notes_table group by a_id))

but the second in the last line "and notes_table.idxno, notes_table.a_id in" is not displayed for sql server.

+4
source share
2

? :

SELECT i.b_id, i.item_desc, n.note_text
FROM item_table AS i INNER JOIN notes_bridge_table AS b
ON i.b_id = b.b_id
INNER JOIN notes_table n ON b.a_id = n.a_id
INNER JOIN
(SELECT b_id, MAX(idxno) AS idxno
    FROM notes_table AS n INNER JOIN notes_bridge_table AS b
    ON n.a_id = b.a_id
    GROUP BY b.b_id) AS b2
ON b.b_id = b2.b_id AND b2.idxno = n.idxno

... a_id. .

Btw, 3 b_id . :

b_id   item_desc          note_text
------ ------------------ ---------------------------
61     desc of item 61    text 2 for item b_id = 61
71     desc of item 71    text 3 for item b_id = 71
81     desc of item 81    text 1 for item b_id = 81
91     desc of item 91    text 2 for item b_id = 91
+1

( Oracle)

select * from t
where ( x, y ) in ( select x, y from t1 );

, MS-SQL:

select * from t
where exists (
  select 1 from t1
  where t1.x = t.x and t1.y = t.y 
);

Oracle: http://www.sqlfiddle.com/#!4/2300d/2
MS_SQL: http://www.sqlfiddle.com/#!3/2300d/2

+2

More articles:


All Articles