Help With T-SQL 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 2005 Forums

Transact-SQL (2005)

Help With T-SQL Query

Author

Topic

coolvijaymca
Starting Member

1 Post

Posted - 2009-11-04 : 01:57:33

Dear All SQL Guru,

Currently I am stuck with a t-SQL query for retriving a user defined out put.

Sample query for data given below
In Sample data client is identify by with the combination of below mention column:
Main_Product
Sub_Product
Product_Code
Relation

DECLARE @t TABLE (
EXCEPTION_ID INT IDENTITY,
Exception_Type char (15),
Main_Product varchar(60),
Sub_Product varchar(60),
Product_Code varchar (40),
Relation varchar (40)
)
INSERT INTO @t(Exception_Type, Main_Product, Sub_Product,Product_Code,Relation)
SELECT 'INVALID', 'DP', 'DP', '10092535', 'Client'
UNION
SELECT 'INVALID', 'MF', 'MF', 'KSEC2', 'FIRST USER'
UNION
SELECT 'INVALID','PMS', 'PMS Wealth', 'PMSC2', 'Client'
--SELECT * FROM @t

DECLARE @clnt TABLE (
CRN_ID INT IDENTITY,
Main_Product varchar(60),
Sub_Product varchar(60),
Product_Code varchar (40),
Relation varchar (40)
)
INSERT INTO @clnt(Main_Product, Sub_Product,Product_Code,Relation)
SELECT 'DP', 'DP', '10092535', 'Second Holder'
UNION
SELECT 'MF', 'MF', 'KSEC2', 'FIRST USER'
UNION
SELECT 'DP', 'DP', '11092535', 'Client'
UNION
SELECT 'DP', 'DP', '11092535', 'Second Holder'
UNION
SELECT 'PMS', 'PMS Wealth', 'PMSC2', 'Client'
--SELECT * FROM @clnt ORDER BY MAIN_Product
Hear I have two table 1 is @t (Invalid data table) and second @clnt (Client Table)

I have client data with the relation as Client and their belonging data with the relation as Second holder. All other field should be remain same (i.e Main Product, Sub Product and Product Code) for both relation record.

From above query I want output like this

main_product sub_product product_code relation
DP DP 11092535 Client
DP DP 11092535 Second Holder
MF MF KSEC2 FIRST USER
PMS PMS Wealth PMSC2 Client

If a client having relation Client and exist in both table then record should be come from clnt table.

If a client having relation Client and exist in @t table and Second Holder record exist in @clnt table then none of the record come.

If a client having relation Client and Second Holder record exist in @clnt table then both of the record should be come.

Kindly provide me way to achive this task.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources