Select 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)

Select Query

Author

Topic

DeepakNewton
Starting Member

39 Posts

Posted - 2008-02-21 : 02:16:13

Hello ALL

Doubt in retreiving the Resultset

I have two Tables

ItemLookup

ItemID ItemDescription Status
1 Fin A
2 Acc A
3 Hris A

Items

ItemdetailId AccountId ItemDescription
100 44 Fin
200 44 Acc
300 44 Hris

I need to check for the whether item exists for the Account in the Items , if exists i need to display from the items or else i need to display from the Look up table

The Issue here is i can able to add user defined items "MNC" and also i can add duplicate item from the lookup table like "Fin","Acc" etc in the Items table.

Now i need to display from the items table as i have added any duplicate entry like "Fin" It should be displayed next to the Original "Fin" and if i delete the added item from the items table and again add the "Fin" it should be displayed as inserted row position

Any Solutions????

I need to retreive the Items

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-21 : 23:23:47

Didnt understand your requirement fully.i think first part can be obtained as this

SELECT ISNULL(t1.ItemDescription,t2.ItemDescription),
FROM
(
SELECT ItemdetailId,AccountId,ItemDescription
FROM Items
WHERE AccountId=@AccountId
AND ItemDescription=@Item
)t1
RIGHT OUTER JOIN 
(
SELECT ItemID,ItemDescription,Status
FROM Items
WHERE ItemDescription=@Item
)t2
ON t2.ItemDescription=t1.ItemDescription

didnt understand your second part.Where will you be adding user defined items?Can you explain this a bit more?

DeepakNewton
Starting Member

39 Posts

Posted - 2008-02-22 : 01:57:10

I need to load the all the Items from the Lookup table if items table doesnot have data, moreover i can able to add itemsdescription in the items table , a)Itemsdesctiption can be the item Present in the Itemlookup or "User defined items" If itemdescripton added in the Item table is same as Look up table item means its dupilcated so it should be displayed next to original item for example "Fin" is the Item allready there in the Items table now again i plan to add "Fin" then the added item should display next to the "Fin" which is already present in the Items Table for the particular account. b) and similarly if i delete the Item "Fin" in the Items table now the orginal Item "fin" from looup will be deleted from the items table and again if i add item "fin" thru the Front end Now it should display the order which has been added.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources