CODEWITHSUNDEEP
sqlsql-serverazure
Ari
Ari

Reputation: 6169

How to INNER JOIN conditional data from two tables with SQL

I have two tables;

one contains a column ENTITYID and a column representing whether the jobs are full time or not (1 being full time).

The other table contains matching ID's and a column stating what country those jobs are based in.

My task is to find all the jobs that are full time in table1 and then use that information to find which countries they are in from table2. But for the second part it needs to be a count of the number of jobs for each country. ie. 10 Japan, 5 USA, 2 UK. Not Japan, Japan, Japan etc.

What I have so far:

SELECT entityid FROM dbo.Jobs WHERE contracthours = 1;

This returns all the jobs id's (entityid) that are full-time (= 1). But how do I now take that data and use it to find them in the second table.

And in Table2:

SELECT COUNT(ID), ISNULL(country, 'No Country')
FROM dbo.JobLocations
GROUP BY Country;

This returns the jobs with a count of every job, and also replaces jobs with no associated country with 'No Country' (part of my task requirement).

What I need to do is take what I have from the first query on table1 and somehow INNER JOIN it and return the result similar to the second query used on table2.

edit: I should note ENTITYID from table1 is equal to JOBID in table2

Some visuals, first image shows table1 ID's and Contracthours (1 being fulltime), The second image shows table2 ID's and related country.

Table1

table2

Upvotes: 0

Views: 57

Answers (2)

ivan.rosina
ivan.rosina

Reputation: 408

SELECT COUNT(ID), ISNULL(country, 'No Country')
FROM dbo.JobLocations
where JOBID in (SELECT entityid FROM dbo.Jobs WHERE contracthours = 1)
GROUP BY Country;

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

If I understand correctly you can try to use count with window function get count then do join on ENTITYID and JOBID.

SELECT * 
FROM dbo.Jobs t1 inner join (
    SELECT JOBID,
            COUNT(ID) over(partition by Country order by Country) cnt, 
            ISNULL(country, 'No Country') country
    FROM dbo.JobLocations
) t2 on t2.JOBID = t1.entityid
WHERE t1.contracthours = 1;

Upvotes: 1

Related Questions