Timmy Lennon
Timmy Lennon

Reputation: 11

Inner-Join query is returning no data

Trying to run a query which builds a list of reports that all have information stored about them. When i run this query without the INNER JOIN part it works fine, and there are a list of the reports with their information displayed respectively. When i try and run this with the INNER JOIN query inserted it returns no data. Both tables definitely have data in them, no idea what the issue could be. Thanks.

This is the query that works and returns data before the INNER JOIN method is added:

SELECT reports_tbl.photoname
    , reports_tbl.location
    , reports_tbl.details
    , reports_tbl.image
    , reports_tbl.spotteddate
    , reports_tbl.uploaddate
    , typesofphoto_tbl.typename 
FROM reports_tbl 

This is the query that returns no data containing the INNER JOIN:

SELECT reports_tbl.photoname
     , reports_tbl.location
     , reports_tbl.details
     , reports_tbl.image
     , reports_tbl.spotteddate
     , reports_tbl.uploaddate
     , typesofphoto_tbl.typename 
FROM reports_tbl 
INNER JOIN typesofphoto_tbl 
ON reports_tbl.typeofphotoID = typesofphoto_tbl.ID

Upvotes: 1

Views: 2181

Answers (1)

A_Sk
A_Sk

Reputation: 4630

INNER JOIN: Returns all rows when there is at least one match in BOTH tables.

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables

so, when you are using Inner Join, there is no match in both table reports_tbl and typesofphoto_tbl thats why you are not getting the data.

try LEFT-JOIN

SELECT reports_tbl.photoname
     , reports_tbl.location
     , reports_tbl.details
     , reports_tbl.image
     , reports_tbl.spotteddate
     , reports_tbl.uploaddate
     , typesofphoto_tbl.typename 
FROM reports_tbl 
LEFT JOIN typesofphoto_tbl 
ON reports_tbl.typeofphotoID = typesofphoto_tbl.ID

you'll get the record from the left table reports_tbl if there are no matching row in right table typesofphoto_tbl .

Upvotes: 1

Related Questions