CODEWITHSUNDEEP
mysqlsql
Dr.Kameleon
Dr.Kameleon

Reputation: 22820

Trouble with MySQL query

OK, first-off I admit MySQL Syntax has never been my strongest point. So, here I am.

Urls :

ID   Url                              Code
===============================================
1    http://www.google.com            Abcd
2    http://www.freetemplates4u.com   Efgh
3    ...

Posts :

ID  Title            Address
===============================================
1   Some Title 1     http://mize.it/Abcd
2   Some Title 2     http://mize.it/Efgh
3   ...

I want to create a query to fetch the following table

Title             Url
=======================================================
Some Title 1      http://www.google.com
Some Title 2      http://www.freetemplates4u.com

In a few words :

I know it has something to do with joins and concatenation, but I'm definitely lost.

SIDENOTE : I don't care neither about my current database's structure, nor about performance issues. All I want is to transfer existing data, from the existing database (without having to alter it), to my new website's database (under a totally different format/structure).

Upvotes: 0

Views: 67

Answers (4)

Fabio
Fabio

Reputation: 23510

This is a different approch, it took a while for me to test it.

Since your Address field contains complete url and we only need to match what is after / so we can replace actual url with nothing (I assume url is always the same) and have string ready to be matched with Code field.

SELECT b.Title, a.URL
FROM Url a
LEFT JOIN Posts b
ON a.Code = REPLACE(b.Address, 'http://mize.it/', '')
ORDER BY a.ID ASC

Upvotes: 0

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31743

You should change your DB-Design, this query will have a poor performance since mysql has to do a full tablescan.

Try adding a code column in your Posts table hat has the right value (populate it on insert/update) and add an index to Code (both tables).

Now you should be able to do.

SELECT Posts.Title, Urls.Url
FROM Posts
INNER JOIN Urls ON Post.Code = Urls.Code

Update:

If the first part of the url is always the same, this will work

SELECT Post.Title, Urls.Url
FROM Posts
INNER JOIN Urls ON Post.Adress = CONCAT('http://mize.it/', Urls.Code)

Upvotes: 3

xkeshav
xkeshav

Reputation: 54084

TRY 

SELECT p.title,x.url
FROM Posts p 
INNER JOIN ( SELECT url, CONCAT('http://mize.it/',code) AS xcode FROM Urls ) x  
ON (x.xcode = p.address)

Working DEMO

Upvotes: 1

MRX
MRX

Reputation: 1651

Check following query.

select m1.Url, m2.Title from Urls as m1, Posts as m2 

where m2.address like 'http://mize.it/%'

Upvotes: -1

Related Questions