Results 1 to 13 of 13

Thread: Join problem

  1. #1
    Join Date
    Feb 2010
    Posts
    38

    Unanswered: Join problem

    Hi!

    This is probably a simple question, but my, also quite simple it seems, mind just can't figure it out.

    I have this SQL query:

    SELECT
    i.ItemCode,
    i.ItemName,
    IFNULL(v.Version, ' ') AS 'Version',
    SUM(inl.Qty) AS 'sum'
    FROM PUB.ItemVers AS v
    INNER JOIN PUB.Item AS i ON v.ItemCode=i.ItemCode
    INNER JOIN PUB.ItemGrp AS g ON i.ItemGrp=g.ItemGrp
    LEFT JOIN PUB.InvLine AS inl ON i.ItemCode=inl.ItemCode AND v.Version=inl.Version
    LEFT JOIN PUB.Invoice AS inv ON inl.InvNum=inv.InvNum AND inv.InvoiceDate BETWEEN TO_DATE ('" & startdate & "') AND TO_DATE ('" & enddate & "')
    WHERE g.ItemGrp = 'someGrp'
    GROUP BY v.Version, i.ItemName, i.ItemCode

    With the second LEFT JOIN it selects all "versions", as it should, but the sum is for all rows in table InvLine for that particular version, it doesn't care about the date. If I change it to INNER JOIN it gets the sum right but only selects "versions" with rows in table InvLine.

    So I would like it to select all versions and for a specific date in table Invoice. It's a Progress database.

    Thanks for any help!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Since you are not selecting anything from that table and it is an outer join, meaning it does not limit any rows being returned, why not just remove it from the statement?

    Dave Nance

  3. #3
    Join Date
    Feb 2010
    Posts
    38
    I want to select all items and left join table invLines, but I also need to get the invoice date from table Invoice.

  4. #4
    Join Date
    Feb 2010
    Posts
    38

    Anyone?

    Did my question not pass the eligible-question-threshold or what? I still need some help on this one... Thank you!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by tosa View Post
    Hi!
    ..It's a Progress database.

    Thanks for any help!

    so its probably best to ask in the Postgres forum
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2010
    Posts
    38
    Excuse me, what?

    Did you think about how incredibly illiterate I'd have to be to write "Progress" instead of "Postgres". There is such a thing as a Progress database, you know...

  7. #7
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by tosa View Post
    Did you think about how incredibly illiterate I'd have to be to write "Progress" instead of "Postgres".
    I think dyslexic is the word you're looking for, but the mistake is understandable, considering the circumstances. Either way, I'm sure healdem spent considerable time thinking about it, and then obviously went out of his way to personally insult you, even going so far as to move your post into a forum he thought might give you better help, thus cleverly baiting you into using another incorrect word. You handled it quite well, imo, especially considering we're all being compensated for taking time to provide you assistance.

    Quote Originally Posted by tosa View Post
    Did my question not pass the eligible-question-threshold or what?
    Actually, I think Dave already tried to help you, which you categorically rejected, seemingly without even giving a moment's thought to what he was saying.

    Quote Originally Posted by tosa View Post
    I also need to get the invoice date from table Invoice.
    You keep saying this, yet as was already pointed out, the invoice date does not appear anywhere in your SELECT statement. So, either you're missing something in your SELECT statement, or you're needlessly joining on the Invoice table, or you don't understand what you're trying to do and your statement is completely wrong. Unfortunately, nobody else understands what you want either. Perhaps showing us some desired, sample output would enlighten us on what you're trying to accomplish.
    Last edited by futurity; 01-13-11 at 13:00.

  8. #8
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Where's the like button for futurity's post?

  9. #9
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    It sounds like there may be either an:
    inl.date = inv.date
    or inl.version = inv.version
    missing from your inv join clause.

  10. #10
    Join Date
    Feb 2010
    Posts
    38
    Sorry, I didn't want to come of as ungrateful or anything, I'm really thankful for all the help I'm getting here. And, yeah, dyslexic would have been a better word in that context...

    I'll try to explain a little better. Here's the basic structure:

    tblINVOICE
    InvoiceNumber, InvoiceDate

    tblINVOICELINE
    InvoiceNumber, Item, Version, Qty

    tblITEM
    Item, ItemName, ItemGrp

    tblITEMGRP
    ItemGrp

    There's one row for one invoice. One invoice has several invoice lines. I want to select all items in an item group, and get the quantities from the invoice lines, if there is any, but only between certain dates which are found in tblInvoice, which is linked to tblInvoiceLine with InvoiceNumber. tblInvoiceLine does not hold any dates. I don't want to select the dates from tblInvoice but I want to limit the invoice lines by them.

    Thanks again!

  11. #11
    Join Date
    May 2008
    Posts
    277
    Code:
    select
        ItemGrp,
        sum(qty)
    from
        tblINVOICELINE
        inner join tblITEM
            on tblINVOICELINE.Item = tblITEM.Item
    where 
        InvoiceNumber in (
            select InvoiceNumber
            from tblINVOICE
            where InvoiceDate between <start_date> and <end_date>
        )
    group by ItemGrp
    You'll have to supply the appropriate start and end dates.

  12. #12
    Join Date
    Feb 2010
    Posts
    38
    Nice, dude! That did it! Thank you very much, futurity!

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by tosa View Post
    Excuse me, what?

    Did you think about how incredibly illiterate I'd have to be to write "Progress" instead of "Postgres". There is such a thing as a Progress database, you know...
    my apologies, some times on a drive by moderation you see things that aren't in the original post. I just presumed it was a typo...
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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