SELECT items.id, *.title, items.ordering FROM items
LEFT JOIN pages ON items.id = pages.id
LEFT JOIN links ON items.id = links.id
I've got the pages table joining items and then have the links table joining items now I want to get the title field that is in both pages and links to show up as one field in the query as the id and ordering do.
SELECT items.id, CONCAT(pages.title, links.title) AS title, items.ordering FROM items
LEFT JOIN pages ON items.id = pages.id
LEFT JOIN links ON items.id = links.id
The title fields of both are the same type, lenght, and collation.
I think its because of the left join but when I use JOIN itself it doesn't retrieve any rows.
SELECT items.id, CONCAT_WS(null, pages.title, links.title), items.ordering FROM items
LEFT JOIN pages ON items.id = pages.id
LEFT JOIN links ON items.id = links.id
the fields are "strings" to it.. CONCAT() will return NULL if any one of the values passed to it are NULL. Since you're using LEFT JOIN, either or both could be NULL at anytime. CONCAT_WS() will ignore any NULL values given to it, still returning a string of the resultant combination.
SELECT items.id, pages.title, items.ordering FROM items JOIN pages USING (id)
UNION DISTINCT
SELECT items.id, links.title, items.ordering FROM items JOIN links USING (id)
(SELECT items.id, pages.title, items.ordering FROM items JOIN pages USING (id))
UNION DISTINCT
(SELECT items.id, links.title, items.ordering FROM items JOIN links USING (id))
ORDER BY ordering