Forum: Ruby on Rails Mysql query is not working

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Venkat E. (Guest)
on 2009-05-14 13:34
Please,

Can anyone help me to execute this query?


SELECT * FROM table WHERE customer_id IN (SELECT IF(1 <> 2,'SELECT
customer_id FROM customers','SELECT customer_id FROM company'))

Regards...
Rob B. (Guest)
on 2009-05-14 17:30
(Received via mailing list)
On May 14, 2009, at 5:34 AM, Venkat E. wrote:
> Please,
>
> Can anyone help me to execute this query?
>
> SELECT * FROM table WHERE customer_id IN (SELECT IF(1 <> 2,'SELECT
> customer_id FROM customers','SELECT customer_id FROM company'))
>
> Regards...
> --


No!

It looks like you're trying to do either:

SELECT table.* FROM table INNER JOIN customers ON
customers.customer_id = table.customer_id

or:

SELECT table.* FROM table INNER JOIN company ON company.customer_id =
table.customer_id

So you can do either of those (after you substitute for 'table' of
course)

If this is Rails (ActiveRecord), then ask your question again with
some more background and perhaps a bit of code (not SQL) and you'll
get a better answer.  (And that answer will probably show you how to
use has_many and belongs_to associations in your models.)

-Rob

Rob B.    http://agileconsultingllc.com
removed_email_address@domain.invalid
Venkat E. (Guest)
on 2009-05-15 08:50
Rob B. wrote:
> On May 14, 2009, at 5:34 AM, Venkat E. wrote:
>> Please,
>>
>> Can anyone help me to execute this query?
>>
>> SELECT * FROM table WHERE customer_id IN (SELECT IF(1 <> 2,'SELECT
>> customer_id FROM customers','SELECT customer_id FROM company'))
>>
>> Regards...
>> --
>
>
> No!
>
> It looks like you're trying to do either:
>
> SELECT table.* FROM table INNER JOIN customers ON
> customers.customer_id = table.customer_id
>
> or:
>
> SELECT table.* FROM table INNER JOIN company ON company.customer_id =
> table.customer_id
>
> So you can do either of those (after you substitute for 'table' of
> course)
>
> If this is Rails (ActiveRecord), then ask your question again with
> some more background and perhaps a bit of code (not SQL) and you'll
> get a better answer.  (And that answer will probably show you how to
> use has_many and belongs_to associations in your models.)
>
> -Rob
>
> Rob B.    http://agileconsultingllc.com
> removed_email_address@domain.invalid




Thanks... Rob B.

I got the solution for that...below code is working fine for me... :-)


SELECT * FROM books WHERE customer_id IN  (SELECT customer_id FROM
customers WHERE (IF('#{user_type}' <> 2,customer_id IS NOT
NULL,company_id='"cus01"')))
Marnen L. (Guest)
on 2009-05-16 10:16
Venkat E. wrote:
[...]
> Thanks... Rob B.
>
> I got the solution for that...below code is working fine for me... :-)

You completely missed Rob's point.  If you're using Rails, then in most
cases you should be letting ActiveRecord write the SQL, *not writing SQL
yourself*!

>
>
> SELECT * FROM books WHERE customer_id IN  (SELECT customer_id FROM
> customers WHERE (IF('#{user_type}' <> 2,customer_id IS NOT
> NULL,company_id='"cus01"')))

This is a waste of time (and the syntax is wrong).  What you want is
probably something like

if user_type == 2
  books = Customer.find_all_by_company_id('cus01').books
else
 books = Customer.find(:all, :conditions => 'company_id is not
null').books
end

See?  Much cleaner.  If you're going to use Rails, take the time to
learn how to work *with* it, not against it.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
removed_email_address@domain.invalid
This topic is locked and can not be replied to.