Column is Ambiguous?

Having a little trouble selecting info from 2 tables using a search query.

The methods I've tried are:

$query = ("select id
from publication
inner join publication_issue on publication.id=publication_issue.publication_id
where publication.tags='%news%' ORDER BY id LIMIT 0,10");
 $result = mysql_query($query) or die(mysql_error());
   while($row = mysql_fetch_array($result)){
    $title = $row['title'];
	echo "$title<br />";
}

PHP:

I also tried:

$query = ("select * from publication p, publication_issue i
where i.publication_id = p.id
and p.tags like '%news%'
order by id
limit 0,10");
 $result = mysql_query($query) or die(mysql_error());
   while($row = mysql_fetch_array($result)){
    $title = $row['title'];
	echo "$title<br />";
}

PHP:

But both come back with:

I don't fully understand, I know that the info is the same in the 2 tables, that's why I want to relate it.

There's 1 publication to many issues, so publication has default attributes such as description, tags, category and subcategory where the issues have image, url, file etc.

Could somebody help explain how I should go about this query? The %news% is just a test string, the user will use a search form to submit the query.

Thanks.

 

Problem solved. Turned out that changing this line:

to:

Works. I think it's because I was trying to order by id and id existed in both tables so it was like WTF?!

 

You can still order by the id, you just need to specify the table you intend to sort from.

select publication.id
from publication
inner join publication_issue on publication.id=publication_issue.publication_id
where publication.tags='%news%' ORDER BY publication.id LIMIT 0,10

Code (markup):

Or:

select * from publication p, publication_issue i
where i.publication_id = p.id
and p.tags like '%news%'
order by p.id
limit 0,10

Code (markup):

I always suggest using the first example. Keep JOIN conditions independent of the where clause.

 

Thanks for the heads up.