Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    17

    Unanswered: Help with a View

    The following is a View named Goya
    --------------
    Select
    t1.Invid1 as Invid1, t2.Invid as Invid2
    From malta t1
    Join malta t2 on t1.Tktnbr = t2.Oldtkt
    Left Join malta t3 on t2.Tktnbr = t3.Oldtkt
    Left join malta t4 on t3.Tktnbr = t4.Oldtkt
    ---------------

    The following statement queries the above view to return the final result that I need. I am struggling to incorporate it in the above select statement to make it a complete query, such that instead of querying the above view, all I need is just run the complete view.
    --------------
    SELECT distinct A.Invid1, B.Invid2 From Goya A
    Left Join Goya B on A.Invid1 = B.Invid2 Where B.invid2 is null;
    --------------
    Any help will be much appreciated.

  2. #2
    Join Date
    Oct 2014
    Posts
    2
    <<Query is not tested it can be combined like this >>

    select distinct GoyaA.Invid1, GoyaB.Invid2 FROM
    (Select
    DISTINCT t1.Invid1 as Invid1, t2.Invid as Invid2
    From malta t1
    Join malta t2 on t1.Tktnbr = t2.Oldtkt
    Left Join malta t3 on t2.Tktnbr = t3.Oldtkt
    Left join malta t4 on t3.Tktnbr = t4.Oldtkt) GoyaA
    LEFT JOIN
    (Select
    DISTINCT t1.Invid1 as Invid1, t2.Invid as Invid2
    From malta t1
    Join malta t2 on t1.Tktnbr = t2.Oldtkt
    Left Join malta t3 on t2.Tktnbr = t3.Oldtkt
    Left join malta t4 on t3.Tktnbr = t4.Oldtkt) AS GoyaB
    ON GoyaA.Invid1 = GoyaB.Invid2
    Where GoyaB.invid2 is null;

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why did you added the following two Left Joins?
    Left Join malta t3 on t2.Tktnbr = t3.Oldtkt
    Left join malta t4 on t3.Tktnbr = t4.Oldtkt

    No column of t3 or t4 was not in select list.
    So, the Left Joins might be no use.

  4. #4
    Join Date
    May 2012
    Posts
    17
    Thanks dev.mysqldba, that works great.

    @tonkuma:
    Yes, they are of great use. This is an attempt to solve an issue that I posted here:
    http://www.dbforums.com/showthread.p...ulti-head-tree

    The above query is definitely a step forward, but still a few steps away from the final result.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    ...

    No column of t3 or t4 was not in select list.
    So, the Left Joins might be no use.
    Your View named Goya was
    --------------
    Select
    t1.Invid1 as Invid1, t2.Invid as Invid2
    From malta t1
    From malta t1
    Join malta t2 on t1.Tktnbr = t2.Oldtkt
    Left Join malta t3 on t2.Tktnbr = t3.Oldtkt
    Left join malta t4 on t3.Tktnbr = t4.Oldtkt
    ---------------
    If changed the view like
    ---------------
    Select
    t1.Invid1 as Invid1, t2.Invid as Invid2
    From malta t1
    From malta t1
    Join malta t2 on t1.Tktnbr = t2.Oldtkt
    ---------------

    What difference would came up from your view Goya?

  6. #6
    Join Date
    May 2012
    Posts
    17
    As explained in the above link, a Tktnbr can be exchanged (and moved) from 1 invoice to another and to another, etc.... In the process a new tktnbr is assigned replacing the OldTkt#
    The simple/1st join returns records where there was 1 exchange.
    The 1st left Join adds cases where there was a 2nd exchange,
    The 2nd left Join adds cases where there was a 3rd exchange.
    Although this process can go on to a 4th Xchange, 5th Xchange, etc,,, I stopped at the 3rd, because historically we never had such cases.

    We are not interested in Tktnbrs, we focus on Invoices.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You don't understand my point of issue.

    I focused on the view Goya
    and the query which you provided
    --------------
    SELECT distinct A.Invid1, B.Invid2 From Goya A
    Left Join Goya B on A.Invid1 = B.Invid2 Where B.invid2 is null;
    --------------
    Even if a Tktnbr was exchanged to 3rd, 4th, so on....,
    the view Goya don't interest in the extra exchanges.
    Because, it(Goya view) select (only) t1.Invid1 as Invid1, t2.Invid as Invid2,
    not mentioned to t3.Invid or t4.Invid in the final select list.

    If you are interested in 3rd, 4th exchanges in the view Goya, you should amend the view Goya and the query using the view.


    Anyway,
    It would be useless for me to be involved in this thread,
    until you understand my point of issue.

Posting Permissions

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