Results 1 to 3 of 3

Thread: [RESOLVED] Join 2 Tables in Mysql Help...

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2005
    Location
    Bulawayo, Zimbabwe
    Posts
    576

    Resolved [RESOLVED] Join 2 Tables in Mysql Help...

    Hi All

    I am using Mysql 5 database and VS2008

    I have two tables the first is named doc_domain and it contains the following Fields

    DocNumber
    DocDate
    FromPanel_AccCode
    ToPanel_AccCode

    The Second Table is called entities and contains Company Information as follows:
    AccType
    AccCode
    AccountName
    Address_Ln1
    Address_Ln2
    Address_Ln3
    Address_Ln4

    This Table contains information on customers, suppliers as well as our own offices
    the records are differentiated by the AccType being 'C', 'S' Or 'O'

    Transactions that take place in the domain table will always contain 2 different
    accounts codes that will be either Ours to Customer, or supplier to Ours or ours to ours


    I now need to run a query that joins these two tables together
    This query works;

    select
    `doc_domain`.`DocNumber` AS `DocNumber`,
    `doc_domain`.`DocDate` AS `DocDate`,
    `alias_from_code`.`AccCode` AS `FromCode`,
    `alias_to_code`.`AccCode` AS `ToCode`
    from
    ((`doc_domain` left join `entities` `alias_From_Code` on((`doc_domain`.`FromPanel_AccCode` = `alias_from_code`.`AccCode`))) left join `entities` `alias_To_Code` on((`doc_domain`.`ToPanel_AccCode` = `alias_to_code`.`AccCode`)))
    where
    (`doc_domain`.`DocNumber` = _latin1'POPBA00001')

    But as you can see it returns the Accounts codes but not the addresses.
    I cannot get the sql right to return the address lines for each accounts code
    to overcome this i have written 3 different views that seperate the Customers, Suppliers and our offices
    i have then joined the domain table to the views to get what i am looking for but i don't think
    that this is very efficient.
    Is it possible to obtain this by changing the above query in any way?

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,615

    Re: Join 2 Tables in Mysql Help...

    Yes, you simply need to add the address fields to the list of fields you're selecting. You've already joined to the tables so they should be available to you.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jul 2005
    Location
    Bulawayo, Zimbabwe
    Posts
    576

    Re: Join 2 Tables in Mysql Help...

    Hi Funky, thanks for that got it, i couldn't see it util you pointed it out to me
    Tks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width