Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Combining two queries – UNION with a JOIN

    Hi all—
    I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.
    Query 1
    SELECT
    e1.File_NBR,
    e1.Division,
    e1.Department,
    e1.Program,
    e1.UNIT,
    (e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,
    (e5.Mngr_FName + ' ' + e5.Mngr_LName) AS VP,
    (e4.Mngr_FName + ' ' + e4.Mngr_LName) AS AVP,
    (e3.Mngr_FName + ' ' + e3.Mngr_LName) AS Director,
    (e2.Mngr_FName + ' ' + e2.Mngr_LName) AS AD,
    (e1.Mngr_FName + ' ' + e1.Mngr_LName) AS Supervisor,
    (e1.First_Name + ' ' + e1.Last_Name) AS Case_Planner
    FROM
    [New_EEs].[dbo].[vw_SBC_Employee_Info] e1
    JOIN
    [New_EEs].[dbo].[vw_ADPFile] e2
    on e1.Supervisor_Position_NBR=e2.POSITION_NBR
    join
    [New_EEs].[dbo].[vw_ADPFile] e3
    on e2.Supervisor_Position_NBR=e3.POSITION_NBR
    left join
    [New_EEs].[dbo].[vw_ADPFile] e4
    on e3.Supervisor_Position_NBR=e4.POSITION_NBR
    left join
    [New_EEs].[dbo].[vw_ADPFile] e5
    on e4.Supervisor_Position_NBR=e5.POSITION_NBR
    left join
    [New_EEs].[dbo].[vw_ADPFile] e6
    on e5.Supervisor_Position_NBR=e6.POSITION_NBR
    order by e1.UNIT;
    Query 2
    SELECT
    'Skill Rating' as Assessment_Type,
    bs.File_NBR as ID,
    bs.Skill_NBR,
    bs.Best_Score as Score,
    bs.Assesment_RND
    FROM
    [New_EEs].[dbo].[SBC_Best_Scores] bs
    union
    SELECT
    sd.Assessment_Type,
    sd.EXAMINEE_NBR as ID,
    Skill_NBR,
    sd.SCORE,
    sd.SBC_RND
    FROM
    [New_EEs].[dbo].[vw_SBC_Score_Details]sd
    order by ID,Assessment_Type;

    Again I’m finding this tricky any assistance will be greatly aperciated!

    Thanks-

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    What is the problem? Does your first query fail (if so, how), or are you having trouble joining the two output sets together?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Can you post and then change this schema?

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible and not local dialect.

    This is minimal polite behavior on SQL forums. What you did post is full of bad programming practices. We refer to the prefix “VW_” as “Volkswagen programming”, we do not format data for display in the database like you did with names; that is what presentation layers do in any tiered architecture.

    Think about ISO-11179 and table names like “SBC_Employee_Info”; only one employee? An SQL programmer thinking in sets would use the collective “SBC_Personnel” for this table. Also, do you have lots of tables that are not “_info” and why? What do they hold? The only option I know is constants.

    UNION is seldom used in a valid schema. More than one LEFT OUTER JOIN in a query is insanely rare. It would mean that you have no DRI that is enforced with PK-FK constraints (sure wish we had some DDL, hint, hint).

    Why do you think that e1, e2, etc. is going to help anyone read or maintain this code? In the days of file systems, the physical drives that held the files (tables) were sequentially numbered. You have re-invented 1950's tape drives!

    When you have spend three decades looking at bad SQL, there are lots of bad code smells. You did not space around = and that smell is very strong. We did that on punch cards in the 1960's because we only had 80 characters to work with. It will work in SQL, but it tells that your mindset is still back there in files and punch cards.

    Why do you have a table named “SBC_Best_Scores”? Being best is an attribute of a score found by a query, not a totally different kind of entity. But with punch cards, we would have sorted those “unit records” (aka cards) into a new deck for further processing.

    Did you notice that you are self-outer-joining a table named “ADP_Files” over and over? And using absurd aliases to do it? The name describes how the data was stored (files, non-RDBMS) but not what the data is by its nature.

    Self-outer joins are the dead fish of bad code smells. When you see
    “E3.supervisor_position_nbr = E4.position_nbr” it screams that you have a 1950's assembly language pointer chain written in SQL. An adjacency list model for the org chart in 2013! Really?

    Did you see how vague and variable your data element names are?

    If you really want help, then post the DDL, like you should have in the first place, and we can try to re-write this mess. 85-95% of the real work in SQL is done with DDL and when it is this screwed up the system degrades exponentially over time with a catastrophe point

    If you cannot change it, update your resume and start job hunting. You are on a sinking ship. Trust me; I wrote the standards and have been doing this for awhile

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Celko, I have to ask. Do you have a template for these posts? :P

    On a serious note, as someone who fell into programming by accident (my formal education was geared up to work full-time with plants!), I'm always interested to read what you write. Regarding the table for an org chart, I should point out that the system selected by our HR department works on this model. How would you implement one?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by weejas View Post
    Regarding the table for an org chart, I should point out that the system selected by our HR department works on this model. How would you implement one?
    Probably something like this.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Thanks - I might have to get that! I'm amused that searching for it on amazon.co.uk has it at about half the price...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Org charts and personnel

    Celko, I have to ask. Do you have a template for these posts? :P
    Of course! After 20+ years of seeing exactly the same mistakes and misconceptions over and over, I got tired of typing. I get the feeling that the posers have a set of templates ..

    Could be worse. When Robert Bloch was a fiction editor at PLAYBOY, he had rubber stamps of increasing sizes that began with "mouseshit" and went to "horseshit" for commenting on manuscripts.

    Regarding the table for an org chart, I should point out that the system selected by our HR department works on this model. How would you implement one?
    I am a big user of the Nested Sets model. You can Google it or buy a copy of my TREES & HIERARCHIES (I have a mortgage). The organizational structure table is separated from the Personnel. This makes vacancy easy to model, re-organizations and summaries are instantaneous, etc. If you are into Matrix management, it is just another organizational structure table.

    Kids get the Nested Sets immediately; it is XML tags in an SQL disguise. Old farts want to see fake pointer chains build with cursors and OUTER JOINs because they are still locked in assembly language programming.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    Kids get the Nested Sets immediately; it is XML tags in an SQL disguise. Old farts want to see fake pointer chains build with cursors and OUTER JOINs because they are still locked in assembly language programming.
    ...or it could be that there are advantages and disadvantages to each method...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    I never found one ..

    ...or it could be that there are advantages and disadvantages to each method...
    I have never found an advantage to adjacency lists, if they are done correctly.
    Most AL programmers do not bother to prevent cycles, not even a simple "CHECK (boss_emp_id <> subordinate_emp_id)" ! They do not assure one and only one root. They do not prevent orphans. Aggregations have to do done with cursors, loops and/or outer joins. Etc.

    It is easier to insert a new subtree into an AL table, but only if you do not care about data integrity and just use the most naive version.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    I have never found an advantage to adjacency lists
    Hmmm.......
    Quote Originally Posted by Celko View Post
    It is easier to insert a new subtree into an AL table
    Also faster to insert.
    Also easier to code.
    Also allow more complex (multiple simultaneous) relationships.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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