How to specify a dummy column in outer queries using MySQL?

Question

I need to perform the following query:

SELECT ID FROM tbl_a WHERE (fld_merchID, 'something') IN
(SELECT MerchID, MAX(Priority) FROM tbl_b WHERE Name LIKE 'AAA.com')

as you can image, there's no 'Priority' field in tbl_a. (I need to select the MerchID with the higher Priority value).

That query is returning empty and it shouldn't.

I'm not sure how to do it.

Thanks!

If there is no `Priority` column in `tbl_a` then why are you selecting it from `tbl_b` in the first place? — Eric Petroelje, Jan 09 '13 at 20:54
I just need to select the MerchID with the higher Priority value — content01, Jan 09 '13 at 20:56

score 0 · Answer 1 · answered Jan 09 '13 at 20:56

0

try this ,

 SELECT ID FROM tbl_a WHERE fld_merchID IN
(SELECT MerchID, MAX(Priority) FROM tbl_b  WHERE Name LIKE 'AAA.com')

answered Jan 09 '13 at 20:56

echo_Me

37,078
5
58
78

you have a missing ) in that query! – content01 Jan 09 '13 at 20:57
ehehhe okay @goodmood. No, that gives a mysql error because the inner query is returning two values and the outer query is waiting for just one. – content01 Jan 09 '13 at 21:00

score 0 · Accepted Answer · answered Jan 09 '13 at 21:06

If you want the one with the max priority, then you need to change your subquery pretty significantly to choose the MerchID with the highest priority:

SELECT ID FROM tbl_a WHERE fld_merchID IN (
  SELECT DISTINCT MerchID
  FROM tbl_b
  INNER JOIN (
    SELECT Name, MAX(Priority) as Priority
    FROM tbl_b 
    WHERE Name LIKE 'AAA.com'
    GROUP BY Name
  ) mx ON mx.Name = tbl_b.Name AND mx.Priority = tbl_b.Priority
)

How to specify a dummy column in outer queries using MySQL?

2 Answers2