Solved

SQL sub count select depending on variable from parent SQL

Posted on 2009-05-18
17
898 Views
Last Modified: 2012-05-07
I am trying to do a sub select count from a table which has jobs in it with assigned sectors. IE, in tbl_recruitment there is a column called job_sectorID which has sector IDs in it as so: ¬id1¬id2¬id3 etc. I need to do a count depending on the tbl_recruitment_sectors.job_sectorID which is an integer field. I am trying to return a list as so:

Job sector 1 (count of jobs in sector)
Job sector 2 (count of jobs in sectore) etc.

The problem is the sub count sql needs to be a like statment as more than on ID is stored in the tbl_recruitment.job_sectorID field. Also, the tbl_recruitment_sectors.job_secorID is an integer field whereas tbl_recruitment.job_sectorID is a varchar field.

Can any one help on this?
SELECT     TOP 100 PERCENT CAST(job_sectorID AS varchar) AS @Expr2, job_sectorName,
                          (SELECT     COUNT(*)
                            FROM          tbl_recruitment
                            WHERE      dbo.tbl_recruitment.job_sectorID LIKE '% ' & Expr2 & ' %') AS Expr1
FROM         dbo.tbl_recruitment_sectors
ORDER BY job_sectorName

Open in new window

0
Comment
Question by:iziizi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 4

Expert Comment

by:bljak
ID: 24410138
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".
0
 

Author Comment

by:iziizi
ID: 24410164
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
0
 
LVL 4

Expert Comment

by:bljak
ID: 24410207
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

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:iziizi
ID: 24410270
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&"¬%' "

Open in new window

0
 
LVL 4

Expert Comment

by:bljak
ID: 24410383
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.
0
 

Author Comment

by:iziizi
ID: 24410452
"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.
0
 
LVL 4

Expert Comment

by:bljak
ID: 24410538
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

Open in new window

0
 
LVL 4

Expert Comment

by:bljak
ID: 24410551
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

Open in new window

0
 

Author Comment

by:iziizi
ID: 24410712
where is @Expr2 coming from?

0
 
LVL 4

Expert Comment

by:bljak
ID: 24410789
I don't know, you had it on your first code snippet.
But basically it's a sector number as variable.
0
 

Author Comment

by:iziizi
ID: 24410832
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

Open in new window

0
 

Author Comment

by:iziizi
ID: 24410848
does b.job_sectorID AS nvarchar need to be used differently?

like with &b.job_sectorID& for example?
0
 

Author Comment

by:iziizi
ID: 24410885
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....
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24411496
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.
0
 
LVL 4

Expert Comment

by:bljak
ID: 24412520
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

Open in new window

0
 
LVL 4

Accepted Solution

by:
bljak earned 500 total points
ID: 24412528
Argh, copy/paste error again:
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(b.job_sectorID as nvarchar) + ' %'
                group by b.job_sectorID, b.job_sectorName
                order by jobCount

Open in new window

0
 

Author Comment

by:iziizi
ID: 24412606
bljak - thank you, thank you repeat....
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Time out only from one specific computer client 15 53
Server 2012 r2 and SQL 2014 6 34
SQL: Transformation or Pivot 3 36
Datatable / Dates ? 4 32
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question