0

I'm getting a problem using user-variables in MYSQL. Here's the code I've written. This isn't producing any errors but is not showing the desired results.

set @org_id_list := (select c.id from tbl_companyinformations c
left join tbl_users u on c.user_id = u.id
where u.email_address LIKE '%pdatar%'
);
select * from tbl_c2c_offers where 
seller_org_id IN (@org_id_list) or buyer_org_id IN (@org_id_list)

But then when I run this in MySQL, it doesn't return the required rows. If I copy the select statement used to define the user variable in the where statement of the second select statement, the required results are shown. What I mean is, the following code produces the required results.

select * from tbl_c2c_offers where 
seller_org_id IN (select c.id from tbl_companyinformations c
    left join tbl_users u on c.user_id = u.id
    where u.email_address LIKE '%pdatar%'
) or 
buyer_org_id IN (select c.id from tbl_companyinformations c
    left join tbl_users u on c.user_id = u.id
    where u.email_address LIKE '%pdatar%'
);

What am I doing wrong the first time around? As I said, the statements don't cause any errors, but the required results are not shown.

Thanks in advance for your time in helping me.

Cheers,

Prathamesh

Prathamesh Datar
  • 375
  • 1
  • 4
  • 20
  • 1
    Use the second version. In the first version, you seem to be trying to store a list of values in a variable and then using them using `in`. You can't really get that to work, unless you use dynamic SQL. – Gordon Linoff Jul 01 '15 at 01:21
  • I am using the second version right now but that just seems so inefficient, because I'm having to do a select statement twice which is returning the same values. I'll have to read in Dynamic SQL then. – Prathamesh Datar Jul 01 '15 at 01:29
  • 1
    If so, store the results in a temporary table, not a string. – Gordon Linoff Jul 01 '15 at 01:34

0 Answers0