CODEWITHSUNDEEP
phpmysql
Snippet
Snippet

Reputation: 1560

mysql joining two table without in common

i have this script.. first i run query 1 and store into array then query 2, using a foreach, i combine them and create a list of urls..but this takes time.. is there a way i can do this just in mysql by combining the table even do they have no common column?

query 1 

 SELECT 
    c.id, 
    c.city_name,
    r.region_name,
    cr.country_name
    FROM city AS c, region AS r, country AS cr
    WHERE r.id = c.id_region 
         AND cr.id = c.id_country 
         AND cr.id IN 
           (SELECT id FROM country WHERE used = 1)

query 2

  SELECT id, title FROM  param WHERE active = 1

loop

  foreach ($arrayCity as $city) {
     foreach ($arrayParam  as $param ) {
        $paramTitle = str_replace(' ', '+', $param['title']);
        $url = 'http://url/city/'. $city['id'] .'/paramId/'. $param['id'] .'/'. 
                $paramTitle .'/'. $city['region_name'] .'/'. $city['city_name'];
            }
        }

Upvotes: 0

Views: 95

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146469

How about

   Select 'http://url/city/' + c.id + '/paramId/' + p.id + '/' + 
           Replace(title, ' ', '+') + '/' + r.region_Name + '/' + c.city_Name
   From city c 
       Join region r On r.id = c.id_region 
       Join country n On n.id = c.id_country
       cross join param p
   Where n.Uused = 1
      And p.active = 1

Upvotes: 0

BClaydon
BClaydon

Reputation: 1970

Something like this. You can select a concatenated string with results from your base table. 

SELECT 
    c.id, 
    c.city_name,
    r.region_name,
    cr.country_name,
    ('http://url/city/' + c.city_name + '/' + p.param + '/' + c.id) AS URL
    FROM city AS c, region AS r, country AS cr
    JOIN param p ON c.id = p.id
    WHERE r.id = c.id_region AND cr.id = c.id_country AND cr.id IN (SELECT id FROM country WHERE used = 1)

My experience is in TSQL, but it'll be close in my SQL

Edit: Sorry, forgot the join

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191729

You don't have to join on a condition. Not doing so is called a CROSS JOIN. The CROSS keyword is optional. In fact, you are already doing this because , is a synonym.

FROM city AS c, region AS r, country AS cr, param

Upvotes: 1

Related Questions