Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

SQL one to many query

i have to tables one with a list of countries
countrlist table
id                   name
1      Afghanistan
2      Albania
3      Andorra
4      Austria

and the other table with country specfic with breakout

destination                                       rate
Afghanistan                                                            0.22
Afghanistan-Kabul                                                      0.22
Afghanistan-Mobile                                                     0.20
Afghanistan-Mobile-Areeba                                              0.22
Afghanistan-Mobile-Etisalat                                            0.19
Afghanistan-Mobile-Roshan                                              0.21
Albania                                                                0.05
Albania-Mobile                                                         0.20
Albania-Mobile-AMC                                                     0.26
Albania-Mobile-Vodafone                                                0.13
Albania-Tirana                                                         0.03
Algeria                                                                0.05
Algeria-Algiers                                                        0.05
Algeria-Mobile                                                         0.18
Algeria-Mobile-Orascom                                                 0.32
Algeria-Mobile-Wataniya                                                0.31
American Samoa                                                         0.04
Andorra                                                                0.02
Andorra-Mobile                                                         0.18
Angola                                                                 0.11
Angola-Mobile                                                          0.10
Angola-Mobile-Unitel                                                   0.10
Anguilla                                                               0.11
Anguilla-Mobile                                                        0.18
Anguilla-Mobile-CW                                                     0.19
Anguilla-Mobile-Digicel                                                0.18
Antarctic Territories                                                  1.18
Antigua & Barbuda                                                      0.16
Antigua & Barbuda-Mobile                                               0.13
Antilles                                                               0.13
Antilles-Curacao                                                       0.07
Antilles-Mobile                                                        0.12
Antilles-Mobile-Curacao                                                0.11
Antilles-Mobile-Ecc                                                    0.12
Antilles-St. Maarten                                                   0.08
Armenia                                                                0.09
Armenia Mobile-K-Telecom                                               0.14
Armenia-Karabakh                                                       0.19
Armenia-Mobile                                                         0.15
Aruba                                                                  0.09
Aruba-Mobile                                                           0.19
Aruba-Mobile-Digicell                                                  0.21
Aruba-Mobile-MIO                                                       0.20
Ascension Island                                                       1.59
Australia                                                              0.02
Australia-Adelaide/Perth                                               0.01
Australia-Cities                                                       0.01
Australia-Mobile                                                       0.10
Australia-Mobile-Optus                                                 0.09
Australia-MobileSat                                                    0.10
Australia-Mobile-Telstra                                               0.09
Australia-Mobile-Vodafone                                              0.10
Australia-NGN                                                          0.03
Austria                                                                0.02
Austria-Mobile                                                         0.30
Austria-Mobile-Hutchinson                                              0.04
Austria-Mobile-Mobilcom                                                0.05
Austria-Mobile-Orange                                                  0.04
Austria-Mobile-T Mobile                                                0.05

i would like to be able to combine the second table destinations together based on the names of the first table

so it should show

id  name             info
1 afghanistan    Afghanistan 0.22,Afghanistan-Kabul 0.22,Afghanistan-Mobile 0.20,Afghanistan-Mobile-Areeba 0.22,Afghanistan-Mobile-Etisalat 0.19,Afghanistan-Mobile-Roshan 0.21

etc

0
daz1234
Asked:
daz1234
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
select a.name , b.rate
from   countrlist  A join secondtable B
on a.name = b.destination                              
0
 
joshbulaCommented:
I have had great success using the Cross Apply example from this post:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

USE AdventureWorks
GO
 
SELECT
    CustomerID,
    SalesOrderIDs = LEFT(o.list, LEN(o.list)-1)
FROM
    Sales.Customer c
CROSS APPLY
(
        SELECT
            CONVERT(VARCHAR(12), SalesOrderID) + ',' AS [text()]
        FROM
            Sales.SalesOrderHeader s
        WHERE
            s.CustomerID = c.CustomerID
        ORDER BY
            SalesOrderID
        FOR XML PATH('')
    ) o (list)
ORDER BY
    CustomerID
0
 
mmr159Commented:
I was thinking of using a CTE but I couldn't get it to work quickly, and now I'm being asked to work... the nerve of some employers!

For people wanting to test...

if object_id('country_rate','U') IS NOT NULL
      drop table country_rate
go

create table country_rate (
      destination varchar(256),
      rate float
      )
go

