Fill in missing combinations in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Fill in missing combinations in a table

Author

Topic

paulocr
Starting Member

9 Posts

Posted - 2005-11-25 : 17:07:39

Hi,

I have a very interesting challenge.

I have a table where there are different combinations of the integer values of the columns x, y, w, and z. Each of these combinations can be assigned to up four letters A, B, C and D (one per row).

For example, two possible combinations are:
1, 3, 4, 5 (in other words, x =1, y = 3, w = 4, and z = 5)
and
4, 6, 7, 8 (n other words, x =4, y = 6, w = 7, and z = 8)

These combinations can be assigned to up four letters, one per row. For example,

1,3,4,5,A
1,3,4,5,B
1,3,4,5,C
4,6,7,8,A
4,6,7,8,C

In the example above, there are five records where the first combination (1,3,4,5) has been assigned to values A, B, and C. The second combination (4,6,7,8) has been assigned to values A and C.

Goal:
to insert in the table the missing assignments to letters for each existing combination, possibly in an elegant way, with only one SQL statement (if more SQL statements are needed, it is ok, but it would be less elegant). Also, if possible, using T-SQL syntax.

In other words, the missing assignments in the example above would be:

1,3,4,5,D
4,6,7,8,B
4,6,7,8,D

Any help would be appreciated.

Thanks in advance,
Paul

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-25 : 18:51:17

Help us help you...

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Slightly bored, so I did it for you..


create table #Letter(Letter char(1) primary key)
go
create table #Combo(x int, y int, w int, z int, Letter char(1) references #Letter(Letter)
	,primary key(x,y,w,z,Letter))
go
insert #Letter (Letter) 
	select 'A' 
	UNION ALL
	select 'B' 
	UNION ALL
	select 'C' 
	UNION ALL
	select 'D' 
go
insert #Combo(x,y,w,z,Letter)
	select 1,3,4,5,'A'
	union all
	select 1,3,4,5,'B'
	union all
	select 1,3,4,5,'C'
	union all
	select 4,6,7,8,'A'
	union all
	select 4,6,7,8,'C'
go
select * from #Combo
go
select x,y,w,z,Letter 
from #Letter L
cross join (select distinct x,y,w,z from #Combo) as CI
where not exists 
	(Select 1 from #Combo C where C.x = Ci.x and C.y = CI.y and C.w=CI.w and C.z = CI.z and L.Letter = C.Letter)
go
drop table #Combo, #Letter
go

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

paulocr
Starting Member

9 Posts

Posted - 2005-11-25 : 19:53:53

Thanks a lot!

There is a next level in this issue that corresponds to the actual case I am trying to solve:
The assignment of letters to combinations depend on the attributes M and K.
All the assignments under the same M and K must be applied to all combinations under these M and K.

For example,
M1,K1,1,2,4,5,A
M1,K1,1,3,4,5,B
M1,K1,4,6,7,8,A
M1,K1,4,6,7,8,C

M2,K2,2,6,4,7,A
M2,K2,5,7,8,9,A
M2,K2,5,7,8,9,E
M2,K2,9,3,7,8,A
M2,K2,9,3,7,8,D

The missing rows would be:

- M1,K1 have combinations with assignments to A, B and C, so all combinations for M1,K1 must be assigned to each of A, B,C.
Their missing rows are then:

M1,K1,1,2,4,5,C
M1,K1,4,6,7,8,B

- M2,K2 have combinations with assignments to A, C, D and E, so all combinations for M2,K2 must be assigned to each of A, C, D and E.
Their missing rows are then:

M2,K2,2,6,4,7,C
M2,K2,2,6,4,7,D
M2,K2,2,6,4,7,E

M2,K2,5,7,8,9,C
M2,K2,5,7,8,9,D

M2,K2,9,3,7,8,C
M2,K2,9,3,7,8,E

So the missing rows are the missing rows listed above, that is,

M1,K1,1,2,4,5,C
M1,K1,4,6,7,8,B

M2,K2,2,6,4,7,C
M2,K2,2,6,4,7,D
M2,K2,2,6,4,7,E

M2,K2,5,7,8,9,C
M2,K2,5,7,8,9,D

M2,K2,9,3,7,8,C
M2,K2,9,3,7,8,E

I trust you there is an elegant solution to this case too. That would solve the issue.

Thanks in advance for all your help,
Paul

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-25 : 20:04:29

Same solution Paul...

1) Get distinct combinations (this time via 2 columns)
2) Cross join to Assignments
3) Find missing sets

And what part of "Help us Help you" didn't you understand?
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

paulocr
Starting Member

9 Posts

Posted - 2005-11-25 : 20:08:16

Sorry, I forgot to enter a complete SQL spec.
I have made the question self-contained too.

Here it goes:

create table #Letter(Letter char(1) primary key)
go
create table #KLetter(KLetter char(2) primary key)
go
create table #MLetter(MLetter char(2) primary key)
go

create table #Combo (
M char(2) references #MLetter(MLetter),
K char(2) references #KLetter(KLetter),
x int, y int, w int, z int, Letter char(1) references #Letter(Letter)
,primary key(M,K,x,y,w,z,Letter))
go

insert #Letter (Letter)
select 'A'
UNION ALL
select 'B'
UNION ALL
select 'C'
UNION ALL
select 'D'
UNION ALL
select 'E'
go

