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)
 Join Help?

Author  Topic 

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-13 : 11:53:30
I need to put together a query that uses three different tables.

1. tblBMS_BudgetCategories, has budget catgorys, (PK = CategoryID)
2. tblBMS_BudgetCategories_Subcategories, has budget subcatgorys (PK = SubCategoryID, FK = CategoryID)
3. tblBMS_Data_rel_BudgetCategories, has budget data, (PK = KeyID, FK = CategoryID, FK = SubCategoryID).

My end result will have the max number of categorys and subcategorys, 36 rows of data representing all the categorys and subcategorys along with the data for each subcategory. (There is only one subcategory entry of data for each record.)

This attempt gives me the exact query I need but DOESN'T include the 3rd table with the data..tblBMS_Data_rel_BudgetCategories

SELECT c.categoryid, c.ShortDescription, sc.ShortDescription AS SubCategoryDescription, c.Active, c.DisplayOrder
FROM tblBMS_BudgetCategories c
INNER JOIN tblBMS_BudgetCategories_Subcategories sc ON sc.CategoryID = c.CategoryID
WHERE c.Active=1
ORDER BY c.DisplayOrder

Here I try to add the data table to the query..

SELECT c.categoryid, c.ShortDescription, sc.ShortDescription AS SubCategoryDescription, sc.SubCategoryID,
d.profileid, d.value1, d.value2
FROM tblBMS_BudgetCategories c, tblBMS_Data_rel_BudgetCategories d, tblBMS_BudgetCategories_Subcategories sc
WHERE c.CategoryID = d.CategoryID
AND c.CategoryID = sc.CategoryID
AND c.Active=1
AND d.profileid = 34129
ORDER BY c.DisplayOrder, sc.ShortDescription

This repeats some fo the subcategorys and values associated with them, even though there is only one entry for the corrseponding subcategory in the tblBMS_Data_rel_BudgetCategories table.

Can I do this with a JOIN? Can this be done and how?

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-13 : 15:12:17
This seems to work.. but I would like to join the category table somehow to pull in the category name.

SELECT d.KeyID, sc.categoryid, sc.ShortDescription, sc.SubCategoryID,
d.profileid, d.value1, d.value2, sc.Value1_Exists, sc.Value1_UserEntered, sc.Value2_Exists,
sc.Value2_UserEntered, d.budgetyear
FROM tblBMS_BudgetCategories_Subcategories sc
LEFT OUTER JOIN tblBMS_Data_rel_BudgetCategories d on d.SubCategoryID = sc.SubCategoryID
AND sc.Active=1
AND d.profileid = 34129
ORDER BY sc.CategoryID, sc.DisplayOrder
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-14 : 13:29:28
No one has any ideas?
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-15 : 09:18:40
No one can assist me on how to add one more join/table table (tblBMS_BudgetCategories)to the query below to pull in the category name? Or is it so obvious that no one will answer me..

SELECT c.categoryid, c.ShortDescription, sc.ShortDescription AS SubCategoryDescription, sc.SubCategoryID,
d.profileid, d.value1, d.value2
FROM tblBMS_BudgetCategories c, tblBMS_Data_rel_BudgetCategories d, tblBMS_BudgetCategories_Subcategories sc
WHERE c.CategoryID = d.CategoryID
AND c.CategoryID = sc.CategoryID
AND c.Active=1
AND d.profileid = 34129
ORDER BY c.DisplayOrder, sc.ShortDescription
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-18 : 11:50:57
No one can help?
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-21 : 10:16:05
I guess I'll have to look elsewhere for my SQL answers..
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-06-21 : 10:25:47
Wow ! a one man thread !

Maybe if you can post some sample data from these tables and the expected result.

KH
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-22 : 12:23:03
Here is the sample data.

Table: tblBMS_BudgetCategories

CategoryID Short Description    Active DisplayOrder
1	   Clinical Protocols	1	40
2	   Contracts	        1	50
3	   CSE 	                1	60
4	   Animal Costs	        1	10
5	   Travel & Training	1	20
6	   Supplies & Services	1	30
8	   Personnel Ceilings	1	70
9	   CANS	                1	5
10	   Center Numbers	1	6
11	   Budget Adjustments	1	9

tblBMS_BudgetCategories_Subcategories

