Page 1 of 1
Getting two title fields to join
Posted: Mon Sep 19, 2005 9:22 am
by bdeonline
Is there any way to get two title fields to join together in one query here is what I have so far:
Code: Select all
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.
Posted: Mon Sep 19, 2005 9:25 am
by feyd
you'll need to use a combining function such as CONCAT() to attach the two titles together..
Posted: Mon Sep 19, 2005 9:33 am
by bdeonline
I've tried this but the title field is comming up NULL:
Code: Select all
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.
Posted: Mon Sep 19, 2005 9:38 am
by feyd
Code: Select all
mysql> select concat('test',null);
+---------------------+
| concat('test',null) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.16 sec)
mysql> select concat_ws('','test',null);
+---------------------------+
| concat_ws('','test',null) |
+---------------------------+
| test |
+---------------------------+
1 row in set (0.00 sec)
Posted: Mon Sep 19, 2005 10:17 am
by bdeonline
I'm not understanding, I get that CONCAT will join together strings but can't get it to join together fields.
Posted: Mon Sep 19, 2005 10:21 am
by bdeonline
Wait I think I am getting it looking up the sytax using:
Code: Select all
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
Does work is that the best way to do this.
Posted: Mon Sep 19, 2005 10:21 am
by feyd
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.
Posted: Mon Sep 19, 2005 10:23 am
by feyd
null as the first parameter to CONCAT_WS() will result in a NULL value, always, from what I'm seeing.
Posted: Mon Sep 19, 2005 10:29 am
by bdeonline
Thats what I am seeing aswell.
Ok then why can't I use a right join, is it because the field types don't all match up.
Posted: Mon Sep 19, 2005 10:42 am
by bdeonline
Never mind this seems to work better and is easier to understand:
Code: Select all
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)
And this does just what I want:
Code: Select all
(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