1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dis-JOIN-ted..

Discussion in 'SQL PL/SQL' started by reylliam, Feb 21, 2011.

  1. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    Nevermind. I had to move forward, so I simply stopped trying to do it as one overall query and built it in UNION pieces.
    Perhaps it was overambitious to think it could be done as a single block.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Anyway , No need to delete the post :)
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    You should have left your post. Someone could have given a solution which could have helped another person, not you maybe but someone else. That's how community help works... There are hundreds of topics here which help other folks when they come here.
     
  4. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    I am happy to comply, but I will let you know that this last response does come across as a bit chastising. I would more have preferred to have seen something like "Please avoid deleting posts in the future" -- note the 'request' approach instead of the ' you should not have' scolding approach.... I will assume chastising was not the intent unless it becomes a habit..

    I removed it based on a decision combining a couple factors.

    I saw that on the first day, it had 19 views, with no response. the second day, there were 4. Having seen threads in here that have gone unresponded for *years*, I determined that this rate of decay was geometric and that the viability of receiving a response was down to 24 more hours.

    Not because people don't want to answer, or don't know the answer, but simply because once a thread drops too far down in the list, it's beyond likelihood of someone spending the time to dig that deep to read it and realize they can answer. People are constantly reading what's latest and greatest, and this would no longer be on the 'page 1' of the forum. For people new to the site,*everything* is new, so there is a potential to mark all as read to start and then begin reading what's new from the moment they join in order to remove all the new flags on every single thing, which means the thread will still go unread.

    Also, the viewpoint I had been considering when doing this was trying to save people the effort that anyone responding would no longer be helping me since I came up with my own resolution, and therefore it would allow them time to help someone else who truly needs it in some other thread. I didn't want their effort to be wasted and possibly prevent some other user from receiving timely assistance due to having spent too much time on my item.

    However, you have pointed out that the thread could potentially be used by someone else. I was not intending to deny some future potential user any needed wisdom. I will know to retain unanswered posts from here on out.

    Thanks,
    Jason
     
  5. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Jason,

    Firstly apologies if my post seemed rude, honestly that was not my intention at all.

    You are right though about unanswered threads dropping out of view. There should ideally be a link somewhere which points people to unanswered threads forum category wise. That is something i just realized as a necessity from your post. I have a developer working on some much needed improvements on this site as a whole. I will add this to his to-do list.

    See something positive came out of this thread still... :)

    Regards,
    Sadik
     
  6. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    Great because now i have another problem... :)

    A LEFT JOIN appears to be behaving as an INNER JOIN...

    Here is a query and result of query table A:
    Code (SQL):
    SELECT a.res_id, a.gst_conf_id, a.gst_last_name, a.gst_first_name

    FROM crmbkngdw.gst_conf a

    WHERE a.gst_conf_id IN
              ('C00526487278', 'C00528386933', 'C00528386934', 'C00528386248',
               'C00528386249')
    Result:
    [TABLE]RES_ID GST_CONF_ID GST_LAST_NAME GST_FIRST_NAME
    294781124 C00526487278 SMTH PAUL
    995006220 C00528386248 SMITH PAUL
    995006220 C00528386249 JONES ALLAN
    745006224 C00528386933 SMITH PAUL
    745006224 C00528386934 JONES ALLAN[/TABLE]

    3 confirmation numbers have 5 guests with rooms.

    However, only PAUL, the person occupying room #1 on each reservation, is getting contact data collected for him. So, only PAUL has rows on table B. When I do a LEFT JOIN A to B, I expect all rows from A regardless of match from B along with data for matching rows from B populating, leaving NULL for data in B columns on nonmatched rows.

    Query:
    Code (SQL):
    SELECT   a.res_id, b.gst_conf_id, a.gst_last_name, a.gst_first_name, b.comm_mthd_cd,
             b.comm_mthd_addr_text

        FROM crmbkngdw.gst_conf a LEFT JOIN crmbkngdw.gst_conf_comm b
             ON a.gst_conf_id = b.gst_conf_id

       WHERE a.gst_conf_id IN
                ('C00526487278', 'C00528386933', 'C00528386934', 'C00528386248',
                 'C00528386249')

    ORDER BY 1
    I expected to see:
    [TABLE]RES_ID GST_CONF_ID GST_LAST_NAME GST_FIRST_NAME COMM_MTHD_CD COMM_MTHD_ADDR_TEXT
    294781124 C00526487278 SMITH PAUL PHONE 555 555 1212
    294781124 C00526487278 SMITH PAUL EMAIL paul.smith@somewhere.com
    745006224 C00528386933 SMITH PAUL EMAIL paul.smith@somewhere.com
    745006224 C00528386933 SMITH PAUL PHONE 555 555 1212
    745006224 C00528386934 JONES ALLAN <NULL> <NULL>
    995006220 C00528386248 SMITH PAUL PHONE 555 555 1212
    995006220 C00528386248 SMITH PAUL EMAIL paul.smith@somewhere.com
    995006220 C00528386249 JONES ALLAN <NULL> <NULL>[/TABLE]

    But instead I am getting:
    [TABLE]RES_ID GST_CONF_ID GST_LAST_NAME GST_FIRST_NAME COMM_MTHD_CD COMM_MTHD_ADDR_TEXT
    294781124 C00526487278 SMITH PAUL PHONE 555 555 1212
    294781124 C00526487278 SMITH PAUL EMAIL paul.smith@somewhere.com
    745006224 C00528386933 SMITH PAUL EMAIL paul.smith@somewhere.com
    745006224 C00528386933 SMITH PAUL PHONE 555 555 1212
    995006220 C00528386248 SMITH PAUL PHONE 555 555 1212
    995006220 C00528386248 SMITH PAUL EMAIL paul.smith@somewhere.com[/TABLE]

    Allan's rows are missing, which is what I would expect had I done INNER JOIN. Am I missing something? I need to know those additional rooms' names and those NULL contact details in the response...

    This is really a small piece of more complicated query; I was simply able to trace it down to this piece as to what is skewing my results. I am sure it's something simple that just needs another pair of eyes to point out.

    Any insight is of course greatly appreciated.

    Thanks,
    Jason
     
  7. reylliam

    reylliam Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Austin, TX
    Just by posting this here, I solved this one. The query is so massive I simply didn't realize what I had done.

    You know what, I had stripped out some additional tests to keep this as uncluttered as possible, and it struck me that one of them was one that I had to move up from WHERE to FROM. Being in WHERE was excluding the remaining lines.

    It was one of these basic tests I have to perform on every table since it's data warehouse recordkeeping stuff, and by habit I had included it in WHERE without thinking.

    In putting them back on my screen after setting up the above email, I realized this.....LOL!

    :)
     
  8. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    I am glad you solved it. Breaking down on large programs can usually identify issues. I am myself not a developer actually, more on the functional side of applications, but in either case a modular breakdown approach usually works.