SubCategoryID CategoryID ShortDescription         Active DisplayOrder
1	       4	Purchases	          1	    2
2	       4	Technical Services        1	    3
3	       4	Holding	1	          1         4
4	       4	Charles River	          1	    1
5	       4	Contracts	          1	    5
6	       5	Travel (Budget Total)	  1	    1
7	       5	Training (Budget Total)	  1	    2
8	       5	Patient Travel	          1	    3
9	       6	NULL	                  1	    3
10	       6	NULL	                  1	    4
11	       1	NULL	                  1	    1
12	       1	NULL	                  1	    2
13	       1	NULL	                  1	    3
14	       2	NULL	                  1	    3
15	       2	NULL	                  1	    4
16	       2	NULL	                  1	    5
17	       3	NULL	                  1         1
18	       3	NULL	                  1         2
19	       3	NULL	                  1	    3
20	       3	Discretionary Funding	  1	    4
21	       6	S&S (Budget Total)	  1	    1
23	       8	FTE	                  1	    1
24	       8	Non-FTE	                  1	    2
25	       8	SAIC, Orkand	          1         3
26	       8	Students	          1	    4
27	       8	SV/GR	                  1	    5
28	       2	Personnel Services	  1	    2
29	       9	NULL	                  1	    1
30	       9	NULL	                  1	    2
31	       9	NULL	                  1	    3
32	       10	NULL	                  1	    1
33	       10	NULL	                  1	    2
34	       10	NULL	                  1	    3
35	       6	Pathology/Histology	  1	    2
36	       11       Fellowship Adjustments	  1	    1
37	       2	Maintenance	          1         1


Table: tblBMS_Data_rel_BudgetCategories

KeyID   ProfileID CategoryID SubCategoryID Description             BudgetYear Value1  Value1
60453	33071	  11	     36	           Fellowship Adjustment   2007	       0      0
60454	33071	  4	     4	           Charles River	   2007	       0      0
60455	33071	  4	     1	           Purchases	           2007	       0      0
60456	33071	  4	     2	           Technical Services	   2007	       0      0
60457	33071	  4	     3	           Holding	           2007	       0      0
60458	33071	  4	     5	           Contracts	           2007	       0      0
60459	33071	  5	     6	           Travel (Budget Total)   2007	       25015  0
60460	33071	  5	     7	           Training (Budget Total) 2007	       17915  0
60461	33071	  5	     8	           Patient Travel	   2007	       0      0
60462	33071	  6	     21	           S&S (Budget Total)	   2007	       165515 0
60463	33071	  6	     35	           Pathology/Histology	   2007	       0      0
60464	33071	  1	     11	           Clincal Protocols	   2007	       0      0
60465	33071	  2	     28	           Personnel Services	   2007	       0      0
60466	33071	  2	     37	           Maintenance	           2007	       0      0
60467	33071	  2	     15	           New COntracts	   2007	       120000 10000
60468	33071	  3	     20	           Discretionary Funding   2007	       0      0
60469	33071	  8	     23	           FTE	                   2007	       0      0
60470	33071	  8	     24	           Non-FTE	           2007	       0      0
60471	33071	  8	     25	           SAIC, Orkand	           2007	       0      0
60472	33071	  8	     26	           Students	           2007	       0      0
60473	33071	  8	     27	           SV/GR	           2007	       0      0
75472	33071	  9	     29	           898765635	           2007	       62000  0
75473	33071	  9	     30	           343535346	           2007	       25000  0
75474	33071	  9	     31	           354324234	           2007	       1500   0
75475	33071	  10	     32		                           2007	       0      0
75476	33071	  10	     33		                           2007	       0      0
75477	33071	  10	     34		                           2007	       0      0



Here are the desired results...


KeyID   Category            CategoryID ShortDescription       SubCategoryID  ProfileID Value1  Value2
60464	Clinical Protocols  1	       NULL	              11	     33071     0       0
NULL	                    1	       NULL	              12	     NULL	NULL   NULL
NULL	                    1	       NULL	              13	     NULL	NULL   NULL
60466	Contracts           2	       Maintenance	      37	     33071	0      0
60465	                    2          Personnel Services     28	     33071	0      0
NULL	                    2	       NULL	              14	     NULL	NULL   NULL
60467	                    2	       NULL	              15	     33071	120000 10000
NULL	                    2	       NULL	              16	     NULL	NULL	NULL
NULL	CSE                 3	       NULL	              17	     NULL	NULL	NULL
NULL	                    3	       NULL	              18	     NULL	NULL	NULL
NULL	                    3	       NULL	              19	     NULL	NULL	NULL
60468	                    3	       Discretionary Funding  20	     33071	0       0
60454	Animal Costs        4	       Charles River	      4	             33071	0       0
60455	                    4	       Purchases	      1	             33071	0       0
60456	                    4	       Technical Services     2	             33071	0       0
60457	                    4	       Holding	              3	             33071	0       0
60458	                    4	       Contracts	      5	             33071	0       0
60459	Travel & Training   5	       Travel (Budget Total)  6	             33071	25015   0
60460	                    5	       Training (Budget Total)7	             33071	17915   0
60461	                    5	       Patient Travel	      8	             33071	0       0
60462	Supplies & Services 6	       S&S (Budget Total)     21	     33071	165515  0
60463	                    6	       Pathology/Histology    35	     33071	0       0
NULL	                    6	       NULL	              9	             NULL	NULL	NULL
NULL	                    6	       NULL	              10	     NULL	NULL	NULL
60469	Personnel Ceilings  8	       FTE	              23	     33071	0       0
60470	                    8	       Non-FTE	              24	     33071	0	0
60471	                    8	       SAIC, Orkand	      25	     33071	0       0
60472	                    8	       Students	              26	     33071	0	0
60473	                    8	       SV/GR	              27	     33071	0	0
75472	CANS                9	       NULL	              29	     33071	62000	0
75473	                    9	       NULL	              30	     33071	25000	0
75474	                    9	       NULL	              31	     33071	1500	0
75475	Center Numbers      10	       NULL	              32	     33071	0       0
75476	                    10	       NULL	              33	     33071	0       0
75477	                    10	       NULL	              34	     33071	0       0
60453	Budget Adjustments  11	       Fellowship Adjustments 36	     33071	0	0


