Solved

SQL sub count select depending on variable from parent SQL

Posted on 2009-05-18
17
880 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

776 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