**** I GOT IT ***** Ummm, well sortof (SQL)

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

Other Forums

MS Access

**** I GOT IT ***** Ummm, well sortof (SQL)

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-29 : 09:25:52

As some of you who replied to my previous posts probably are aware of my problem (other then being a little slow) the following is an outline:


tLoadList
Date      | Project | Load
10/28/03  | 10001   | 1

tA
Project | Component | Weight
10001   | 401       | 6000

tAS 
Project | Component | Date     | Load
10001   | 401       | 10/28/03 | 1
10001   | 401       | 10/28/03 | 1

tB
Project | Component | Weight
10001   | LB-1      | 8000

tBS
Project | Component | Date    | Load
10001   | LB-1      |10/28/03 | 1

Output
##################################
Date     | Project | Load | TAWEIGHT | TBWEIGTH
10/31/03 | 10001   |  1   | 12000    | 8000
##################################

Now the SQL

SELECT tLoadList.Date, tLoadList.Project, tLoadList.Load,
       SUM (tA.Weight) AS TAWEIGHT,
       SUM (tB.Weight) AS TBWEIGHT,
FROM ((((tLoadList INNER JOIN tAS
                   ON tLoadList.Date = tAS.Date
                   AND tLoadList.Project = tAS.Project
                   AND tLoadList.Load = tAS.Load)
                           INNER JOIN tA
                           ON tAS.Project = tA.Project
                           AND tAS.Component = tA.Component)
                                  INNER JOIN tBS
                                  ON tLoadList.Date = tBS.Date
                                  AND tLoadList.Project = tBS.Project
                                  AND tLoadList.Load = tBS.Load)
                                        INNER JOIN tB
                                        ON tBS.Project = tB.Project
                                        AND tBS.Component = tB.Component)
GROUP BY tLoadList.Date, tLoadList.Project, tLostList.Load

Now this solution does exactly as I want except... if there are no tA/tAS records matching the tblLoadList Criteria, the resultset is emtpy. If both tA/tAS and tB/tBS have have records matching the result set is not empty and is correct.

Any ideas? Can I use an (iif) in here anywhere?

Mike B

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-29 : 09:46:35

Alright I was wrong, even this does not work. This keeps a running total of the weight columns as they are being iterated it seems. For example, from the tables in the previous post:


tLoadList
Date      | Project | Load
10/28/03  | 10001   | 1

tA
Project | Component | Weight
10001   | 401       | 6000

tAS 
Project | Component | Date     | Load
10001   | 401       | 10/28/03 | 1
10001   | 401       | 10/28/03 | 1

tB
Project | Component | Weight
10001   | LB-1      | 8000

tBS
Project | Component | Date    | Load
10001   | LB-1      |10/28/03 | 1

The output would be


Output
##################################
Date     | Project | Load | TAWEIGHT | TBWEIGTH
10/31/03 | 10001   |  1   | 12000    | 16000
##################################

Notice, TBWEIGHT should be 8000, not 16000

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-29 : 14:11:25

Of course, the sums must gotten doubled, because in tAS table
you have 2 duplicates-rows. Why?
If you'll get rid of duplicates in your tables you'll get just what you need.

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-29 : 14:27:17

quote:
Originally posted by Stoad

Of course, the sums must gotten doubled, because in tAS table
you have 2 duplicates-rows. Why?
If you'll get rid of duplicates in your tables you'll get just what you need.

That appears to be a duplicate row but it actually is not, there are differences that have been left out for clarity. There is a Many-To-One relationship between tA and tAS. Many (401) components from tA can be "Loaded". tA is a component list and tAS is a Production/Shipping Schedule. There is one component type 401 but many 401's produced and shipped.

Mike B

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-29 : 17:10:12

OK. I see what you mean. The point is that the INNER JOIN in
the middle of the query is in fact a CROSS JOIN between two
main halves of the query, thus it produces the 'fantom'-duplicate
(where weight is 8000 and then it's doubled).

Think you should back to the UNION query with 2 dummy output
fields for weight values. Like this:

select Col1, Col2, ..., sum(Weight1), sum(Weight2)
FROM
(
select Col1, Col2, ..., w AS Weight1, 0 AS Weight2
... ...
UNION
select Col1, Col2, ..., 0 AS Weight1, w AS Weight2
... ...
) AS ...
GROUP BY Col1, Col2, ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources