|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now! |
|
#1
March 1st, 2003, 12:06 PM
|
|||
|
|||
|
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
March 1st, 2003, 06:31 PM
|
||||
|
||||
|
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
|
|
#3
March 1st, 2003, 07:47 PM
|
|||
|
|||
|
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.
|
|
#4
March 2nd, 2003, 01:20 PM
|
||||
|
||||
|
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
|
|
#5
March 2nd, 2003, 03:04 PM
|
|||
|
|||
|
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.
|
|
#6
March 2nd, 2003, 03:33 PM
|
|||
|
|||
|
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.
|
|
#7
March 2nd, 2003, 03:55 PM
|
||||
|
||||
|
"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
|
|
#8
March 2nd, 2003, 04:07 PM
|
|||
|
|||
|
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.
|
|
#9
March 2nd, 2003, 04:24 PM
|
||||
|
||||
|
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'
|
|
#10
March 2nd, 2003, 08:02 PM
|
|||
|
|||
|
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!
|
|
#11
March 2nd, 2003, 08:41 PM
|
||||
|
||||
|
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
|
|
#12
March 2nd, 2003, 08:55 PM
|
|||
|
|||
|
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.
|
|
#13
March 2nd, 2003, 09:10 PM
|
||||
|
||||
|
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
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
