sql join question

Question

I have the following tables

nid    timestamp   title

82  1245157883  Home
61  1245100302  Minutes
132    1245097268  Sample Form
95  1245096985  Goals & Objectives
99  1245096952  Members

AND

        pid     src      dst                                  language



        70  node/82         department/34-section-2   
        45  node/61/feed  department/22-section-2/feed   
        26  node/15      department/department1/15-department1   
        303 node/101      department/101-section-4

These are fragments of the tables, and is missing the rest of the data (they are both quite large), but I am trying to join the dst column from the second table into the first one. They should match up on their "nid", but the second table has node/[nid] which makes this more complicated. I also want to ignore the ones that end in "feed" since they are not needed for what I am doing.

Much thanks

EDIT: I feel bad for not mentioning this, but the first table is an sql result from

select nid, MAX(timestamp) as timestamp, title from node_revisions group by nid ORDER BY timestamp DESC LIMIT 0,5

The second table has the name "url_alias"

Answer 1

try

select * from table1 inner join table2 on src=concat('node/',nid)

Edit
edited to reflect change in OP

select `nid`, MAX(`timestamp`) as `timestamp`, `title` from `node_revisions` inner join `url_alias` on `src`=concat('node/',`nid`) group by `nid` ORDER BY `timestamp` DESC LIMIT 0,5

Answer 2

I don't know what database you are using. However, I suggest you write a parsing function that returns the nid from that column. Then, you can have this kind of query (assuming GET_NID is the function you defined):

SELECT * from T1, T2
WHERE T1.nid = GET_NID( T2.node)

Answer 3

You have a few options.

1. write a function that converts src to an nid and join on t1.nid = f(t2.src) -- you didn't say what DBMS you use, but most have a way to do that. It will be slow, but that depends on how big the tables are.

2. Similar to that, make a view that has a computed field using that function -- same speed, but might be easier to understand.

3. Create a new nid field in t2 and use the function to populate it. Make insert and update triggers to keep it up to date, then join on that. This is better if you query this frequently.

4. Convert t2 so that it has a nid field and compute the src from that and another field that is a template that the nid needs to be inserted into.

Answer 4

SELECT *
FROM (SELECT *, 'node/' + nid AS src FROM table1) t1
INNER JOIN table2 t2
ON t1.src = t2.src

Answer 5

I'd pull the node id in the second table into a separate column. Otherwise any attempt to join the two tables will result in a table scan with some processing on the src field (I assume you meant the src field and not the dst field) and performance will be problematic.

Answer 6

Depending on the scenario you want to this for (if for example you are regularly going to be performing this JOIN and your 2nd table is rather large) you may want to look into a Materialized View.

Write a function that performs all the logic to extract the nid into a separate column. Aside from initial m-view creation, the function will only need to run when the basetable changes (insert, update, delete) compared to running the function against every row each time you query.

This allows a fairly simple join to the materialized view with standard benefits of tables such as Indexing.

NB: looks like I was beaten to it while writing :)

Answer 7

You haven't specified with DBMS are you using. Most engines support the SQL-99 standard SIMILAR TO clause which is using regular expression for matching. Some engines also implement this, but use some other keywords instead of SIMILAR TO.

FirebirdSQL:
http://wiki.firebirdsql.org/wiki/index.php?page=SIMILAR+TO

PostgreSQL:
http://www.network-theory.co.uk/docs/postgresql/vol1/SIMILARTORegularExpressions.html

MySQL:
http://dev.mysql.com/doc/refman/5.0/en/regexp.html