다 대다 관계가있는 여러 테이블에서 SELECT

자, 문자열로 사용되는 [FrId]와 같은 문자 필드, 이름으로 사용되는 [address]와 같은 예약어 등과 같은 문제의 전체 세탁 목록이 있습니다.

나쁜 디자인 문제는 제쳐두 자.

먼저 빠른 테스트 환경을 만들어야합니다. 정답을 얻기 위해 제약이 필요하지 않기 때문에 외래 키를 넣지 않았습니다.

--
-- Setup test tables
--


-- Just playing
use Tempdb;
go

-- drop table
if object_id('franchise')>  0
drop table franchise;
go

-- create table
create table franchise
( 
  Id int primary key,
  FrID varchar(50),
  FirstName varchar(50),
  LastName varchar(50)
);

-- insert data
insert into franchise values
( 1, 10, 'John Q', 'TestCase'),  
( 2, 10, 'Jack Q', 'TestCase'),
( 3, 11, 'Jack Q', 'TestCase');

-- select data
select * from franchise;
go


-- drop table
if object_id('store')>  0
drop table store;
go

-- create table
create table store
( 
  Id int primary key,
  FrID varchar(50),
  StoreNumber varchar(50),
  StoreName varchar(50),
  Address varchar(50)
);

-- insert data
insert into store values
(10, 10, 22222, 'TestStore1', '123 Main street'),
(11, 10, 33333, 'TestStore2', '144 Last Street'),
(12, 10, 44444, 'TestStore2', '145 Next Street'),
(13, 11, 55555, 'Other Test', '156 Other Street');

-- select data
select * from store;
go


-- drop table
if object_id('pricing')>  0
drop table pricing;
go

-- create table
create table pricing
( 
  Id int primary key,
  StoreNumber varchar(50),
  Price1 money,
  Price2 money,
  Price3 money
);


-- insert data
insert into pricing values
(1,  22222,  19.99, 20.99 , 30.99),
(2,  33333,  19.99, 20.99 , 30.99), 
(3,  44444,  19.99, 20.99 , 30.99), 
(4,  55555,  19.95, 20.95 , 30.95);

-- select data
select * from pricing;
go

주요 문제는 프랜차이즈 테이블 FrId에 기본 키 (PK),하지 ID가 있어야한다는 것입니다. 중복되는 이유를 이해할 수 없습니다.

그러나 아래 쿼리는 그룹화하여 제거합니다. Jack Q의 가격 데이터를 변경하여 다른 기록임을 보여주었습니다.

--
-- Fixed Query - Version 1
--

select 
  f.FirstName, 
  f.LastName, 
  f.FrID, 
  s.StoreNumber, 
  s.StoreName, 
  s.Address,
  p.Price1,
  p.Price2, 
  p.Price3
from

-- Remove duplicates from francise
(
select 
  LastName,
  FirstName,
  Max(FrID) as FrID
from
  franchise
group by
  LastName,
  FirstName
) as f

join store s on f.FrID = s.FrID 
join pricing p on p.StoreNumber = s.StoreNumber;

올바른 출력은 다음과 같습니다.

내가 맞다면 중복 항목을 제거하고 기본 키를 변경하십시오.

변경 요구 사항

좋습니다. 동일한 테이블에 둘 이상의 소유자를 배치합니다.

아래는 하위 쿼리를 사용하여 소유자 목록을 하나의 문자열로 결합합니다. 또 다른 방법은 기본 소유자라는 플래그를 갖는 것입니다. 표시 이름으로 선택하십시오.

--
-- Fixed Query - Version 2
--

select 
  f.OwnersList,
  f.FrID, 
  s.StoreNumber, 
  s.StoreName, 
  s.Address,
  p.Price1,
  p.Price2, 
  p.Price3
from

-- Compose owners list
(
  select 
  FrID, 
  (
    SELECT FirstName + ' ' + LastName + ';'
    FROM franchise as inner1
    WHERE inner1.FrID = outer1.FrID
    FOR XML PATH('')
  ) as OwnersList
  from franchise as outer1
  group by FrID
) as f (FrId, OwnersList)
join store s on f.FrID = s.FrID 
join pricing p on p.StoreNumber = s.StoreNumber 

다음은 두 번째 쿼리의 출력입니다.