Solved

SQL sub count select depending on variable from parent SQL

Posted on 2009-05-18
17
860 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
 

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now