Othuna Othuna - 3 months ago 10
MySQL Question

Sql select from multiple tables where in doesn't work

I am creating a website where there are multiple pages and each page has a table in the database and when you follow pages you like. I want posts from the pages you follow to show up in your newsfeed. This will need to select from multiple tables. This is what i tried so far:

Select page-id, id, content
from page1, page2, page3, page4
where page-id in(select p_id from follows where p_username = '$me')


But I get this error:

Column 'page-id' in field list is ambiguous


Help?

Answer
Column 'page-id' in field list is ambiguous

That means that "page-id" find it in more than one table, you must use the table name to identify or alias

Select a.page-id, id, content
from page1 a, page2 b, page3 c, page4 d
where a.page-id in(select p_id from follows where p_username = '$me')

but I do not think that's what you want with that query, you have to explain more.

EDIT, reading the scaisEdge's answer

If you want get all tables as one, you need UNION

 (SELECT page-id, id, content
  FROM page1
  WHERE page-id in(SELECT p_id FROM follows WHERE p_username = '$me'))
UNION
 (SELECT page-id, id, content
  FROM page2
  WHERE page-id in(SELECT p_id FROM follows WHERE p_username = '$me'))
UNION
 (SELECT ...) ...

But, you know the number of pages? you can make the query with php dynamically.