Styjun

I'm having difficulty getting my players to do stuff in a sandbox campaign

How do I keep my slimes from escaping their pens?

If A makes B more likely then B makes A more likely"

What would be Julian Assange's expected punishment, on the current English criminal law?

What do you call a plan that's an alternative plan in case your initial plan fails?

Stop battery usage [Ubuntu 18]

How is simplicity better than precision and clarity in prose?

How does modal jazz use chord progressions?

Need a suitable toxic chemical for a murder plot in my novel

Is above average number of years spent on PhD considered a red flag in future academia or industry positions?

Notation for two qubit composite product state

Geometric mean and geometric standard deviation

Why use gamma over alpha radiation?

Simulating Exploding Dice

Why don't the Weasley twins use magic outside of school if the Trace can only find the location of spells cast?

Windows 10: How to Lock (not sleep) laptop on lid close?

Blender game recording at the wrong time

How should I respond to a player wanting to catch a sword between their hands?

3 doors, three guards, one stone

What did Darwin mean by 'squib' here?

Problem when applying foreach loop

Strange behaviour of Check

Stars Make Stars

Can smartphones with the same camera sensor have different image quality?

Troubles with Oracle SQL query

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

-2

I have two tables and I want to extract the following requirements :

• Job Title.


• Number of Users.

Tables and sample data:

 **Job_Applied** **Jobs_posted**

Job_id Empl_id User_id | Job_id Empl_id Job_Title
------------------------ --------------------------
 1 2 3 | 1 2 Android
 1 2 4 | 2 2 Web
 1 2 5 | 3 4 Java
 2 2 3 |
 2 2 3 |
 2 2 7 | 
 3 2 5 |

I have tried:

 Select count(User_id) as Users , Job_Title 
 from Jobs_posted 
 inner join Jobs_Applied on Jobs_posted.job_id = Jobs_Applied.job_id.

I get number of users that applied on job. How can I get job_title with zero number of users. I have also tried left join. Kindly help.

mysql sql

share|improve this question

edited Mar 24 at 9:46

marc_s

585k13011261272

asked Mar 22 at 7:40

Jahanzeb AwanJahanzeb Awan

45

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You title has Oracle SQL and you tagged mysql. So, which of the two DBMS are you using, Oracle or MySQL?
  
  – Thorsten Kettner
  Mar 22 at 7:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What are the tables's primary keys? What role does empl_id play? Is this column important for the query or can we safely ignore it?
  
  – Thorsten Kettner
  Mar 22 at 7:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The combination (Job_id, Empl_id, User_id) = (2, 2, 3) occurs twice in Job_Applied. Do you want this user count twice for the job accordingly or just once?
  
  – Thorsten Kettner
  Mar 22 at 7:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I am downvoting the request. As my questions above show, it is incomplete, and the OP hasn't answered any of the questions. This makes the request rather worthless for any other reader than the OP himself.
  
  – Thorsten Kettner
  Mar 22 at 9:01
  

  
  
  
  

add a comment | 

-2

I have two tables and I want to extract the following requirements :

• Job Title.


• Number of Users.

Tables and sample data:

 **Job_Applied** **Jobs_posted**

Job_id Empl_id User_id | Job_id Empl_id Job_Title
------------------------ --------------------------
 1 2 3 | 1 2 Android
 1 2 4 | 2 2 Web
 1 2 5 | 3 4 Java
 2 2 3 |
 2 2 3 |
 2 2 7 | 
 3 2 5 |

I have tried:

 Select count(User_id) as Users , Job_Title 
 from Jobs_posted 
 inner join Jobs_Applied on Jobs_posted.job_id = Jobs_Applied.job_id.

I get number of users that applied on job. How can I get job_title with zero number of users. I have also tried left join. Kindly help.

mysql sql

share|improve this question

edited Mar 24 at 9:46

marc_s

585k13011261272

asked Mar 22 at 7:40

Jahanzeb AwanJahanzeb Awan

45

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You title has Oracle SQL and you tagged mysql. So, which of the two DBMS are you using, Oracle or MySQL?
  
  – Thorsten Kettner
  Mar 22 at 7:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What are the tables's primary keys? What role does empl_id play? Is this column important for the query or can we safely ignore it?
  
  – Thorsten Kettner
  Mar 22 at 7:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The combination (Job_id, Empl_id, User_id) = (2, 2, 3) occurs twice in Job_Applied. Do you want this user count twice for the job accordingly or just once?
  
  – Thorsten Kettner
  Mar 22 at 7:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I am downvoting the request. As my questions above show, it is incomplete, and the OP hasn't answered any of the questions. This makes the request rather worthless for any other reader than the OP himself.
  
  – Thorsten Kettner
  Mar 22 at 9:01
  

  
  
  
  

add a comment | 

I have two tables and I want to extract the following requirements :

• Job Title.


• Number of Users.

Tables and sample data:

 **Job_Applied** **Jobs_posted**

Job_id Empl_id User_id | Job_id Empl_id Job_Title
------------------------ --------------------------
 1 2 3 | 1 2 Android
 1 2 4 | 2 2 Web
 1 2 5 | 3 4 Java
 2 2 3 |
 2 2 3 |
 2 2 7 | 
 3 2 5 |

I have tried:

 Select count(User_id) as Users , Job_Title 
 from Jobs_posted 
 inner join Jobs_Applied on Jobs_posted.job_id = Jobs_Applied.job_id.

I get number of users that applied on job. How can I get job_title with zero number of users. I have also tried left join. Kindly help.

mysql sql

share|improve this question

edited Mar 24 at 9:46

marc_s

585k13011261272

asked Mar 22 at 7:40

