dcsimg
Results 1 to 5 of 5

Thread: Need to get values from 2 tables but one dows not always inner join

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,322

    Need to get values from 2 tables but one dows not always inner join

    Hi.
    2 tables . One always have a value but the second one is not always has a value.
    So this will work only if vwDWTickets has a value that is also in bookingFee:

    Code:
    with tickets(admissions,Transnumber,value)
    as 
    (
    select sum(T.admissions) as admissions,T.TransNumber,sum(GrossValue) from vwDWTickets T
    where T.TransNumber  = 10550901
    group by T.TransNumber)
    
    
    select tickets.admissions,tickets.Transnumber,tickets.value + sum(tblDWBookingFee.GrossValue) from tickets
    inner join tblDWBookingFee on tickets.Transnumber = tblDWBookingFee.Transnumber
    group by tickets.admissions,tickets.Transnumber,tickets.value,tblDWBookingFee.GrossValue
    I did this with CTE becuase tblDWBookingFee can also have more than one row so with a simple join it will not give me the correct admissions count.

    So if transaction 10550901 has booking fees then no problem, if it does not then I get no rows (logical because the inner join has no joined values)

    How can I ignore the join an get all the values from "tickets" (aka vwDWTickets ) regardless if tblDWBookingFee joins with values or not?

    Thanks
    Slow as hell.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,773

    Re: Need to get values from 2 tables but one dows not always inner join

    Change to a left join...

    Inner join - return rows where there are matches on both sides
    Left Join - return rows where there are rows on the left side, even if no matches on the right
    Right Join - return rows were there are rows on the right side, even if no matches on the left
    (full) outer join - return rows from both sides, matching when possible, but also when there are no matches on either side

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,322

    Re: Need to get values from 2 tables but one dows not always inner join

    Hi.
    Yes but this will set the GrossValue value as NULL

    admissions Transnumber (No column name)
    2 8548449 NULL
    Slow as hell.

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

    Re: Need to get values from 2 tables but one dows not always inner join

    That's happening because anything + null = null. So this:-
    Code:
    tickets.value + sum(tblDWBookingFee.GrossValue)
    will return null if there are no rows in tblDWBookingFee, regardless of the value from tickets.

    The answer is quote simple. Use an IsNull on the tblDWBookingFee bit to convert nulls to zeros:-
    Code:
    tickets.value + IsNull(sum(tblDWBookingFee.GrossValue), 0)
    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

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,322

    Re: Need to get values from 2 tables but one dows not always inner join

    Ahh, those damn nulls!!!
    Thanks both.
    Slow as hell.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width