"Does not contain" ...

[empoor]

Hey. I've got this code,

// query article
	$query1 = "SELECT * FROM (articles LEFT JOIN authorisation ON articles.page_id = authorisation.page_id LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE articles.page_id NOT IN (authorisation.page_id) ORDER BY issue ASC";

	$mysql_result1 = mysql_query ($query1)
		or die ("Query '$query1' failed with error message: \"" . mysql_error () . '"');
	
	// Print
	
	while($row = mysql_fetch_array($mysql_result1))
	{
	
		echo '							<tr>
							<td>"' . $row['page_title'] . '"</td>
							<td> (' . $row['page_id'] . ')</td>
							<td class="lightgrey">' . $row['author'] . '</td>
						</tr>
						<tr class="comment">
							<td colspan="3" id="comment' . $row['page_id'] . '">' . $row['authorisation_comment'] . '</td>
						</tr>';
	}

, and it's supposed to look for the following: if a row in 'articles' does not exist in 'authorisation' (based upon its "page_id"), it should return said row and put it into the table. When it does exist in both tables, ignore.

But obviously it's not working. Can anyone help me out?

(P.S. For convenience, I have left out every column that needs to be selected and replaced it with the all-encompassing *. In the actual code it only selects the needed columns.)

[SimonMayer]

One thing I would do differently (and I am not sure if this is the cause or not) is to add an extra level of brackets into the query:

$query1 = "SELECT * FROM ((articles LEFT JOIN authorisation ON articles.page_id = authorisation.page_id) LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE articles.page_id NOT IN (authorisation.page_id) ORDER BY issue ASC";

If this doesn't help, please could you say how it isn't working?
Do you get any output at all or are you getting no rows?

[empoor]

Thanks for the reply! I added the extra level, but it didn't change anything.

It's not working in the sense that it isn't outputting any rows, even though it should. There's no error or anything, just an empty table.

I'm guessing the problem lies somewhere in the area around:

WHERE articles.page_id NOT IN (authorisation.page_id)

[SimonMayer]

I think this will need another approach.

Now sadly I wouldn't be able to give the exact syntax without testing it and playing around, but first of all I would start with

SELECT * FROM ((articles LEFT JOIN authorisation ON articles.page_id = authorisation.page_id) LEFT JOIN authors ON articles.author_id = authors.author_id) ORDER BY issue ASC

This will give you all rows in table `articles`. I would do this once so you can see what is returned. Note: in a worst-case scenario, you will be able to filter down the relevant rows with PHP.

What I would then attempt is something like

SELECT * FROM ((articles LEFT JOIN authorisation ON articles.page_id = authorisation.page_id) LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE authorisation.page_id IS NULL ORDER BY issue ASC

The idea behind this is that because all rows of `articles` are searched, with the `authorisation` table left joined, you would then want to find the rows where `authorisation` values were not joined.
I expect IS NULL will do the trick, but I have never used it before, so cannot be sure. I anticipate that the performance of IS NULL may also depend on whether the field allows NULL values, so perhaps also try it with a different `authorisation` field if necessary.

If IS NULL isn't suitable, perhaps there will be another way of identifying where `authorisation`.`page_id` has not been declared.

[empoor]

Tried it out, and, by golly, it works perfectly! Well, for some reason it can't identify the 'articles.page_id' any more, but that's not an issue right now.

I also fiddled with the data in 'authorisation' and 'articles' to make sure the data on the webpage really changed, and it did, so it finally works.

Thank you very much for the help and explanation!

[SimonMayer]

Well, for some reason it can't identify the 'articles.page_id' any more, but that's not an issue right now.

I think that is because there are two instances of `page_id` -
`articles.page_id` and `authorisation.page_id`

Calling `articles.page_id` as a unique name should sort that:

SELECT `articles.page_id` AS `article_page_id` FROM ((articles LEFT JOIN authorisation ON `article_page_id` = authorisation.page_id) LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE authorisation.page_id IS NULL ORDER BY issue ASC

You would then have to use $row['article_page_id'] to call this

[empoor]

Ah, that's a good fix, thanks! (Learned something new, again. Going to use that AS syntax more often now.)

But I already sorted it out. I actually didn't use SELECT * in the code, but SELECT [whatever I needed], and my problem was that I was both selecting 'articles.page_id' and 'authorisation.page_id'. I removed the second 'page_id' and it worked.