October 24, 2003 at 2:17 am
Greeting, I've just started with SQL (MSSQL 2000) and got stuck at this problem.
The actual tables contains more fields and data but I've cut it down a bit
to easier grasp and get to the core of my troubles.
I have the following schema:
CREATE TABLE tdKuk (
SEQ int NOT NULL ,
KukS int NOT NULL ,
PRIMARY KEY (SEQ, KukS))
CREATE TABLE tdSei (
SEQ int NOT NULL ,
KukS int NOT NULL ,
SeRyCD smallint NOT NULL ,
SeFl smallint NOT NULL ,
PRIMARY KEY (SEQ, KukS, SeRyCD)
FOREIGN KEY (SEQ, KukS)
REFERENCES tdKuk (SEQ, KukS))
CREATE TABLE tdSit (
SEQ int NOT NULL ,
KukS int NOT NULL ,
SiRyCD smallint NOT NULL ,
SiFl smallint NOT NULL,
PRIMARY KEY (SEQ, KukS, SiRyCD)
FOREIGN KEY (SEQ, KukS)
REFERENCES tdKuk (SEQ, KukS))
I have the following sample data:
INSERT INTO tdKuk
SELECT 16, 98
UNION SELECT 17, 99
UNION SELECT 18, 100
UNION SELECT 19, 114
INSERT INTO tdSei
SELECT 16, 98, 176, 3
UNION SELECT 16, 98, 177, 1
UNION SELECT 16, 98, 191, 1
UNION SELECT 18, 100, 176, 3
UNION SELECT 18, 100, 177, 1
UNION SELECT 18, 100, 191, 1
INSERT INTO tdSit
SELECT 17, 99, 126, 3
UNION SELECT 17, 99, 127, 1
UNION SELECT 17, 99, 151, 1
UNION SELECT 18, 100, 126, 3
UNION SELECT 18, 100, 127, 1
UNION SELECT 18, 100, 151, 1
UNION SELECT 18, 100, 159, 1
What I'm trying to achieve is merging the tables into a single one with the result as slim as possible.
I suspect that it can be done with some INSERT/UPDATE statement but I just too new with SQL.
What i would like to generate is the following rowset:
SEQ KukS SeRyCD SeFl SiRyCD SiFl
----- ----- ------- ------- ------- ------
16 98 176 3 <NULL> <NULL>
16 98 177 1 <NULL> <NULL>
16 98 191 1 <NULL> <NULL>
17 99 <NULL> <NULL> 126 3
17 99 <NULL> <NULL> 127 1
17 99 <NULL> <NULL> 151 1
18 100 176 3 126 3
18 100 177 1 127 1
18 100 191 1 151 1
18 100 <NULL> <NULL> 159 1
19 114 <NULL> <NULL> <NULL> <NULL>
Thanks in advance.
synakamr
Edited by - synakamr on 10/24/2003 02:18:50 AM
October 24, 2003 at 7:07 am
This is my original attempt
SELECTa.SEQ,a.KukS,
b.SeRyCD,b.SeFl,
c.SiRyCD,c.SiFl
FROMtdKuk a
LEFT OUTER JOIN tdSei b
ON b.SEQ = a.SEQ AND b.KukS = a.KukS
LEFT OUTER JOIN tdSit c
ON c.SEQ = a.SEQ AND c.KukS = a.KukS
but is gives more rows than your example output. What are the rules for reducing the output?
Far away is close at hand in the images of elsewhere.
Anon.
October 24, 2003 at 4:01 pm
hi synakamr,
I came up with the same query as David, which made me realize your problem. David's query produces:
SEQ KukS SeRyCD SeFl SiRyCD SiFl
----------- ----------- ------ ------ ------ ------
16 98 176 3 NULL NULL
16 98 177 1 NULL NULL
16 98 191 1 NULL NULL
17 99 NULL NULL 126 3
17 99 NULL NULL 127 1
17 99 NULL NULL 151 1
18 100 176 3 126 3
18 100 176 3 127 1
18 100 176 3 151 1
18 100 176 3 159 1
18 100 177 1 126 3
18 100 177 1 127 1
18 100 177 1 151 1
18 100 177 1 159 1
18 100 191 1 126 3
18 100 191 1 127 1
18 100 191 1 151 1
18 100 191 1 159 1
19 114 NULL NULL NULL NULL
This lets you see the core of your problem - the relationship you have is essentially a Cartesian product. When you try to produce this part of the reults:
18 100 176 3 126 3
18 100 177 1 127 1
18 100 191 1 151 1
18 100 <NULL> <NULL> 159 1
there is no reason for the values in SeRyCD and SeFl to be matched with SiRyCD and SiFl in any particular order. Your joining table tdKuk doesn't have any unique values to join on th eouter tables, and doesn't actually do anything in a normal query.
I would first check that you really want what you have asked for. If so, the only solution I can think of is to build you results piece-meal, using 3 cursors to put the rows together one-by-one. Let us know if this is what you want, and we can show you how it's done.
HTH,
Jeff
October 25, 2003 at 12:21 am
The original procedure had 12 tables for spitting out a result of 39 items on each row,
I only included these 3 tables and some fictional values to get to the core of my problem.
I also included tdKuk here to show how I also wanted to also get
"19, 114, <NULL>, <NULL>, <NULL>, <NULL>"
As for your question, yes, that's what I'm asking for.
Was hoping there was a way to avoid cursors tho as the final result could be up to +10000 rows,
But if that can't be avoided could you please show me how it's done with those 3 tables I supplied,
I should be able to incorporate it in the larger picture.
Thanks again
-synakamr
October 26, 2003 at 1:00 am
OK, here's the code:
***************************************
if exists (select * from sysobjects where id = object_id(N'[dbo].[listResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[listResults]
GO
create procedure listResults
as
begin
declare @onePass int
declare @seq int, @kuks int , @countTdkuks int
declare @SeRyCD int, @SEFL int, @countTdSei int
declare @SiRyCD int, @SiFl int, @countTdSit int
select @countTdkuks = count(*) from tdKuk where Processed = 0
while @countTdkuks > 0
begin
select @onePass = 1
select @seq = SEQ, @kuks = KukS
from tdKuk
where SEQ = (select MIN(tk2.SEQ) from tdKuk tk2 where tk2.processed = 0)
print 'Point 1:' + convert(varchar(12),@seq) + ' ' + convert(varchar(12),@kuks)
--print @kuks
update tdKuk set Processed = 1 where SEQ = @seq
select @countTdkuks = count(*) from tdKuk where Processed = 0
select @countTdSei = 0, @countTdSit = 0
while (@countTdSei = 0 or @countTdSit = 0)
begin
-- table 1
select @SeRyCD = -1
select @SeRyCD = SeRyCD, @SEFL = SeFl
from tdSei
where processed = 0 and seq = @SEQ and kuks = @KukS
and SeRyCD = (select MIN(td2.SeRyCD) from tdSei td2
where td2.processed = 0 and td2.seq = @SEQ and td2.kuks = @KukS )
if @SeRyCD = -1
begin
select @countTdSei = -1
select @SeRyCD = null, @SEFL = null
end
else
begin
update tdSei set Processed = 1
where SEQ = @seq and kuks = @KukS and SeRyCD = @SeRyCD
select @onePass = 0
end
-- table 2
select @SiRyCD = -1
select @SiRyCD = SiRyCD, @SiFl = SiFl
from tdSit
where processed = 0 and seq = @SEQ and kuks = @KukS
and SiRyCD = (select MIN(td2.SiRyCD) from tdSit td2
where td2.processed = 0 and td2.seq = @SEQ and td2.kuks = @KukS )
if @SiRyCD = -1
begin
select @countTdSit = -1
select @SiRyCD = null, @SiFl = null
end
else
begin
update tdSit set Processed = 1
where SEQ = @seq and kuks = @KukS and SiRyCD = @SiRyCD
select @onePass = 0
end
-- after all your tables, insert
if (@countTdSei = 0 or @countTdSit = 0 or @onepass = 1)
begin
insert resultsTable values (@seq, @kuks,@SeRyCD,@SeFl,@SiRyCD,@SiFl)
end
end
end
end
***************************************
I also wanted to stay away from cursors when I heard how many tables you had. To make this work:
1) on each of your tables, add an extra column named "Processed" and initialize to 0.
2) Create a "resultstable" with all the columns to be inserted.
3) The code under "Table 1", "Table 2", etc. can just be duplicated for each of your other tables, then extend the while and if logic. Most of the structure should be good to go.
**** ERROR CHECK FIRST PLEASE! ****
I concentrated on a solution, not perfect, error-free code. You may want to handle how I exit the loops, etc. differently, based on your data.
Please tell me why you wanted the data in this structure - I'm curious from a design perspective.
HTH,
Jeff
October 28, 2003 at 7:24 am
OK now I understand the problem. My suggestion is that if you want to avoid cursors, I would do the following
On each table (tdSei and tdSit in this eaxmple) add a new column (eg rowid int) and increment for each record (starting at 1 for each combination of SEQ,KukS) this can be achieved by using update
DECLARE @SEQ int, @KukS int, @rowid int
SET @SEQ = 0
SET @KukS = 0
SET @rowid = 0
UPDATE tdSei
SET rowid = @rowid = (case
WHEN SEQ <> @SEQ THEN 1
WHEN KukS <> @KukS THEN 1
ELSE @rowid = 1
END)
Find the highest rowid of ALL the tables
Create a table (eg counter) with a single column (eg rowid int) and create a row for each number from 1 to highest rowid of ALL the tables found above
join all the tables together
SELECTDISTINCT
a.SEQ,a.KukS,
b.SeRyCD,b.SeFl,
c.SiRyCD,c.SiFl
FROMtdKuk a
CROSS JOIN count x
LEFT OUTER JOIN tdSei b
ON b.SEQ = a.SEQ AND b.KukS = a.KukS AND b.rowid = x.rowid
LEFT OUTER JOIN tdSit c
ON c.SEQ = a.SEQ AND c.KukS = a.KukS AND c.rowid = x.rowid
ORDER BY a.SEQ,a.KukS,x.rowid
If this needs to be often then I suggest the calculation and setting of rowid is done when data is inserted.
Alternatively you could create temporary tables and do the above but this would create overheads you may not want.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply