SQLTeam.com | Weblogs | Forums

SQL query - join tables

Transact-SQL
#1

Hi,

I have 2 tables: table "clients" and table "area".

Clients table:

Client_number Client_name Client_language Area_number
1 Walter Dutch A1
2 Maya French B1
3 Tom French C1

Area table:

Area_number Area_name Area_language
A1 Moeskroen Dutch
A1 Mouscron French
B1 Meyerode (Saint Vith) French
B1 Meyerode (Sankth Vith) German
C1 Bullingen German

In the result of the query I want to display the area_name that corresponds with the client_language from the clients table. So the result of the query must look like this:

Client_number Client_name Client_language Area_number Area_name
1 Walter Dutch A1 Moeskroen
2 Maya French B1 Meyerode (Saint Vith)
3 Tom German C1 Bullingen

Someone who can help me with the query?
Thanks a lot :slight_smile:

#2

Did you give it a try? or stuck somewhere so that we can help.

#3
#4

Well, I am new to this query writing so i would join the tables like this:
select A.client_number, A.client_name, A.client_language, A.area_number, B.area_name
from
(select *
from clients) A
left join
(select *
from area) B
on A.area_number = B.Area_number

But how to incorporate the area_name selection based on the language of the client is a step to far for me at the moment :slight_smile: But I want to learn :wink:

#5

Hope this helps

SELECT 
     a.*
   , b.Area_name
FROM 
  clients a 
    JOIN 
  area b 
    ON  a.Area_number = b.Area_number

Learning how to learn is a seperate PHD topic
which is applicable in all all all things
playing music
tennis
etc etc etc etc etc etc etc etc

#6

Thanks but I don't see any selection of area_name based on the language of the client. But maybe I explained it wrong.
Client 1 (Walter) speaks Dutch (= client_language) and he lives in area A1. This Area is named Moeskroen in Dutch language and Mouscron in French (2 lines in area table for area A1).
Since Walter speaks Dutch, I want to see "Moeskroen" in the result table.

#7

How about using rownum to determine if the languages match?

Create table #Clients(
		Client_number int,
		Client_name	varchar(20),
		Client_language varchar(20), 
		Area_number varchar(10))

insert into #Clients values
(1,'Walter','Dutch','A1'),
(2,'Maya','French','B1'),
(3,'Tom','French','C1')

Create table #Area(
	Area_number	varchar(10),
	Area_name varchar(50),
	Area_language varchar(20))

insert into #Area values
('A1','Moeskroen','Dutch'),
('A1','Mouscron','French'),
('B1','Meyerode (Saint Vith)','French'),
('B1','Meyerode (Sankth Vith)','German'),
('C1','Bullingen','German')


select Client_Number, Client_Name, Client_Language, Area_number, Area_Name
  from (
		Select c.Client_Number, c.Client_Name, c.Client_Language, c.Area_number, a.Area_Name,
				row_number() over (partition by c.Area_Number order by c.Area_Number, case when c.Client_language = a.Area_language then 1 else 2 end, c.Client_language) as RowNum
		  from #Clients c
			join #Area a
				on c.Area_number = a.Area_number) x
	where x.RowNum = 1