In Master and detail table fetch record.

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)

In Master and detail table fetch record.

Author

Topic

Asit
Starting Member

6 Posts

Posted - 2008-06-24 : 04:33:30

Dear Sir
Thank for your reply but our requirements are not this
I am fully explain my requirements

In Master table have 5 columns; In Master Table stored all records.

Name Appid FunctionCode FunName SubFunCode
Ad 6 30 Manual 0
Ad 6 30 Log 10
Data 8 10 Summary 0
Data 8 20 View 0
Data 8 30 &View 0
Data 8 40 Row 10
Ad 6 30 Mbl 20

In second Table store those records who are selected and stored in 2nd table.
Name Appid FunctionCode FunName SubFunCode
Data 8 10 Summary 0
Data 8 20 View 0
Data 8 30 &View 0
Ad 6 30 Mbl 20

Our requirements we use one query,
In query fetch total 5 rows. and output show like this

Name Appid FunctionCode FunName SubFunCode
Ad 6 30 Manual 0 N
Ad 6 30 Log 10 N
Data 8 10 Summary 0 Y
Data 8 20 View 0 Y
Data 8 30 &View 0 Y
Data 8 40 Row 0 N
Ad 6 30 Mbl 20 Y

Please immediate reply me,
I am waiting your reply.
Thanks Asit Sinha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 04:38:51

Try like this:-

SELECT otherfields,COALESCE(t2.Mark,t1.Mark) AS AssignedFunction
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.field1=t1.field1
.....linking conditions

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 04:42:33

In your case it will be like this:-

SELECT TBLAPPLICATIONS.Name, TBLGROUPFUNCTIONS.AppID, TBLGROUPFUNCTIONS.FunctionCode, TBLALLFUNCTIONS.DefaultSubFunctionName, 
TBLGROUPFUNCTIONS.SubFunctionCode, COALESCE(TBLALLFUNCTIONS.Mark,TBLGROUPFUNCTIONS.Mark) AS AssignedFunction
FROM TBLGROUPFUNCTIONS 
LEFT JOIN TBLALLFUNCTIONS ON TBLGROUPFUNCTIONS.AppID = TBLALLFUNCTIONS.AppID AND 
TBLGROUPFUNCTIONS.FunctionCode = TBLALLFUNCTIONS.FunctionCode AND 
TBLGROUPFUNCTIONS.SubFunctionCode = TBLALLFUNCTIONS.SubFunctionCode I
INNER JOIN
TBLAPPLICATIONS ON TBLGROUPFUNCTIONS.AppID = TBLAPPLICATIONS.AppID
WHERE TBLGROUPFUNCTIONS.GroupID = 12
Order BY aPPID,FunctionCode,sUBfUNCTIONcODE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 06:16:00

[code]SELECT otherfields,
CASE WHEN t2.column IS NULL THEN 'N' ELSE 'Y' END AS AssignedFunction
FROM MasterTable t1
LEFT JOIN OtherTable t2
ON t2.field1=t1.field1
.....linking conditions[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources