Solved

SQL one to many query

Posted on 2011-03-03
9
268 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
Comment Utility
select a.name , b.rate
from   countrlist  A join secondtable B
on a.name = b.destination                              
0
 
LVL 9

Expert Comment

by:joshbula
Comment Utility
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
Comment Utility
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
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:daz1234
Comment Utility
hi joshbula

how can i apply your solution
0
 
LVL 9

Accepted Solution

by:
joshbula earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks had to upgrade to sql 2008 and worked like a dream
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now