SQL sub count select depending on variable from parent SQL

This is fairly easy if you would provide whole table structure of both tables and their relations.
And the way sectors are saved in tbl_recruitment. Is it 1,2,3 and it's just a varchar, or is it really saved as: "ID1", "ID2".

tbl_recruitment:

3      job_ID      int      4      0
0      job_categoryID      nvarchar      50      1
0      job_sectorID      nvarchar      50      1
0      job_title      varchar      255      1
0      job_abstract      text      16      1
(snip)
tbl_recruitment_sector:

3      id      int      4      0
0      job_sectorID      int      4      1
0      job_sectorName      varchar      50      1

Sample data from tbl_recruitment
2248      ¬11¬,¬16¬,¬1¬      ¬1¬,¬0¬      job title  job abstract

As you can see the IDs from tbl_recruitment_sector are saved as ¬id¬, ¬id1¬ etc in tbl_recruitment

Ok, i'll work with what you gave me and what i understood from it.

select a.job_sectorID, a.job_sectorName, count(b.job_sectorID) as jobCount
	from tbl_recruitment a, tbl_recruitment_sector b
	where a.job_sectorID like '% ' + @Expr2 + ' %'
		and cast(a.job_sectorID as int) = b.job_sectorID
		group by a.job_sectorID, a.job_sectorName

Select allOpen in new window

thanks for the reply.

Not sure that can work because multiple IDS are stored in tbl_recruitment.job_sectorID

They are stored like this: ¬11¬,¬16¬,¬1¬

Where each number is an ID of a job sector from tbl_recrtuiment_sectors.

I have attached another code snippet to show how I have it working at the moment in VB but it uses two seperate SQL statements.

Does that make sense?

	SQLjs = "SELECT job_sectorID, job_sectorName FROM tbl_recruitment_sectors WHERE job_sectorID <> 0 ORDER BY job_sectorName"
	Set RSjs = adoCon.Execute(SQLjs)
	While Not RSjs.EOF
		job_sectorID = RSjs("job_sectorID")
		job_sectorName = RSjs("job_sectorName")
		job_sectorNeat = NeatUrl(RSjs("job_sectorName"))
 
		SQLjsA = "SELECT count(*) FROM tbl_recruitment WHERE job_sectorID LIKE '%¬"&job_sectorID&"¬%' "

Select allOpen in new window

Now you made me curious.
So basically, in tbl_recruitment_sectors, column job_sectorID is not unique, and can be present in more than one row.
And also in tbl_recruitment, the same value can be present in more than one row?
And only thing you want to know, is count, how many rows (in sum of sectorID from tbl_recruitment_sectors) are present.

"So basically, in tbl_recruitment_sectors, column job_sectorID is not unique, and can be present in more than one row."

Yes, they are a combnation of any job sector IDs from tbl_recruitment_sectors. Here are some example row data:

¬11¬,¬16¬,¬1¬
¬30¬,¬1¬
¬1¬,¬2¬,¬0¬,¬26¬

basically assigning more than one category (sector) to a job.

"And also in tbl_recruitment, the same value can be present in more than one row?"

yes, jobs can be assigned the same sectors.

"And only thing you want to know, is count, how many rows (in sum of sectorID from tbl_recruitment_sectors) are present."

Yes, i need to know how many jobs are assigned a to a specific job sector. IE i want to produce a table like this:

"      Account Manager (1)
"      Account Executive (2)
"      Accountant (2)

Where the account manager is a sector type and the number in brackets in the number of jobs assigned to that job sector from the tbl_recuitment table


Cheers.

If the job_sectorName is something "unique" you can use following (assuming @expr2 is int):

select b.job_sectorID, b.job_sectorName, count(b.job_sectorID) as jobCount
        from tbl_recruitment a, tbl_recruitment_sector b
        where a.job_sectorID like '% ' + cast(@Expr2 as nvarchar) + ' %'
                and b.job_sectorID = @Expr2
                group by b.job_sectorID, b.job_sectorName

Select allOpen in new window

Oops sorry, mistyped something:

select b.job_sectorID, b.job_sectorName, count(a.job_sectorID) as jobCount
        from tbl_recruitment a, tbl_recruitment_sector b
        where a.job_sectorID like '% ' + cast(@Expr2 as nvarchar) + ' %'
                and b.job_sectorID = @Expr2
                group by b.job_sectorID, b.job_sectorName

Select allOpen in new window

where is @Expr2 coming from?

I don't know, you had it on your first code snippet.
But basically it's a sector number as variable.

ah yeah.... thats the bit which is causing me a problem. That variable i was trying to do this:

CAST(tbl_recruimtnet_sector.job_sectorID AS varchar) AS @Expr2

then I need to use that variable later in the like statment. This is why i am not sure if its possible.


I think i have throughly confused you, and myself! :)

The statment you last wrote is nearly correct i think. The only thing is @Expr2 needs to be the b.job_sectorID (please see snuppet)

I run this query and no results are returned tho

SELECT     b.job_sectorID, b.job_sectorName, COUNT(a.job_sectorID) AS jobCount
FROM         tbl_AM_recruitment AS a CROSS JOIN
                      tbl_AM_recruitment_sectors AS b
WHERE     (a.job_sectorID LIKE '% ' + CAST(b.job_sectorID AS nvarchar) + ' %')
GROUP BY b.job_sectorID, b.job_sectorName

Select allOpen in new window

does b.job_sectorID AS nvarchar need to be used differently?

like with &b.job_sectorID& for example?

Here is some sample data from tbl_recruitment_sectors:

      1      Accounts
      2      Accounts / Finance
      3      Administration

Where 1,2,3 are IDS of the sector types accounts, accounts/finance and administration

If a job was assigned to these sectors, the IDs would appear like this: ¬1¬,¬2¬,¬3¬ in the tbl_recruitment.job_sectorID table....

Totally unrelated, but contrary to what you may believe TOP 100 PERCENT ... ORDER BY does not sort your resultset in SQL Server 2005.  Even if you are still using 2000 it is probably a good idea to keep that in mind.

Try this:

select top 100 percent b.job_sectorID, b.job_sectorName, count(a.job_sectorID) as jobCount
        from tbl_recruitment a, tbl_recruitment_sector b
        where a.job_sectorID like '% ' + cast(@Expr2 as nvarchar) + ' %'
                group by b.job_sectorID, b.job_sectorName
                order by jobCount

Select allOpen in new window

bljak - thank you, thank you repeat....