Subquery Help - 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

General SQL Server Forums

New to SQL Server Programming

Subquery Help

Author

Topic

mrgalvan
Starting Member

9 Posts

Posted - 2006-07-20 : 16:17:53

I have this subquery that I'm trying to get info from the two seperate tables to show up, but I only get one or the other.

-- This report is for the DC. This report is to look at what items
-- come in a kit and what the inventory levels looks like at the
-- time the report is run. 07/18/06 mjg
SELECT I.ItemID, D.ShortDesc, KI.ItemID, KD.ShortDesc
FROM timKitCompList KL
INNER JOIN timItem I ON KL.CompItemKey = I.ItemKey
INNER JOIN timitemdescription D ON I.ItemKey = D.ItemKey
WHERE EXISTS
(SELECT KI.ItemID AS KitItemNo, KD.ShortDesc AS KitItemDesc
FROM timKit K
INNER JOIN timItem KI ON K.KitItemKey = KI.ItemKey
INNER JOIN timitemdescription KD ON KI.ItemKey = KD.ItemKey)

Here are the error messages that I get:
Server: Msg 107, Level 16, State 2, Line 4
The column prefix 'KI' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 4
The column prefix 'KD' does not match with a table name or alias name used in the query.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-20 : 16:38:56

U have 4 tables, but u mention 2 tables ?
Why do u use "Exists"
U cannot use Aliases of the Query for Exist check, in the main Query

Without knowing ur Joining / Conditions ... hard to give a solution. but seems to be not complex.

Srinika

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 16:39:02

Instead of a subquery, would not another inner join get you what you need?

SELECT DISTINCT K.ItemID, D.ShortDesc
FROM timKitCompList KL
INNER JOIN timItem I ON KL.CompItemKey = I.ItemKey
INNER JOIN timitemdescription D ON I.ItemKey = D.ItemKey
INNER JOIN timKit K ON K.KitItemKey = I.ItemKey
AND K.KitItemKey = D.ItemKey

mrgalvan
Starting Member

9 Posts

Posted - 2006-07-20 : 16:51:27

quote:
Originally posted by Srinika

U have 4 tables, but u mention 2 tables ?
Why do u use "Exists"
U cannot use Aliases of the Query for Exist check, in the main Query

Without knowing ur Joining / Conditions ... hard to give a solution. but seems to be not complex.

Srinika

I guess I'm trying to get the timKit and the timKitCompList to both give me their ItemIDs as well as their descriptions. I can run the two seperate queries alone and get what I'm looking for, but when I try to do them together it doesn't like it. What am I doing wrong. By the way, I'm very new to SQL.
Thanks,

USE Mas500_App
SELECT timItem.ItemID, timItemDescription.ShortDesc, timKit.KitItemKey
FROM timKitCompList
INNER JOIN timItem ON timKitComplist.CompItemKey = timItem.ItemKey
INNER JOIN timitemdescription ON timItem.ItemKey = timItemDescription.ItemKey
INNER JOIN timKit ON timKitCompList.KitItemKey = timKit.KitItemKey
WHERE EXISTS
(SELECT timItem.ItemID, timItemDescription.ShortDesc
FROM timKit
INNER JOIN timItem ON timKit.KitItemKey = timItem.ItemKey
INNER JOIN timitemdescription ON timItem.ItemKey = timItemDescription.ItemKey)
GROUP BY timItem.ItemID, timItemDescription.ShortDesc, timkit.KitItemKey

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 17:08:07

SELECT DISTINCT I.ItemID, D.ShortDesc, K.ItemID
FROM timKitCompList KL
INNER JOIN timItem I ON KL.CompItemKey = I.ItemKey
INNER JOIN timitemdescription D ON I.ItemKey = D.ItemKey
INNER JOIN timKit K ON K.KitItemKey = I.ItemKey
AND K.KitItemKey = D.ItemKey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources