From: Robert Klemme <shortcutter@...>
Date: 2010-11-04T00:25:08+09:00
Subject: Re: Multiple Table Join

On Wed, Nov 3, 2010 at 4:11 PM, Michael Brennan
<michael.brennan@koons.com> wrote:
> 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


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