[SOLVED] joining two sets of like data from different tables

[Zeradin]

I am trying to select the id and title from two tables and combine them together using code

but I keep getting an "ambiguous" error. How do I fix this? I know this code will only select from reviews, but I think it gets across where I'm trying to go with this:

 

'SELECT id, title FROM reviews JOIN articles ON(reviews.reviewer=articles.writer) WHERE reviews.reviewer = "'.$info['username'].'" AND visible = 1 ORDER BY timestamp DESC';

[Mchl]

Perhaps 'visible' or 'timestamp' column exists in both tables? Besides you should put timestamp column name in `` as it is a reserved word.

 

Paste full error text.

[Zeradin]

I do have the both in both tables, actually I was looking for a way to combine them so i can sort reviews and articles together and see which is newest between the two. here is the error:

 

Error in query: SELECT id, title FROM reviews, articles WHERE reviews.reviewer = articles.writer AND reviews.reviewer = "1=1?" AND visible = 1 ORDER BY `timestamp` DESC. Column 'id' in field list is ambiguous

 

I changed the query a little to get it to work, which it didn't. Same error though.

[Mchl]

You have 'id' in both tables. You must tell which one you want to select.

[Zeradin]

I want to select both. Like. I want to determine what the latest anything that someone has submitted. So i want to look in articles and reviews, sort them both by timestamp in the same list and then be able to figure out what is the newest anything they've submitted. That make sense?

[Mchl]

use UNION

[Zeradin]

I never used union before. I tried a union that didn't look this earlier and i got errors. This one gives me this error:

 

Error in query: SELECT id, title, `timestamp` FROM((SELECT id, title, `timstamp` FROM reviews WHERE reviewer = "1=1?" AND visible = 1 ORDER BY `timestamp` DESC) UNION (SELECT id, title, `timstamp` FROM articles WHERE writer = "1=1?" AND visible = 1 ORDER BY `timestamp` DESC)). Every derived table must have its own alias

 

based on something i got from dev.mysql.com

SELECT * FROM ((

SELECT * FROM table1

WHERE ...

ORDER BY ...

LIMIT ...

) UNION (

SELECT * FROM table2

WHERE ...

ORDER BY ...

LIMIT ...

)) as t

WHERE ...

ORDER BY ...

 

What's the proper way to use union. last time i did like select id, title FROM reviews UNION articles

[Mchl]

SELECT id, title FROM reviews UNION SELECT id, title FROM articles

[Zeradin]

sorry to be running into problems every step of the way here but

i keep running into problems.

how do i do a where clause with this? i tried one each on both sides of the union

i've tried reviewer=writer=username

i've tried reviews.reviewer=articles.writer=username

i've tried reviewer = username AND writer = username

 

this is so aggravating

thanks for helping so much though

[Mchl]

SELECT id, title FROM reviews AS r WHERE r.reviewer = 'username'

UNION

SELECT id, title FROM articles AS a WHERE a.writer = 'username'

 

This should work...

[Zeradin]

NICE! I almost came back with another question implementing the ORDER BY but i used google and figured it out. Kind of weird... never seen any sql query like it with order by position. in case anyone was wondering this is how i got it done:

 

'SELECT id, title, `timestamp` FROM reviews AS r WHERE r.reviewer = "'.$info['username'].'"
AND visible = 1
UNION ALL
SELECT id, title, `timestamp` FROM articles AS a WHERE a.writer = "'.$info['username'].'" 
AND visible = 1 ORDER BY 3 DESC';

 

weird.

Mchl, you're awesome for helping so much. Thanks again.

[Mchl]

'SELECT id, title, `timestamp` FROM reviews AS r WHERE r.reviewer = "'.$info['username'].'"
AND visible = 1
UNION ALL
SELECT id, title, `timestamp` FROM articles AS a WHERE a.writer = "'.$info['username'].'" 
AND visible = 1 ORDER BY `timestamp` DESC';

 

should work as well...

 

And check this out for extra information

 

'SELECT 'Review' AS type, id, title, `timestamp` FROM reviews AS r WHERE r.reviewer = "'.$info['username'].'"
AND visible = 1
UNION ALL
SELECT 'Article', id, title, `timestamp` FROM articles AS a WHERE a.writer = "'.$info['username'].'" 
AND visible = 1 ORDER BY `timestamp` DESC';

[Zeradin]

when i tried doing it with `timestamp` it said it wasn't a valid column in ORDER BY or something. I didn't really understand what it meant. What're you doing with that other one, is that like creating objects in php?

[Mchl]

No. It's just adding extra column that says, whether given row is an article or a review

[Zeradin]

oh sweet! that's amazing. I didn't know you could do anything like that. That will be useful. thanks man=)