Jahanzeb AwanJahanzeb Awan

45

 **Job_Applied** **Jobs_posted**

Job_id Empl_id User_id | Job_id Empl_id Job_Title
------------------------ --------------------------
 1 2 3 | 1 2 Android
 1 2 4 | 2 2 Web
 1 2 5 | 3 4 Java
 2 2 3 |
 2 2 3 |
 2 2 7 | 
 3 2 5 |

 Select count(User_id) as Users , Job_Title 
 from Jobs_posted 
 inner join Jobs_Applied on Jobs_posted.job_id = Jobs_Applied.job_id.

share|improve this question

edited Mar 24 at 9:46

marc_s

585k13011261272

asked Mar 22 at 7:40

Jahanzeb AwanJahanzeb Awan

45

share|improve this question

edited Mar 24 at 9:46

marc_s

585k13011261272

asked Mar 22 at 7:40

Jahanzeb AwanJahanzeb Awan

45

edited Mar 24 at 9:46

marc_s

585k13011261272

edited Mar 24 at 9:46

marc_s

585k13011261272

asked Mar 22 at 7:40

Jahanzeb AwanJahanzeb Awan

45

asked Mar 22 at 7:40

Jahanzeb AwanJahanzeb Awan

45

2 Answers
2

active

oldest

votes

0

You just need to group by Job_Title

Select count(distinct User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id = a.Empl_id 
 group by p.Job_Title

share|improve this answer

edited Mar 22 at 7:51

answered Mar 22 at 7:42

Barbaros ÖzhanBarbaros Özhan

15.1k71634

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. But if jobs_applied table is empty I want to show job_title with number of user = 0.
  
  – Jahanzeb Awan
  Mar 22 at 7:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JahanzebAwan you're welcome, you need to use left join then. Because jobs_applied is on the right side. If it was on the left side you would need to use a right join.
  
  – Barbaros Özhan
  Mar 22 at 7:52
  
  

  
  
  
  

add a comment | 

0

use left join

 Select count(a.User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id=a.Empl_id
 group by p.Job_Title

share|improve this answer

answered Mar 22 at 7:46

Zaynul Abadin TuhinZaynul Abadin Tuhin

19.2k31135

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55294925%2ftroubles-with-oracle-sql-query%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

You just need to group by Job_Title

Select count(distinct User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id = a.Empl_id 
 group by p.Job_Title

share|improve this answer

edited Mar 22 at 7:51

answered Mar 22 at 7:42

Barbaros ÖzhanBarbaros Özhan

15.1k71634

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. But if jobs_applied table is empty I want to show job_title with number of user = 0.
  
  – Jahanzeb Awan
  Mar 22 at 7:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JahanzebAwan you're welcome, you need to use left join then. Because jobs_applied is on the right side. If it was on the left side you would need to use a right join.
  
  – Barbaros Özhan
  Mar 22 at 7:52
  
  

  
  
  
  

add a comment | 

0

You just need to group by Job_Title

Select count(distinct User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id = a.Empl_id 
 group by p.Job_Title

share|improve this answer

edited Mar 22 at 7:51

answered Mar 22 at 7:42

Barbaros ÖzhanBarbaros Özhan

15.1k71634

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you. But if jobs_applied table is empty I want to show job_title with number of user = 0.
  
  – Jahanzeb Awan
  Mar 22 at 7:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JahanzebAwan you're welcome, you need to use left join then. Because jobs_applied is on the right side. If it was on the left side you would need to use a right join.
  
  – Barbaros Özhan
  Mar 22 at 7:52
  
  

  
  
  
  

add a comment | 

You just need to group by Job_Title

Select count(distinct User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id = a.Empl_id 
 group by p.Job_Title

share|improve this answer

edited Mar 22 at 7:51

answered Mar 22 at 7:42

Barbaros ÖzhanBarbaros Özhan

15.1k71634

Select count(distinct User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id = a.Empl_id 
 group by p.Job_Title

share|improve this answer

edited Mar 22 at 7:51

answered Mar 22 at 7:42

Barbaros ÖzhanBarbaros Özhan

15.1k71634

answered Mar 22 at 7:42

Barbaros ÖzhanBarbaros Özhan

15.1k71634

answered Mar 22 at 7:42

Barbaros ÖzhanBarbaros Özhan

15.1k71634

0

use left join

 Select count(a.User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id=a.Empl_id
 group by p.Job_Title

share|improve this answer

answered Mar 22 at 7:46

Zaynul Abadin TuhinZaynul Abadin Tuhin

19.2k31135

add a comment | 

0

use left join

 Select count(a.User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id=a.Empl_id
 group by p.Job_Title

share|improve this answer

answered Mar 22 at 7:46

Zaynul Abadin TuhinZaynul Abadin Tuhin

19.2k31135

add a comment | 

use left join

 Select count(a.User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id=a.Empl_id
 group by p.Job_Title

share|improve this answer

answered Mar 22 at 7:46

Zaynul Abadin TuhinZaynul Abadin Tuhin

19.2k31135

 Select count(a.User_id) as Users , p.Job_Title 
 from Jobs_posted p
 left join Jobs_Applied a 
 on p.job_id = a.job_id and p.Empl_id=a.Empl_id
 group by p.Job_Title

share|improve this answer

answered Mar 22 at 7:46

Zaynul Abadin TuhinZaynul Abadin Tuhin

19.2k31135

answered Mar 22 at 7:46

Zaynul Abadin TuhinZaynul Abadin Tuhin

19.2k31135

answered Mar 22 at 7:46

Zaynul Abadin TuhinZaynul Abadin Tuhin

19.2k31135