Recursive Query Help

I have this query that gives me the postingFacilityID and total openings.

SELECT DISTINCT j.postingFacilityID, SUM(j.true_job_cnt) AS tot_openings, ff.facilityName
FROM jobInfo j, facilities f
WHERE j.display = 'Y'
            AND j.dateClose >= GetDate()
            AND j.facilityID = f.facilityID
            and f.statusid = 1
            and f.deleted = 0
GROUP BY j.postingFacilityID
ORDER BY SUM(j.true_job_cnt) DESC

What I want is to be able to use the resultant postingFacilityID and get the facility name which would be in the facilities table as "facilityName".
LVL 1
coldchillinAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
Try:


SELECT j.postingFacilityID, f.facilityName, SUM(j.true_job_cnt) AS tot_openings
FROM jobInfo j
inner join facilities f
on j.facilityID = f.facilityID
WHERE j.display = 'Y'
  AND j.dateClose >= GetDate()
  and f.statusid = 1
  and f.deleted = 0
GROUP BY j.postingFacilityID , f.facilityName
ORDER BY SUM(j.true_job_cnt) DESC

Open in new window

0
 
Daniel WilsonCommented:
You've got a FacilityName in your list of fields ... is it returning the wrong thing?
0
 
coldchillinAuthor Commented:
I tried that as well, but with no luck. The reason why I can't just include it is because I need the DISTINCT item.

The records are grouped by "corporation", where all members belong to the corporate record will be aggregated and displayed under the "corporate" name.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Daniel WilsonCommented:
Can you post a few rows of sample data?  My code would have looked a lot like Brandon's from what I understand ... so I'm missing something.
0
 
coldchillinAuthor Commented:
Ok, initial query I posted returns this:
postingFacilityID        tot_openings
11                  559
158                  48
211                   30

The suggested query returns this:
postingFacilityID
11              name1      117
11              name2      102
11              name3      69
11              name4      68
11              name5      50

The problem with the second query is that all those records with the postingFacilityID of 11 should be aggregated. The actual entity with the ID 11 will have the name "Name0"
0
 
BrandonGalderisiCommented:
Can you post the definitions of jobInfo and facilities?
0
 
Daniel WilsonCommented:
Is there more than one FacilityName for a FacilityID?
0
 
coldchillinAuthor Commented:
The facility table has the following relevant fields:
facilityID, corporateID, facilityName

The jobInfo table has the following relevant field:
postingFacilityID

The posted query works fine, but what I then need to do is go back and using the resultant postingFacilityID, match it back to the facilities table to the facilityID, and get the facilityName
0
 
Daniel WilsonCommented:
The posted query works fine, but what I then need to do is go back and using the resultant postingFacilityID, match it back to the facilities table to the facilityID, and get the facilityName
Understood.  It's that matching up that is missing some criterion.


The suggested query returns this:
postingFacilityID
11              name1      117
11              name2      102
11              name3      69
11              name4      68
11              name5      50

The problem with the second query is that all those records with the postingFacilityID of 11 should be aggregated. The actual entity with the ID 11 will have the name "Name0"
Why?  Where does "Name0" come from?

Are you using Brandon's query exactly as he posted it?  Or are you modifying it?  If modifying, please post the modification.  

Perhaps you've simplified the problem for us ... a little too much.
0
 
coldchillinAuthor Commented:
Ahhh...I see what happened!

Brandon, the query you posted uses j.facilityid = f.facilityid, but what it needed to use was j.postingFacilityID = f.facilityID
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.