Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12

    Simple new-be query


    I'm humbled...and stuck.

    How would I do find the Description of both a part and it's parent in the same query? The queries that I tried give only the child's Description.

    Thank you in advance!

    ======Data looks something like this ========
    TBLPART

    Part_Number
    Serial_Number->PK
    Part_SKey --> Child Seq # for this part number
    Parent_SKey -> Parent Seq # to another part in this same table. Or 0 if there are no parents



    TBLPARTINFO

    Serial_Number ->PK
    Description
    Mfg_Date
    .... some other columns
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    maybe because we've never seen both child and parent links together

    that's weird

    got some example rows so we can see how the tables are related?


    rudy
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12
    If I have the fan's part number how do I find it's description and it's parent description in the same query?

    An Engine (parent)
    has a fan (child)
    and some other stuff (child)


    Tblpart row Looks like this for Engine(parent)

    PartNum SerialNum Prt_SKey Parent_Skey
    1111111 11-1111 1111118881 0 --->meaning it has no parent
    5555555 55-5555 5555555222 11111 --->meaning it has a parent

    SerialNum Description Mfg_Date
    11-111111 Engine 12/31/2002
    55-555555 Fan 01/03/2003
    Last edited by vrkelley; March 2nd, 2003 at 12:36 AM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    i still don't get it

    TBLPARTINFO is okay, that's your parts

    TBLPART is driving me crazy

    there are 4 key fields, when i would expect only two

    when i asked for samples, i meant show me the TBLPART rows for an actual assembly so that i could see which of the child and parent columns actually link to where

    if you know what i mean
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12
    Need to show that this particular fan has been installed in an engine and ready to ship (as opposed to a fan just sitting by itself on a shelf). PK for both tables is SerialNum

    Question is...In one query how can I get the fan's description + SN AND the Engine's description + S/N?

    Thax for your patience...Agg formating! How's this?

    TBLPART
    Code:
    PartNum     SerialNum     Prt_SKey     Parent_Skey
    1111111    11-1111       33333            0      --->Engine has no parent
    5555555    55-5555       55555           33333--->Fan installed in eng. Maps to Engine's Prt_SKey

    TBLPARTINFO
    Code:
    SerialNum   Description   Mfg_Date 
    11-1111     Engine          12/31/2002
    55-5555     Fan               01/03/2003
    Last edited by vrkelley; March 2nd, 2003 at 08:49 PM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12
    R937
    If I follow your wisdom from another post, perhaps a CASE stmt would work.

    For a query to show all parts, descriptions, and their parent's descriptions (if any). Can I just do a basic select to get the all parts and use the Case thing if the part is already installed in a parent part?

    How do I do both in 1 query? A basic example would be great!
    Last edited by vrkelley; March 2nd, 2003 at 03:58 PM.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    "PK for both tables is SerialNum"

    really?

    TBLPARTINFO has a SerialNum 5555555, but there is no TBLPART with the same SerialNum

    there is a TBLPART with a PartNum of 5555555 but that's not the PK, according to you

    the thing that's messing with my head is the fact that you have both child and parent keys

    if a particular fan can only be in one particular assembly (parent) then you do not need child keys, just parent keys

    is this your database or did someone else build it?

    as i said, you have 4 columns in TBLPART and i would've expected only two
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12
    Looks like we're both posting and editing the same time. Take another look at the data...

    You are correct only the Serialnum cols are PK's . And I've corrected the 55-5555 data so hopefully it's easier to read.

    I did not design the DB but have the power to change the schema of the some 60 tables. So all comments are welcome.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    okay, cool, not your design, fine, we can work with it the way it is

    there is an extra join involved, because the parts are not linked using their PKs, but some other keys altogether

    you originally asked for the description of both a part and its parent

    here it is --
    Code:
    select Fan.Description   
         , Engine.Description   
      from TBLPARTINFO Fan
    inner
      join TBLPART FanJunction
        on Fan.SerialNum  = FanJunction.SerialNum
    inner 
      join TBLPART EngineJunction
        on FanJunction.Parent_Skey  = EngineJunction.Prt_SKey
    inner
      join TABLEPARTINFO Engine
        on EngineJunction.SerialNum  = Engine.SerialNum
     where Fan.SerialNum = '55-5555'
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12
    Wow! You make that look easy!

    I didn't understand this syntax?
    join TBLPART FanJunction -> is FanJunction the name of the join?
    join TBLPART EngineJunction
    join TABLEPARTINFO Engine

    Also What improvements should be added to the TBLPARTINFO?

    Wow!
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    FanJunction is a table alias

    this is always necessary when a table occurs more than once in a query

    in this case, each of the two tables occurs twice

    improvements?

    it's probably too late for improvements

    there must be a reason why the table has the fields it does

    if you change it now, you will break existing application programs, that's for sure


    rudy
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12
    I'll try this query tomorrow. It's probably not too late to change the schema. They've got about 6mo's data and maybe 65K records in the part table. They just hired me as C++ engineer...somehow I'm the SQL guru! Ha Ha! :> Not even close!

    It would seem that the part_Skey should be added to the partinfo table. I don't like to join on the S/N (varchar).

    -V
    Last edited by vrkelley; March 2nd, 2003 at 08:58 PM.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    if you're going to (re)design the schema, you need to ask a bunch more questions

    those extra keys might be necessary, although from what you've shown, they are not

    tell them if they want to hire an sql consultant, for a short term contract, that you know where to find one: http://r937.com/

    rudy
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12

    Another solution


    Will do Rudy. This co is in Seattle and Ive already told them that what they really need is a dataware house or mart and DBA to manage it.

    Here's another solution that Pablojo posted from another thread. This "simple" question is really an inheritance question. We just got Oracle 9i so will try his solution also.

    http://otn.oracle.com/products/oracl...ily/oct04.html
    Last edited by vrkelley; March 4th, 2003 at 12:04 AM.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12
    Hey your solution really worked. Thank you so much. An they brought in a DBA Toronto today. Guess canuks make good DBAs
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo