-1

enter image description hereMy query is like this:

SELECT * FROM inscription i
INNER JOIN employeGroupe ge
ON i.IDGroupe=ge.IDGroupe
INNER JOIN Employe e
ON e.IDEmploye=ge.IDEmploye
INNER JOIN cOURS BC
on bc.IDCours=i.IDCours

I have 4 tables as follows:

Inscription
EmployeeGroupe
Employee
Cours
Column IDGroupe in table Inscription relates to table EmployeeGroupe
Column IDEmploe in table EmployeeGroupe relates to table Employee
Column IDCours in table Inscription relates to table Cours

My problem is that I want to display the names of related employes to cours. We can find the cours into inscription table. Not that INscription.IDGroupe for some cours is null then I am not able to bring up the EmployeGroupe related which go up to EmployeID in Employe table.

My query is like this:

SELECT * FROM inscription i
INNER JOIN employeGroupe ge
ON i.IDGroupe=ge.IDGroupe
INNER JOIN Employe e
ON e.IDEmploye=ge.IDEmploye
INNER JOIN cOURS BC
on bc.IDCours=i.IDCours
Blessed
  • 55
  • 6
  • 5
    Tag your question with the database you are using. Sample data and desired results would help. – Gordon Linoff Sep 23 '19 at 19:03
  • 3
    inner join will *only* return matching results. You'll need left join (or right join or full outer join) to *also* return non-matching results (null). I would start by changing everything to left join instead of inner join and see if that gets you the results you desire. – avery_larry Sep 23 '19 at 19:11
  • 1
    Sample data and expected result, please. – The Impaler Sep 23 '19 at 20:16
  • My results: https://i.stack.imgur.com/gbFc7.jpg – Blessed Sep 23 '19 at 20:54
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Also, links die. Insert images/links using edit functions. Make your post self-contained. Please clarify via edits, not comments. – philipxy Sep 23 '19 at 22:04
  • Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. – philipxy Sep 23 '19 at 22:05
  • Learn what OUTER JOINs return: LEFT/RIGHT JOIN ON return INNER JOIN ON rows UNION ALL unmatched left/right table rows extended by NULLs. FULL JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right/left [sic] table column to be not NULL after a LEFT/RIGHT JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". Similarly for FULL JOIN ON. You have that in your image code. – philipxy Sep 23 '19 at 22:16
  • Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) – philipxy Sep 23 '19 at 22:17
  • Please note that If I use left outer join or any other join I am getting no results at all. I used full join just to show you the kind of data i have. SELECT bc.Numero AS IDCOurs ,bc.Titre as CoursName ,e.IDEmploye ,e.NomPrenom FROM Inscription i LEFT OUTER JOIN EmployeGroupe ge ON i.IDGroupe=ge.IDGroupe LEFT OUTER JOIN Employes.Employe e ON e.IDEmploye=ge.IDEmploye LEFT OUTER JOIN [Cours] bc ON bc.IDCours=i.IDCours WHERE e.Numero in ('601TST00') ---(0 row(s) affected) – Blessed Sep 24 '19 at 19:11
  • This post's language & organization are not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. To describe a result: Say enough that someone could go away & come back with a solution. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. Clarify via edits, not comments. (What is that comment code for?) Also please act on my 1st two comments. – philipxy Sep 24 '19 at 23:05

1 Answers1

0

Try something like this

SELECT * from inscription I
    LEFT JOIN employeGroupe ge
        ON (ISNULL((i.IDGroupe),0) OR (i.IDGroupe = ge.IDGroupe))
    LEFT JOIN  Employe e
        ON e.IDEmploye = ge.IDEmploye
    LEFT JOIN cOURS bc
        ON bc.IDCours = i.IDCours
GokuMizuno
  • 493
  • 2
  • 5
  • 14
  • Plz your query is giving an error on ISNULL function. It requires two arguments and also SQL wants me to put it in WHERE Clause. Thanks – Blessed Sep 23 '19 at 20:55