Solved

SQL one to many query

Posted on 2011-03-03
9
274 Views
Last Modified: 2012-06-21
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
Comment
Question by:daz1234
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35027910
select a.name , b.rate
from   countrlist  A join secondtable B
on a.name = b.destination                              
0
 
LVL 9

Expert Comment

by:joshbula
ID: 35027915
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
 
LVL 7

Expert Comment

by:mmr159
ID: 35028378
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 7

Expert Comment

by:mmr159
ID: 35028418
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
 

Author Comment

by:daz1234
ID: 35029357
hi joshbula

how can i apply your solution
0
 
LVL 9

Accepted Solution

by:
joshbula earned 500 total points
ID: 35030769
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
 

Author Comment

by:daz1234
ID: 35031195
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
 
LVL 9

Expert Comment

by:joshbula
ID: 35036037
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
 

Author Closing Comment

by:daz1234
ID: 35043032
thanks had to upgrade to sql 2008 and worked like a dream
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question