Nested Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nested Query

Author

Topic

Mark Stilwell
Starting Member

6 Posts

Posted - 2004-07-22 : 19:09:42

Sorry people for a stupid question.

Two tables are present.
1. Equipment Table
2. Equipment Component Table which links equipment to equipment (called COMP_NUM)

Is there any way to write the following query without the nested select? In english the query is basically, "Get me the component number, component name, and master equipment number for all components of the master."

SELECT
EQ_COMPONENTS.COMP_NUM AS COMP,
(SELECT EQUIPMENT."NAME" FROM EQUIPMENT WHERE EQUIP_NUM=COMP) AS COMPONENT_NAME,
EQUIPMENT.EQUIP_NUM
FROM
DS2000.EQUIPMENT EQUIPMENT,
DS2000.EQ_COMPONENTS EQ_COMPONENTS
WHERE
EQ_COMPONENTS.EQUIP_NUM='LH100-010'

I Hope this makes sense and thanks in advance.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-22 : 19:25:16

Can you post the table designs, sample data and your required output?

Makes things much easier....

Mark Stilwell
Starting Member

6 Posts

Posted - 2004-07-22 : 19:32:10

Apologies...

Equipment Table
---------------
Equip_Num
Description
...

Eq_Components Table
-------------------
Equip_Num
Comp_Num (Points back to the equipment table)

Desired output is:
Equip_Num, Comp_Num (Really from Equipment table), Comp_Description(Really from Equipment table)

Sample output might look like this:
'LH100-010','LH100-010a',"ComponentA"
'LH100-010','LH100-010b',"ComponentB"
'LH100-010','LH100-010c',"ComponentC"
'LH100-010','LH100-010d',"ComponentD"

I hope this helps and thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 19:36:07

Comp_Num relates to which column in the Equipment table?
Comp_Description? I don't see that in the tables.

You are looking for something like this though:

SELECT t1.SomeColumn, t2.SomeOtherColumn, t1.SomeDiffColumn
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.Column1 = t2.Column1
WHERE...

Tara

Mark Stilwell
Starting Member

6 Posts

Posted - 2004-07-22 : 19:41:41

The desired output is:

Equip_Num, Comp_Num (which IS Equip_Num), Comp_Description (which IS Equipment description)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 19:46:24

You've shown us the desired output, but can you show us the data in the two tables that relates to the desired output.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 19:48:01

BTW, it is considered bad design to have two columns named the same but contain different data. Equip_Num in the first table should be the same data as in the second table. If Comp_Num is how to connect to the first with Equip_Num, then they should be named the same.

Tara

Mark Stilwell
Starting Member

6 Posts

Posted - 2004-07-22 : 19:53:01

Okay here we go given the desired output of

'LH100-010','LH100-010a',"ComponentA"
'LH100-010','LH100-010b',"ComponentB"
'LH100-010','LH100-010c',"ComponentC"
'LH100-010','LH100-010d',"ComponentD"

The tables contain:

Equipment Table
---------------
Equip_Num Description
LH100-010 Master
LH100-010a ComponentA
LH100-010b ComponentB
LH100-010c ComponentC
LH100-010d ComponentD

Equipment Component Table
-------------------------
Equip_Num Comp_Num
LH100-010 LH100-010a
LH100-010 LH100-010b
LH100-010 LH100-010c
LH100-010 LH100-010d

I hope this helps and thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 19:55:50

SELECT ec.Equip_Num, ec.Comp_Num, e.Description
FROM Eq_Components ec
INNER JOIN Equipment e
ON ec.Comp_Num = e.Equip_Num

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-22 : 21:43:34

I guess that's what I get for doing work at work!

Like this:


SELECT
EQ_COMPONENTS.COMP_NUM AS COMP, 
EQUIPMENT.Name AS COMPONENT_NAME
EQUIPMENT.EQUIP_NUM

FROM DS2000.EQUIPMENT EQUIPMENT
INNER JOIN DS2000.EQ_COMPONENTS EQ_COMPONENTS ON EQ_COMPONENTS.COMP_NUM = EQUIPMENT.EQUIP_NUM

WHERE  EQ_COMPONENTS.EQUIP_NUM='LH100-010'

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources