Getting two title fields to join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Getting two title fields to join

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you'll need to use a combining function such as CONCAT() to attach the two titles together..
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Post by bdeonline »

I'm not understanding, I get that CONCAT will join together strings but can't get it to join together fields.
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

null as the first parameter to CONCAT_WS() will result in a NULL value, always, from what I'm seeing.
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Post 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.
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Post 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
Post Reply