September 2, 2004 at 9:30 pm
Hi,
I have 3 tables TBL_MAIN, TBL_A, TBL_B. TBL_MAIN has a primary key PRIM1 and both TBL_A and TBL_B has foreign key to TBL_MAIN.
Sample Data:
(TBL_MAIN)
PRIM1
-----
1
(TBL_A)
PRIM1 | A
-------------
1 A1
1 A2
(TBL_B)
PRIM1 | B
------------
1 B1
1 B2
1 B3
So, my problem is, I want to construct an SQL statement which will return a join results of TBL_A and TBL_B to TBL_MAIN with no duplicacies and multiplicities.
Sample Result:
PRIM1 | A | B
---------------------
1 A1 B1
1 A2 B2
1 B3
Please help.
Thanks.
September 2, 2004 at 11:55 pm
Are you looking for all the combinations -
PRIM1 | A | B
---------------------
1 A1 B1
1 A1 B2
1 A1 B3
1 A2 B1
1 A2 B2
1 A2 B3
From your post, I'm guessing not. So there is some sort of implied link between tbl_a and tbl_b that's not obvious (character 2 of A with character 2 of B? - but that doesn't follow with your B3 entry).
Cheers
Phil
September 3, 2004 at 12:52 am
Hi,
I tried :
SELECT TBL_MAIN.PRIM1, TBL_A.A, TBL_B.B
FROM TBL_MAIN, TBL_A, TBL_B
WHERE (TBL_MAIN.PRIM1=TBL_A.PRIM1 (+))
AND (TBL_MAIN.PRIM1=TBL_B.PRIM1 (+))
ORDER BY TBL_MAIN.PRIM1
But it resulted as you shown and it became a combination. I believe there is another simple addition to this statement that will give me the real result as I want. Still struggling on this.
Anyway, the entry A1, A2..etc is just example data. The primary link is on column PRIM1.
Any idea?
There has to be a way.
Thnx.
September 4, 2004 at 10:48 am
Hello,
It looks like you are actually combining records based on the row number in each sub-table. To do that, SQL needs to have the Row# explicitly defined somewhere. The query would be possible if you had the following data, where the Row# column contains the Row number of each data item within each PRIM1 key grouping:
1. Revised Sample Data - showing two primary keys:
(TBL_MAIN)
PRIM1
-----
1
2
(TBL_A)
PRIM1 | A | ROWNUM
-------------
1 A1 1
1 A2 2
2 A10 1
2 A11 2
(TBL_B)
PRIM1 | B | ROWNUM
------------
1 B1 1
1 B2 2
1 B3 3
2 B10 1
2. Revised Sample Result:
PRIM1 | A | B | ROWNUM
---------------------
1 A1 B1 1
1 A2 B2 2
1 NULL B3 3
2 A10 B10 1
2 A11 NULL 2
3. Even with this data definition the query is not so straight forward, since you need an intermediate result containing all row-numbers for each PRIM1 key from either sub-table. This query should create the intermediate result:
SELECT DISTINCT PRIM1, ROWNUM FROM
(
SELECT ALL PRIM1, ROWNUM FROM TBL_A
UNION ALL SELECT ALL PRIM1, ROWNUM FROM TBL_B
)
4. Then this query would get the desired sample results:
SELECT PR.PRIM1, TA.A, TB.B, PR.ROWNUM
FROM
(
SELECT DISTINCT PRIM1, ROWNUM FROM
(
SELECT ALL PRIM1, ROWNUM FROM TBL_A
UNION ALL SELECT ALL PRIM1, ROWNUM FROM TBL_B
)
) AS PR
TBL_A AS TA,
TBL_B AS TB
WHERE PR.PRIM1 *= TA.PRIM1 AND PR.ROWNUM *= TA.ROWNUM
AND PR.PRIM1 *= TB.PRIM1 AND PR.ROWNUM *= TB.ROWNUM
5. OK, OK -- but how do we get the ROWNUM's in each sub-table? I believe you have to use a cursor to process each sub-table, and put the results into a temp table. Here's code for TBL_A, assuming that PRIM1 and A are VARCHAR(20):
CREATE TABLE #ta_temp (
PRIM1 VARCHAR(20)
A VARCHAR(20)
ROWNUM INT
)
DECLARE @this_prim VARCHAR(20)
DECLARE @last_prim VARCHAR(20)
DECLARE @row_num INT
DECLARE @a VARCHAR(20)
SELECT @row_num = 1
SELECT @last_prim = NULL
DECLARE ta_cursor CURSOR FOR
SELECT ta.PRIM1, ta.A
FROM TBL_A ta
ORDER BY ta.PRIM1, ta.A
OPEN ta_cursor
FETCH NEXT FROM ta_cursor INTO @this_prim, @a
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @row_num =
(CASE @this_prim = @last_prim
THEN @row_num + 1
ELSE 1
END)
INSERT #ta_temp VALUES( @this_prim, @a, @row_num)
SELECT @last_prim = @this_prim
FETCH NEXT FROM ta_cursor INTO @this_prim, @a
END
CLOSE ta_cursor
DEALLOCATE ta_cursor
6. Put it all together by using the temp-tables in the query from step #4:
SELECT PR.PRIM1, TA.A, TB.B, PR.ROWNUM
FROM
(
SELECT DISTINCT PRIM1, ROWNUM FROM
(
SELECT ALL PRIM1, ROWNUM FROM #ta_temp
UNION ALL SELECT ALL PRIM1, ROWNUM FROM #tb_temp
)
) AS PR
#ta_temp AS TA,
#tb_temp AS TB
WHERE PR.PRIM1 *= TA.PRIM1 AND PR.ROWNUM *= TA.ROWNUM
AND PR.PRIM1 *= TB.PRIM1 AND PR.ROWNUM *= TB.ROWNUM
Good luck with it!
Bob Monahon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply