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... -- Posted via http://www.ruby-forum.com/.
On May 14, 2009, at 5:34 AM, Venkat Eee 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 Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
Rob Biedenharn wrote:> On May 14, 2009, at 5:34 AM, Venkat Eee 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 Biedenharn http://agileconsultingllc.com > Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.orgThanks... Rob Biedenharn 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"''))) -- Posted via http://www.ruby-forum.com/.
Venkat Eee wrote: [...]> Thanks... Rob Biedenharn > > 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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.