0

I would like to know why my sql query don't return me all my row (just the first one):

SELECT *
FROM ___Projects
INNER JOIN ___Regions
ON ___Projects.REG_Id=___Regions.REG_Id
ORDER BY PRO_Id ASC

Table ___Projects:

|--------|--------|--------|
|PRO_Id  |REG_Id  |PRO_Name|
|1       |1       |Projet 1|
|2       |0       |Projet 2|
|3       |0       |Projet 3|
|--------|--------|--------|

Table ___Regions

|--------|--------|
|REG_Id  |REG_Name|
|1       |Region 1|
|2       |Region 2|
|3       |Region 3|
|--------|--------|

Thanks for your help.

  • Not entirely sure.. Maybe because of your `ON ___Projects.REG_Id=___Regions.REG_Id` and the only thing that corresponds to that condition is where `REG_Id` is 1, which is Projet 1 – AL. May 26 '16 at 02:47
  • I think is because on table `___Projects ` on `REG_Id` I do not have a link with `___Regions` as it's `0`. Perhaps ? –  May 26 '16 at 02:48
  • Yep. Exactly. Try changing the other `REG_Id`s to 2 and 3.. Or are you aiming to match `ON ___Projects.PRO_Id=___Regions.REG_Id` instead? – AL. May 26 '16 at 02:50
  • Try it out. Let me know what happens. – AL. May 26 '16 at 02:50
  • Do you want all of the records in `___Projects`? – Blank May 26 '16 at 02:50
  • @Reno: Yes please. –  May 26 '16 at 02:52

2 Answers2

1

Use LEFT JOIN;)

SELECT *
FROM ___Projects
LEFT JOIN ___Regions
ON ___Projects.REG_Id=___Regions.REG_Id
ORDER BY PRO_Id ASC
Blank
  • 12,308
  • 1
  • 14
  • 32
  • `INNER JOIN` will match record both in two table on condition, and `LEFT JOIN` will fetch all records in left table and only fetch records on condition in right table. – Blank May 26 '16 at 03:00
  • Take a look of [Difference between INNER and OUTER joins](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins). – Blank May 26 '16 at 03:01
1

INNER JOIN is used when you want your result to be a common in both the tables.

Therefore INNER JOIN will result in the output of row matching both the tables.

Using LEFT JOIN will take all the rows from the table on the left hand side of the keyword JOIN in your query and takes only the matching record from the right side table.

use query given by @reno

Saahil
  • 54
  • 4