Pulling Values from one table for a select statement

Ok, I'm trying to end up with a view that returns some fields from a Drupal instance running on MySQL. The problem is this information is spread out over three tables:

table 1 (keyword_search_table): contains the following fields:

id, keyword, dst
example data:
1, 'chicken', 'aboutus/fred'
2, 'boxes', 'services/patching_stuff'

table 2 (url_alias): contains the following fields:

pid, src, dst
example data:
45, 'node/22','aboustus/fred'
46, 'node/36','patching_stuff'

table 3 (node_revisions): contains the following fields:

nid, title, teaser
example data:

22, 'About Fred - Our President', 'Fred is a great guy and has been pres for 4 years...'
36, 'Patching Your Deck', 'Patching your deck should be done using barbed wire..."

I'm trying to end up with this:

keyword, destination_url, title, teaser_text

example output based on data above:

1. chicken, aboutus/fred, About Fred - Our President, Fred is a great guy and has been pres for 4 years...
2. Boxes, services/patching_stuff, Patching Your Deck, Patching your deck should be done using barbed wire...

Im guessing I need to do some inner outer and left joins...I'm bad at writing this stuff. Any ideas?

 

Yes if you wish to join tables you can use 'Left Join' or 'Inner Join'. I use Left Join simple because that's what i know.

This is an example of Left Join query:

$qry="
SELECT *
FROM table1
LEFT JOIN table2 USING("your_common_field")
";

Code (markup):

or

$qry="
SELECT *
FROM table1
LEFT JOIN table2 ON this_field_value = that_field_value
";

Code (markup):

The first example is my preference. If you are planning on linking tables they should usually share a common field. For example in an online shop I could Left Join the "Products" table with the "Images" table USING(product_ID). This makes queries a lot easier and less mind boggling.

Hope that helps!

W.

 

The below query should work out. Please try

select t1.keyword,t1.dst,t3.title,t3.teaser
from table1 t1
inner join table2 t2
on t1.dst = t2.dst
inner join table3 t3
on substring(t2.src,6,2) = t3.nid