Multiple Table Join

ruby-talk
1

Hi, I am not sure if this is the rigth place to post this or not. The
file is .rb so I will give it a shot. I am trying to join a 3rd table to
my code that currently joins 2 tables. The original code was written by
a freelancer that my company used to use. We just need info from one
field in a 3rd table so I thought I would give it a go myself and got
stumped by this section of code. I am trying to add
FINMAIN2.VehicleSaleType where FINMAIN1.DealNumber =
FINMAIN2.DealNumber. The first section of code is what he is using. The
second section is my modified version that is not working correctly. I
am just not sure what I am doing wrong.

sales_begin = Time.now
select = "SELECT
FINMAIN1.DealNumber,FINCOMMISSION.SalespersonNumber,FINCOMMISSION.SalespersonName,FINCOMMISSION.SalespersonType
"
from = "FROM FINMAIN1 "
join = "LEFT OUTER JOIN FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber "
where = "WHERE ((FINMAIN1.DateSold >= '#{first}' AND FINMAIN1.DateSold <
'#{today}') AND (FINMAIN1.BuyerType='R' OR FINMAIN1.BuyerType IS NULL)
AND (FINMAIN1.DealNumberType='') AND (FINCOMMISSION.DealNumberType='')
AND (FINCOMMISSION.SalespersonType='1' OR
FINCOMMISSION.SalespersonType='2'))"
order = " ORDER BY FINCOMMISSION.SalespersonType DESC"
sales = db.select_all(select + from + join + where + order)
db.disconnect # close mainframe connection
logger.puts "\t(#{sales.length}) sales records found in
#{sec2min(Time.now - sales_begin)}"
flagged_deals = []

sales_begin = Time.now
select = "SELECT
FINMAIN1.DealNumber,FINCOMMISSION.SalespersonNumber,FINCOMMISSION.SalespersonName,FINCOMMISSION.SalespersonType,FINMAIN2.VehicleSaleType
"
from = "FROM FINMAIN1 "
join = "LEFT OUTER JOIN ((FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber) AND (FINMAIN2 ON FINMAIN1.DealNumber =
FINMAIN2.DealNumber)) "
where = "WHERE ((FINMAIN1.DateSold >= '#{first}' AND FINMAIN1.DateSold <
'#{today}') AND (FINMAIN1.BuyerType='R' OR FINMAIN1.BuyerType IS NULL)
AND (FINMAIN1.DealNumberType='') AND (FINMAIN2.DealNumberType='') AND
(FINCOMMISSION.DealNumberType='') AND (FINCOMMISSION.SalespersonType='1'
OR FINCOMMISSION.SalespersonType='2'))"
order = " ORDER BY FINCOMMISSION.SalespersonType DESC"
sales = db.select_all(select + from + join + where + order)
db.disconnect # close mainframe connection
logger.puts "\t(#{sales.length}) sales records found in
#{sec2min(Time.now - sales_begin)}"
flagged_deals = []

···

--
Posted via http://www.ruby-forum.com/.

2

Hi, I am not sure if this is the rigth place to post this or not. The
file is .rb so I will give it a shot. I am trying to join a 3rd table to
my code that currently joins 2 tables. The original code was written by
a freelancer that my company used to use. We just need info from one
field in a 3rd table so I thought I would give it a go myself and got
stumped by this section of code. I am trying to add
FINMAIN2.VehicleSaleType where FINMAIN1.DealNumber =
FINMAIN2.DealNumber. The first section of code is what he is using. The
second section is my modified version that is not working correctly. I
am just not sure what I am doing wrong.

Including error messages and / or stack traces usually helps us a
great deal to help you.

sales_begin = Time.now
select = "SELECT
FINMAIN1.DealNumber,FINCOMMISSION.SalespersonNumber,FINCOMMISSION.SalespersonName,FINCOMMISSION.SalespersonType
"
from = "FROM FINMAIN1 "
join = "LEFT OUTER JOIN FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber "
where = "WHERE ((FINMAIN1.DateSold >= '#{first}' AND FINMAIN1.DateSold <
'#{today}') AND (FINMAIN1.BuyerType='R' OR FINMAIN1.BuyerType IS NULL)
AND (FINMAIN1.DealNumberType='') AND (FINCOMMISSION.DealNumberType='')
AND (FINCOMMISSION.SalespersonType='1' OR
FINCOMMISSION.SalespersonType='2'))"
order = " ORDER BY FINCOMMISSION.SalespersonType DESC"
sales = db.select_all(select + from + join + where + order)
db.disconnect # close mainframe connection
logger.puts "\t(#{sales.length}) sales records found in
#{sec2min(Time.now - sales_begin)}"
flagged_deals =

sales_begin = Time.now
select = "SELECT
FINMAIN1.DealNumber,FINCOMMISSION.SalespersonNumber,FINCOMMISSION.SalespersonName,FINCOMMISSION.SalespersonType,FINMAIN2.VehicleSaleType
"
from = "FROM FINMAIN1 "
join = "LEFT OUTER JOIN ((FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber) AND (FINMAIN2 ON FINMAIN1.DealNumber =
FINMAIN2.DealNumber)) "
where = "WHERE ((FINMAIN1.DateSold >= '#{first}' AND FINMAIN1.DateSold <
'#{today}') AND (FINMAIN1.BuyerType='R' OR FINMAIN1.BuyerType IS NULL)
AND (FINMAIN1.DealNumberType='') AND (FINMAIN2.DealNumberType='') AND
(FINCOMMISSION.DealNumberType='') AND (FINCOMMISSION.SalespersonType='1'
OR FINCOMMISSION.SalespersonType='2'))"
order = " ORDER BY FINCOMMISSION.SalespersonType DESC"
sales = db.select_all(select + from + join + where + order)
db.disconnect # close mainframe connection
logger.puts "\t(#{sales.length}) sales records found in
#{sec2min(Time.now - sales_begin)}"
flagged_deals =

Can you really join two tables with a single JOIN with your RDBMS? I
am not aware of any which support this syntax of yours:

LEFT OUTER JOIN ((FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber) AND (FINMAIN2 ON FINMAIN1.DealNumber =
FINMAIN2.DealNumber))

I would have expected something like

LEFT OUTER JOIN FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber,
LEFT OUTER JOIN FINMAIN2 ON FINMAIN1.DealNumber =
FINMAIN2.DealNumber

Btw, you should probably rewrite this code to use bind variables.
This is much less error prone and less prone to SQL injection attacks.

Kind regards

robert

···

On Wed, Nov 3, 2010 at 4:11 PM, Michael Brennan <michael.brennan@koons.com> wrote:

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/