asked on

Help With Database JOIN

Hello Experts,

I have a table of Organisations, each organisation is associated with a primary category via a field 'teOrgCategory'

SELECT [teOrgTitle]
,[teOrgGUID]
,[teOrgCategory]
FROM [my_database].[dbo].[tblCsdOrg]
WHERE teOrgCategory = 'CAT-CSD-DBH-30-14-85'

So I can also associate other Organisations with additional categories I have created another table that allows additional associations between organisations and categories

SELECT [teOrgAsOrgGUID]
,[teOrgAsOrgCatID]
FROM [my_database].[dbo].[tblCsdOrgToCats]

To clarify;

tblCsdOrg.teOrgGUID = tblCsdOrgToCats.teOrgAsOrgGUID

I'd like guidance on the most efficient query to list all Organisations in the Category 'CAT-CSD-DBH-30-14-85', Ordered by teOrgTitle

Thanks for your help.

Sample MSSQL Data Attached
ee_csd_sample.sql

Mike Eghtebas

Hi sonic1234,

Select * from tblCsdOrgToCats
teOrgAsID        teOrgAsOrgGUID       teOrgAsOrgCatID
10	             CSD-PPA-64-48-20	CAT-CSD-IWI-11-05-76
11	             CSD-PPA-64-48-20	CAT-CSD-ICR-42-35-17
13	             CSD-JMV-22-16-87	CAT-CSD-AKA-18-03-74
14	             CSD-STO-55-40-11	CAT-CSD-ROV-55-48-11

select * from [tblCsdOrg];
teOrgTitle                                    teOrgGUID               teOrgCategory
Yamba Touch Football Association	        CSD-XXR-27-02-73	CAT-CSD-EEQ-50-34-15
Yamba Yabbies Toastmasters Club	        CSD-YCC-27-02-73	CAT-CSD-ZNV-17-02-73
Yamba Surf Life Saving Club	        CSD-VEO-16-01-62	CAT-CSD-TBO-45-30-11
Yamba Community Hall	                CSD-KLV-32-26-07	CAT-CSD-RFG-44-38-11
Yamba Library	                        CSD-OPH-24-17-70	CAT-CSD-VBD-56-41-12


Select * from [tblCsdCategory]
0	0	CAT-CSD-11-11-11	CAT-CSD-11-11-11	Halls, Centres &amp; Facilities
2	3	CAT-CSD-DYN-10-84-65	CAT-CSD-HGQ-21-15-86	Arts Groups
3	0	CAT-CSD-HGQ-21-15-86	CAT-CSD-11-11-11	Arts & Culture
4	0	CAT-CSD-RUP-35-28-01	CAT-CSD-11-11-11	Community Health
6	0	CAT-CSD-LDP-82-76-57	CAT-CSD-11-11-11	Education, Training & Learning
7	0	CAT-CSD-LHR-33-16-88	CAT-CSD-11-11-11	Crisis & Emergency Support
10	0	CAT-CSD-HKF-01-85-66	CAT-CSD-11-11-11	Government
11	0	CAT-CSD-DLE-50-44-15	CAT-CSD-11-11-11	Seniors

Open in new window

Above is some sample data from each of your tables. So far, I have not been able sense of what exactly you are asking for. Most likely, you have done a very good job describing what you want but I am not able to understand it. Could you please, based on the data above, show what is the output your are looking for. If it helps, use your own sample data for each of these three tables.

Thanks,

Mike

sonic1234

ASKER

Let's say I want to show all organisations associated with the category CAT-CSD-DBH-30-14-85

So I can find any this way.

SELECT [teOrgTitle]
,[teOrgGUID]
,[teOrgCategory]
FROM [test].[dbo].[tblCsdOrg]
WHERE teOrgCategory = 'CAT-CSD-DBH-30-14-85'

This finds the Organisation CSD-TCQ-35-20-81

However, I also need to add in the additional Organisations linked via tblCsdOrgToCats

SELECT [teOrgAsID]
,[teOrgAsOrgGUID]
,[teOrgAsOrgCatID]
FROM [test].[dbo].[tblCsdOrgToCats]
WHERE teOrgAsOrgCatID = 'CAT-CSD-DBH-30-14-85'

This gives me these additional Organisations

CSD-AVI-78-53-34
CSD-ASN-48-22-04
CSD-HSS-11-05-76
CSD-NTO-23-17-88

So basically I want a single query where I input CAT-CSD-DBH-30-14-85 and I get the equivalent output to running

SELECT [teOrgTitle]
,[teOrgGUID]
,[teOrgCategory]
FROM [test].[dbo].[tblCsdOrg]
WHERE teOrgGUID IN ('CSD-TCQ-35-20-81','CSD-AVI-78-53-34','CSD-ASN-48-22-04','CSD-HSS-11-05-76','CSD-NTO-23-17-88')
ORDER BY teOrgTitle ASC

Arana (G.P.)

not sure if this is what you want:

SELECT  [teOrgTitle]
      ,[teOrgGUID]
      ,[teOrgCategory]
      ,[teOrgAsOrgCatID]
  FROM [test].[dbo].[tblCsdOrg]
JOIN [test].[dbo].[tblCsdOrgToCats] on [test].[dbo].[tblCsdOrg].teOrgCategory=[test].[dbo].[tblCsdOrgToCats].teOrgAsOrgCatID
  WHERE teOrgCategory = 'CAT-CSD-DBH-30-14-85'
ORDER BY teOrgTitle ASC

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Hi Sonic1234,
Any luck the solution i gave or do you need any more changes in that ?

sonic1234

ASKER

Thanks that worked perfectly.

Pawan Kumar

Welcome, Glad to help!