insert into country_rate (destination,rate)
select 'Afghanistan',0.22
union
SELECT 'Afghanistan-Kabul',0.22
union
SELECT 'Afghanistan-Mobile',0.20
union
SELECT 'Afghanistan-Mobile-Areeba',0.22
union
SELECT 'Afghanistan-Mobile-Etisalat',0.19
union
SELECT 'Afghanistan-Mobile-Roshan',0.21
union
SELECT 'Albania',0.05
union
SELECT 'Albania-Mobile',0.20
union
SELECT 'Albania-Mobile-AMC',0.26
union
SELECT 'Albania-Mobile-Vodafone',0.13
union
SELECT 'Albania-Tirana',0.03
union
SELECT 'Algeria',0.05
union
SELECT 'Algeria-Algiers',0.05
union
SELECT 'Algeria-Mobile',0.18
union
SELECT 'Algeria-Mobile-Orascom',0.32
union
SELECT 'Algeria-Mobile-Wataniya',0.31
union
SELECT 'American Samoa',0.04
union
SELECT 'Andorra',0.02
union
SELECT 'Andorra-Mobile',0.18
union
SELECT 'Angola',0.11
union
SELECT 'Angola-Mobile',0.10
union
SELECT 'Angola-Mobile-Unitel',0.10
union
SELECT 'Anguilla',0.11
union
SELECT 'Anguilla-Mobile',0.18
union
SELECT 'Anguilla-Mobile-CW',0.19
union
SELECT 'Anguilla-Mobile-Digicel',0.18
union
SELECT 'Antarctic Territories',1.18
union
SELECT 'Antigua & Barbuda',0.16
union
SELECT 'Antigua & Barbuda-Mobile',0.13
union
SELECT 'Antilles',0.13
union
SELECT 'Antilles-Curacao',0.07
union
SELECT 'Antilles-Mobile',0.12
union
SELECT 'Antilles-Mobile-Curacao',0.11
union
SELECT 'Antilles-Mobile-Ecc',0.12
union
SELECT 'Antilles-St. Maarten',0.08
union
SELECT 'Armenia',0.09
union
SELECT 'Armenia Mobile-K-Telecom',0.14
union
SELECT 'Armenia-Karabakh',0.19
union
SELECT 'Armenia-Mobile',0.15
union
SELECT 'Aruba',0.09
union
SELECT 'Aruba-Mobile',0.19
union
SELECT 'Aruba-Mobile-Digicell',0.21
union
SELECT 'Aruba-Mobile-MIO',0.20
union
SELECT 'Ascension Island',1.59
union
SELECT 'Australia',0.02
union
SELECT 'Australia-Adelaide/Perth',0.01
union
SELECT 'Australia-Cities',0.01
union
SELECT 'Australia-Mobile',0.10
union
SELECT 'Australia-Mobile-Optus',0.09
union
SELECT 'Australia-MobileSat',0.10
union
SELECT 'Australia-Mobile-Telstra',0.09
union
SELECT 'Australia-Mobile-Vodafone',0.10
union
SELECT 'Australia-NGN',0.03
union
SELECT 'Austria',0.02
union
SELECT 'Austria-Mobile',0.30
union
SELECT 'Austria-Mobile-Hutchinson',0.04
union
SELECT 'Austria-Mobile-Mobilcom',0.05
union
SELECT 'Austria-Mobile-Orange',0.04
union
SELECT 'Austria-Mobile-T Mobile',0.05
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mmr159Commented:
Oops... forgot the country list


if object_id('country_list','U') IS NOT NULL
      drop table country_list
go

create table country_list (
      id int identity(1,1),
      name varchar(256)
      )
go

insert into country_list (name)
select 'Afghanistan'
union
select 'Albania'
union
select 'Andorra'
union
select 'Austria'
go
0
 
daz1234Author Commented:
hi joshbula

how can i apply your solution
0
 
joshbulaCommented:
SELECT
    ID, name, destination = LEFT(o.list, LEN(o.list)-1)
FROM
    countrlist  
CROSS APPLY
(
        SELECT
            destination + '-' + rate +',' AS [text()]
        FROM
            destinationTable
        WHERE
         countrlist.ID = destinationTable.CountryID      ///(or whatever the foreign key is that you can join it to the country list table.  If you don't have one, you might have to use LIKE )
        ORDER BY
            destination
        FOR XML PATH('')
    ) o (list)
ORDER BY
    ID
0
 
daz1234Author Commented:
cross apply is only available on sql 2005 i'm using an earlier sql version it there any other way to write this script

0
 
joshbulaCommented:
SqlServerCentral just did an great article on this in their newsletter this morning.

http://www.sqlservercentral.com/articles/T-SQL/67973/

You'll have to adapt it to your tables, but it might be a good place to start to understand the concept.
0
 
daz1234Author Commented:
thanks had to upgrade to sql 2008 and worked like a dream
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now