Basically the results should display all the Categorys and subcategorys even if an entry doesn't exist in the tblBMS_Data_rel_BudgetCategories table.

Also as it shows in the grid I would like to only display the category for the first record of the results.

I want to display all the options as a grid to display to the user and bind to a grid in asp.net.

Thanks
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-25 : 14:11:58
The tables info didn't help?
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

384 Posts
Posted - 2007-06-26 : 01:59:43
[code]SELECT d.KeyID
,Category = c.ShortDescription
,c.CategoryID
,sc.ShortDescription
,sc.SubCategoryID
,d.ProfileID
,d.Value1
,d.Value2
FROM tblBMS_BudgetCategories c
INNER JOIN tblBMS_BudgetCategories_Subcategories sc
ON sc.CategoryID = c.CategoryID AND sc.Active = 1
LEFT OUTER JOIN tblBMS_Data_rel_BudgetCategories d
ON d.CategoryID = c.CategoryID AND d.SubCategoryID = sc.SubCategoryID
WHERE c.Active = 1
ORDER BY c.CategoryID, sc.DisplayOrder[/code]

quote:
Also as it shows in the grid I would like to only display the category for the first record of the results.

That you handle in the application, it will kill the poor server to do it there.




-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-26 : 09:47:02
quote:
Originally posted by PSamsig

SELECT d.KeyID
      ,Category = c.ShortDescription
      ,c.CategoryID
      ,sc.ShortDescription
      ,sc.SubCategoryID
      ,d.ProfileID
      ,d.Value1
      ,d.Value2
FROM tblBMS_BudgetCategories c
INNER JOIN tblBMS_BudgetCategories_Subcategories sc
ON sc.CategoryID = c.CategoryID AND sc.Active = 1
LEFT OUTER JOIN tblBMS_Data_rel_BudgetCategories d
ON d.CategoryID = c.CategoryID AND d.SubCategoryID = sc.SubCategoryID
WHERE c.Active = 1
ORDER BY c.CategoryID, sc.DisplayOrder


quote:
Also as it shows in the grid I would like to only display the category for the first record of the results.

That you handle in the application, it will kill the poor server to do it there.


-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.


Thanks for the effort, but when I run the query it only returns subcategories where the specific record has data. In the grid I need to display ALL 36 subcategorys as an option for the user to change/enter data. Your query returned 25 of the 36 which are sub categorys the record has data for.

I tried changing the OUTER Join to RIGHT but got the same results.
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

384 Posts
Posted - 2007-06-26 : 09:55:21
It has to be a LEFT join, did you by any chance add 'd.profileid = 34129' to the WHERE?

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-26 : 14:37:46
Yes I did. The results have to be for one individual.
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

384 Posts
Posted - 2007-06-26 : 15:49:49
You misplaced it then, you have to add in this line, as 'ON d.CategoryID = c.CategoryID AND d.SubCategoryID = sc.SubCategoryID AND d.profileid = 34129'.

If you put it in the where clause, then you limit the reslut set to only include row where d.profileid has the given value, it wont be the case where there is no coresponding entry from tblBMS_Data_rel_BudgetCategories, in those cases d.profileid
will be NULL (along with all other coloumns from that table).

If you insist on putting it in the WHERE clause, then you can write it as: 'WHERE c.Active = 1
AND (d.profileid IS NULL OR d.profileid = 34129).

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page

JimAmigo
Posting Yak Master

119 Posts
Posted - 2007-06-28 : 10:19:43
That seems to have done it.. Pure Genius.. thanks for your help!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site