insert #KLetter (KLetter)
select 'K1'
UNION ALL
select 'K2'
go

insert #MLetter (MLetter)
select 'M1'
UNION ALL
select 'M2'
go

insert #Combo(M,K,x,y,w,z,Letter)

select 'M1','K1',1,2,4,5,'A'
union all
select 'M1','K1',1,3,4,5,'B'
union all
select 'M1','K1',4,6,7,8,'A'
union all
select 'M1','K1',4,6,7,8,'C'
union all

select 'M2','K2',2,6,4,7,'A'
union all
select 'M2','K2',5,7,8,9,'A'
union all
select 'M2','K2',5,7,8,9,'E'
union all
select 'M2','K2',9,3,7,8,'A'
union all
select 'M2','K2',9,3,7,8,'D'
go

As said before,
The assignment of letters to combinations depend on the attributes M and K.
All the assignments under the same M and K must be applied to all combinations under these M and K.

The missing rows would be:

- M1,K1 have combinations with assignments to A, B and C, so all combinations for M1,K1 must be assigned to each of A, B,C.
Their missing rows are then:

M1,K1,1,2,4,5,C
M1,K1,4,6,7,8,B

- M2,K2 have combinations with assignments to A, C, D and E, so all combinations for M2,K2 must be assigned to each of A, C, D and E.
Their missing rows are then:

M2,K2,2,6,4,7,C
M2,K2,2,6,4,7,D
M2,K2,2,6,4,7,E

M2,K2,5,7,8,9,C
M2,K2,5,7,8,9,D

M2,K2,9,3,7,8,C
M2,K2,9,3,7,8,E

So the missing rows are the missing rows listed above, that is,

M1,K1,1,2,4,5,C
M1,K1,4,6,7,8,B

M2,K2,2,6,4,7,C
M2,K2,2,6,4,7,D
M2,K2,2,6,4,7,E

M2,K2,5,7,8,9,C
M2,K2,5,7,8,9,D

M2,K2,9,3,7,8,C
M2,K2,9,3,7,8,E

I trust you there is an elegant solution to this case too. That would solve the issue.

Thanks in advance for all your help,
Paul

paulocr
Starting Member

9 Posts

Posted - 2005-11-25 : 20:16:02

DavidM,

Many thanks! As you can see, I did add the SQL spec this time.

I tried the following from what you said, but it is starting to get confusing. Could you please tell me what is missing below? It does not look much simple:

select M,K,x,y,w,z,Letter
from #Letter L
????
cross join (select distinct x,y,w,z from #Combo) as CI
????
where not exists
(Select 1 from #Combo C where C.x = Ci.x and C.y = CI.y and C.w=CI.w and C.z = CI.z,
C.M=Ci.M and C.K=Ci.K and L.Letter = C.Letter and M.MLetter = C.M and K.KLetter = C.K)
go

Thanks again,
- Paul

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-25 : 20:28:55

Glad to see you took a shot at it....

you have some bad example data by the way....


select l.m,l.k,x,y,w,z, Letter 
from (select distinct m,k,letter from #Combo) as  L
cross join (select distinct x,y,w,z,m,k from #Combo) as CI
where not exists 
(Select 1 from #Combo C where C.x = Ci.x and C.y = CI.y and C.w=CI.w and C.z = CI.z and L.Letter = C.Letter
	and c.m = ci.m and c.k = ci.k)
and l.m = ci.m and l.k = ci.k

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

paulocr
Starting Member

9 Posts

Posted - 2005-11-25 : 20:56:49

Sorry for the bad example. The results for M2,K2 in my example are not correct because they include rows with letter C. These rows should not exist.

But your SQL is both elegant and correct.

That solves my issue. It works.
Great! Thanks a lot.

If I may ask another question, which SQL book with such an advance level of queries would you recommend? All the SQL books I know don't go much far and keep the examples too simple such as "select sum(c) from d group by a". And that's all of it. Any suggestions for good, advanced and practical SQL books?

Thanks,
Paul

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-25 : 21:22:08

No problems Paul.

I am probably the last person you should ask in regards in SQL books.
My database library consists of 5 books.. All theoretical. I think SQL is mentioned in couple of them with utter disdain!

I cannot stress enough the importance of understanding the basic relational operators.

As for "advanced SQL"... stick around this site and you will see some pretty weird and wonderful stuff..

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

paulocr
Starting Member

9 Posts

Posted - 2005-11-25 : 21:32:13

This site has now certainly become one of my favorite sites, David!
Your reply was not only outstanding but almost immediate. I am truly impressed.

I think that I understand what you mean regarding books. A deep theoretical understanding of database theory is the best basis for any "advanced" work.

Would you mind letting me know at least 2 or 3 of these theoretical books in your database library, those you like best? I used to read Date's book.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-25 : 21:50:10

Chris Date is the leading author, so you are off to an excellent start.

Mandatory ---> "An introduction to Database Systems" In 8th edition at the moment
Serious stuff ---> "The Third Manifesto" (my brain stills hurts from this book)

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

paulocr
Starting Member

9 Posts

Posted - 2005-11-25 : 22:33:45

Excellent and interesting books.

Many thanks!!

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources