I am still learning php and would be glad if somebody help me to understand the processes of the following select query.

The below stated select query does not contain any results (rows) in its result set if some variables contain no entry in the database (in the respective row). Why is this the case and how can I still retrieve results even if some variables contain no entries?

Here is the query

p2g.id and p2gtext are part of the p2g tablename, email are part of the author table

In the table "author" are no entries made. Therefore the variables name and email are empty. The table p2g contains entries. If I run the query no results are shown (the if statement is applied)

I get the results if I do not "Inner Join" the author table, and therefore all variables have entries in respective rows.I also get the results if I insert data into the author table. But only the results will be show which have an entry in ALL variables (in the respective row).

Why there is an entry needed in all variables for a select query to show the results?And how can I alter the select query that results are still shown, even if not all variables contain an entry for the respective row?

Is there a possibility that I can change the select request, that all results (rows) of both tables are shown. This is because, for instance, I like to check whether each joke an author as been assigned in the author table ( the table which add with INNER JOIN).

Thanks for an answer in advance.

guido2004
—
2014-04-27T11:26:56Z —
#5

If you want to extract all jokes that DON'T have an author that exists in the autors table, add the red line to the WHERE statement:

thanks for providing me that code! Can you also think of a select query in terms of: "Select all rows regardless whether the entry is null or sth, else in a row"? when using INNER JOIN between tables?

(I like to avoid to state for every variable a specific statement that it can be null or sth. else)

I would be glad if you can look at this problem.

guido2004
—
2014-04-27T12:43:08Z —
#7

The solution to that has already been given by others, and it isn't an inner join

tribe_of_frog
—
2014-04-27T14:59:50Z —
#8

Guido, thanks for your quick reply, do you have a link for me where I can read about this problem? Or does this problem refer to a certain wording I could look up in the forum? It would be nice if you can give me a hint on it because I already tried to google it, but seems that I use the wrong words.

Thanks

guido2004
—
2014-04-27T16:02:27Z —
#9

If you want all rows from the first table, even if the authorid doesn't exist in the authors table, just take off the WHERE condition from the query I posted.

As explained in this thread by xMog, and also in the article Mittineague linked to, INNER JOIN only gives you those rows where the two tables actually have the same authorid. The pictures in the article are very clear, did you read it?

tribe_of_frog
—
2014-04-27T16:56:04Z —
#10

Hi Guido,

thanks for your note, now with the explanations on the example you posted on my data I understand. Thanks and sorry for questioning a lot. Still very new complex topic for me. But lots of fun. Thanks again for being active on the post to help out!