Re: MySQL Join via Lasso documentation?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: MySQL Join via Lasso documentation?

Doug Gentry
Well - that falls under the general category of issuing SQL statements  
in Lasso, which you might not have done before in the older version.

Start on page 90 of the Language Guide for 8.5

Basically, you can construct your own SQL statements, bypassing the  
usual Lasso search and other parameters.

For example, many developers set up the SQL command as a variable

var: 'query'='SELECT * FROM tablename WHERE fieldname = somevalue';

inline:
-username='xxx',
-password='yyy',
-database='aaa',
-sql=$query;
/inline;

So, then your next step is to understand how JOIN and LEFT JOIN and  
others work in SQL. There are tips in the archives here, and having a  
good MySQL reference is handy.

...Doug Gentry

On May 16, 2008, at 10:09 AM, nohl wrote:

>
> Has anyone seen a clear explanation of how to use Lasso to setup and  
> access a
> Join in MySQL.
>
> I'm not totally familiar with the concept, but I would like to study  
> it.
>
> Thanks,
> Nohl
> --
> View this message in context: http://www.nabble.com/MySQL-Join-via-Lasso-documentation--tp17279782p17279782.html
> Sent from the Lasso - Talk mailing list archive at Nabble.com.
>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>



---
Doug Gentry
Dynapolis & Southern Oregon University
p:  541-261-8501 / Toll Free: 866-890-6013
[hidden email]
www.dynapolis.com - blog: www.plain-sense.com



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: MySQL Join via Lasso documentation?

Stephen Christiansen
Nohl,

Here is a snippet (somewhat complex) showing one way to do this by  
using a SQL statement, Lasso variables, etc...

[Inline: -database='XXX', -SQL='SELECT
rm.RA_Name as RA_Name,
rm.RA_Nbr as RA_Nbr,
rm.State AS State,
at.ID AS Acct_ID,
at.Type AS Acct_Type,
at.Manuf_Cust_Nbr AS Manuf_Cust_Nbr,
at.Multi_DIR_Order AS Multi_DIR_Order,
bm.Name AS Brokerage,
dm.Name AS Distributor,
cm.Name AS Coop,
mm.Name AS Mgmt_Co,
stt.ShipTo_Name AS ShipTo_Name,
stt.Manuf_ShipTo_Nbr AS ShipTo_Nbr,
stt.Address1 AS ShipTo_Address,
stt.City AS ShipTo_City,
stt.State AS ShipTo_State,
stt.Zip AS ShipTo_Zip
FROM
RA_Master rm,
' + (var:'Account_Dist_Table') + ' ad,
Dist_Master dm,
COOP_Master cm,
Management_Master mm,
Brokerage_Master bm,
' + (var:'Accounts_Table') + ' at
LEFT JOIN ' + (var:'ShipTo_Table') + ' stt ON at.ID = stt.Account_ID
WHERE
rm.RA_Name LIKE \'%' + (var:'Local_RA_Name') + '%\' AND
rm.RA_Nbr LIKE \'%' + (var:'Local_RA_Nbr') + '%\' AND
rm.State LIKE \'' + (var:'Local_State') + '\' AND
rm.Mgmt_Co LIKE \'' + (var:'Local_Mgmt') + '\' AND
rm.Prim_COOP_ID LIKE \'' + (var:'Local_COOP') + '\' AND
rm.ID = at.RA_ID AND
at.Manuf_Cust_Nbr LIKE \'%' + (var:'Local_Manuf_Cust_Nbr') + '%\' AND
at.Status LIKE \'' + (var:'Local_Status') + '\' AND
at.Type LIKE \'' + (var:'Local_Acct_Type') + '\' AND
at.Regional_ID LIKE \'' + (var:'Local_Regional_ID') + '\' AND
at.Brokerage_ID = bm.ID AND
bm.ID LIKE \'' + (var:'Local_Brokerage_ID') + '\' AND
bm.Brokerage_Nbr LIKE \'' + (var:'Local_Brokerage_Group') + '\' AND
stt.Master_ID LIKE \'' + (var:'Local_ShipTo_ID') + '\' AND
at.ID = ad.Account_ID AND
ad.Dist_ID = dm.ID AND
cm.ID = rm.Prim_Coop_ID AND
mm.ID = rm.Mgmt_Co AND
CURRENT_DATE BETWEEN ad.Effective_Date AND ad.Term_Date AND
stt.Status = \'A\'
ORDER BY ' + (var:'Order_List') + ' ',-maxrecords='All']

Steve
On May 16, 2008, at 1:09 PM, nohl wrote:

>
> Has anyone seen a clear explanation of how to use Lasso to setup and  
> access a
> Join in MySQL.
>
> I'm not totally familiar with the concept, but I would like to study  
> it.
>
> Thanks,
> Nohl
> --
> View this message in context: http://www.nabble.com/MySQL-Join-via-Lasso-documentation--tp17279782p17279782.html
> Sent from the Lasso - Talk mailing list archive at Nabble.com.
>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/