Solved

SQL Query one-to-many-to-many

Posted on 2013-11-21
12
220 Views
Last Modified: 2013-12-02
(Using MS SQL 2008 R2 or greater)

Need help making a view that gets the results from several tables.
(considering stored proc as an alternative, but prefer view)

"solc" table can have multiple rows that relate to each "sol" table row
"sol" table can have multiple rows that relate to each "so" table row
"so" table has one row per TranNo field which is what is used in the WHERE to get the results.  

See attached image with tables and desired results.
(SELECT ... WHERE TranNo = '00000123')

Please note: I will be out of office for a week so I won't be able to look at any solutions for several days after creating this.

Edit: oops I forgot put in the example that the "sol" table does have a "SOKey" field to relate to the so.SOKey  (so.SOKey = sol.SOKey)
sql-comp-query.png
0
Comment
Question by:NCIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39667741
are there really 2 [width] columns in the same table? (solx)

any chance of getting sample data in a re-usable format?
0
 
LVL 25

Expert Comment

by:chaau
ID: 39667743
before you go on holidays can you describe how SOlineKey from sol table is linked to to the SOKey of the so table

EDIT: found your answer
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39667751
I did the typing - not my favorite pastime
CREATE TABLE SO
	([SOKey] int, [TranNo] varchar(20))
;
	
INSERT INTO SO
	([SOKey], [TranNo])
VALUES
	(150, '00000123')
;

CREATE TABLE SOL
	([SOLineKey] int, [SOLineNo] int, [Qty] int, [ItemKey] int)
;
	
INSERT INTO SOL
	([SOLineKey], [SOLineNo], [Qty], [ItemKey])
VALUES
	(250, 1, 5, 22),
	(251, 2, 6, 23),
	(252, 3, 7, 24)
;

CREATE TABLE SOLX
	([SOLineKey] int, [Width1] int, [Width2] int)
;
	
INSERT INTO SOLX
	([SOLineKey], [Width1], [Width2])
VALUES
	(250, 12, 12),
	(251, 12, 12),
	(252, 12, 12)
;

CREATE TABLE SOLC
	([SOLineKey] int, [ComplItemQtym] int, [ComplItemKey] int)
;
	
INSERT INTO SOLC
	([SOLineKey], [ComplItemQtym], [ComplItemKey])
VALUES
	(250, 10, 26),
	(250, 11, 27),
	(251, 12, 25),
	(251, 13, 26),
	(251, 14, 27),
	(251, 15, 28),
	(252, 16, 23),
	(252, 17, 25),
	(252, 18, 26)
;

CREATE TABLE ITEM
	([ItemKey] int, [ItemID] varchar(5))
;
	
INSERT INTO ITEM
	([ItemKey], [ItemID])
VALUES
	(22, 'ABC22'),
	(23, 'ABC23'),
	(24, 'ABC24'),
	(25, 'ABC25'),
	(26, 'ABC26'),
	(27, 'ABC27'),
	(28, 'ABC28')
;

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 25

Expert Comment

by:chaau
ID: 39667764
This statement will produce the desired result. You can use it in a view:
SELECT
  so.TranNo,
  sol.SOLineNO,
item.itemID,
solx.width,
solx.width1,
sol.Qty,
0 AS CompItemQty,
'' AS CompItemID
FROM so INNER JOIN sol on so.SOKey = sol.SOKey
INNER JOIN item ON sol.ItemKey = item.itemKey
INNER JOIN solx ON sol.solinekey = solx.solinekey
UNION ALL
SELECT
  so.TranNo,
  sol.SOLineNO,
item.itemID,
NULL AS width,
NULL AS width1,
NULL AS Qty,
solc.CompItemQty,
ic.itemID AS CompItemID
FROM so INNER JOIN sol on so.SOKey = sol.SOKey
INNER JOIN item ON sol.ItemKey = item.itemKey
INNER JOIN solc ON sol.solinekey = solc.solinekey
INNER JOIN item ic ON solc.CompItemKey = ic.itemKey
ORDER BY 1, 2, 7, 8

Open in new window

0
 
LVL 1

Author Comment

by:NCIT
ID: 39667771
are there really 2 [width] columns in the same table? (solx)

any chance of getting sample data in a re-usable format?

OH, sorry yea one field would be solx.Width and the other solx.Height.

I did the typing - not my favorite pastime

Thank you for doing that... I will start doing that along with the image.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39667772
Sorry PortletPaul, while you were doing typing for the schema, I was typing the query
0
 
LVL 25

Accepted Solution

by:
chaau earned 350 total points
ID: 39667773
This one is with height:
SELECT
  so.TranNo,
  sol.SOLineNO,
item.itemID,
solx.width,
solx.height,
sol.Qty,
0 AS CompItemQty,
'' AS CompItemID
FROM so INNER JOIN sol on so.SOKey = sol.SOKey
INNER JOIN item ON sol.ItemKey = item.itemKey
INNER JOIN solx ON sol.solinekey = solx.solinekey
UNION ALL
SELECT
  so.TranNo,
  sol.SOLineNO,
item.itemID,
NULL AS width,
NULL AS width1,
NULL AS Qty,
solc.CompItemQty,
ic.itemID AS CompItemID
FROM so INNER JOIN sol on so.SOKey = sol.SOKey
INNER JOIN item ON sol.ItemKey = item.itemKey
INNER JOIN solc ON sol.solinekey = solc.solinekey
INNER JOIN item ic ON solc.CompItemKey = ic.itemKey
ORDER BY 1, 2, 7, 8

Open in new window

0
 
LVL 25

Expert Comment

by:chaau
ID: 39667775
BTW, I think your image contains the wrong ItemID. I think the proper one should be:
  TRANNO  SOLINENO  ITEMID   WIDTH  WIDTH1     QTY  COMPITEMQTY  COMPITEMID 
00000123         1   ABC22      12      12       5            0             
00000123         1   ABC22                                   10       ABC26 
00000123         1   ABC22                                   11       ABC27 
00000123         2   ABC23      12      12       6            0             
00000123         2   ABC23                                   12       ABC25 
00000123         2   ABC23                                   13       ABC26 
00000123         2   ABC23                                   14       ABC27 
00000123         2   ABC23                                   15       ABC28 
00000123         3   ABC24      12      12       7            0             
00000123         3   ABC24                                   16       ABC23 
00000123         3   ABC24                                   17       ABC25 
00000123         3   ABC24                                   18       ABC26 

Open in new window

0
 
LVL 1

Author Comment

by:NCIT
ID: 39667795
chaau - this looks to be it. Super Quick work!
I considered approaching a union, was thinking different kind of joins or something, but a union like this should do it it appears.
I will try to test it out when I get a chance back in office.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39667800
just for the record:

CREATE VIEW bizarro
AS
        SELECT
                so.TranNo
              , sol.SOLineKey
              , item.ItemID
              , solx.Width1
              , solx.Width2
              , sol.Qty
              , null AS ComplItemQtym
              , null AS ComplItemID
        FROM SO
        INNER JOIN SOL ON so.SOKey = sol.SOKey
        INNER JOIN SOLX ON sol.SOLineKey = solx.SOLineKey
        INNER JOIN ITEM ON sol.ItemKey = item.ItemKey
            
        UNION ALL

        SELECT
                so.TranNo
              , sol.SOLineKey
              , i2.ItemID
              , null AS Width1
              , null AS Width2
              , null AS Qty
              , solc.ComplItemQtym
              , i2.ItemID AS ComplItemID
        FROM SO
        INNER JOIN SOL ON so.SOKey = sol.SOKey
        INNER JOIN SOLC ON sol.SOLineKey = solc.SOLineKey
        INNER JOIN ITEM AS I2 ON solc.ComplItemKey = i2.ItemKey
;
            

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 150 total points
ID: 39667866
The ItemID can be matched to the image:
|   TRANNO | SOLINEKEY | ITEMID |  WIDTH | HEIGHT |    QTY | COMPLITEMQTYM | COMPLITEMID |
|----------|-----------|--------|--------|--------|--------|---------------|-------------|
| 00000123 |       250 |  ABC22 |     12 |     12 |      5 |        (null) |      (null) |
| 00000123 |       250 |  ABC26 | (null) | (null) | (null) |            10 |       ABC26 |
| 00000123 |       250 |  ABC27 | (null) | (null) | (null) |            11 |       ABC27 |
| 00000123 |       251 |  ABC23 |     12 |     12 |      6 |        (null) |      (null) |
| 00000123 |       251 |  ABC25 | (null) | (null) | (null) |            12 |       ABC25 |
| 00000123 |       251 |  ABC26 | (null) | (null) | (null) |            13 |       ABC26 |
| 00000123 |       251 |  ABC27 | (null) | (null) | (null) |            14 |       ABC27 |
| 00000123 |       251 |  ABC28 | (null) | (null) | (null) |            15 |       ABC28 |
| 00000123 |       252 |  ABC23 | (null) | (null) | (null) |            16 |       ABC23 |
| 00000123 |       252 |  ABC24 |     12 |     12 |      7 |        (null) |      (null) |
| 00000123 |       252 |  ABC25 | (null) | (null) | (null) |            17 |       ABC25 |
| 00000123 |       252 |  ABC26 | (null) | (null) | (null) |            18 |       ABC26 |

CREATE VIEW bizarro
AS
        SELECT
                so.TranNo
              , sol.SOLineKey
              , item.ItemID
              , solx.Width
              , solx.Height
              , sol.Qty
              , null AS ComplItemQtym
              , null AS ComplItemID
        FROM SO
        INNER JOIN SOL ON so.SOKey = sol.SOKey
        INNER JOIN SOLX ON sol.SOLineKey = solx.SOLineKey
        INNER JOIN ITEM ON sol.ItemKey = item.ItemKey
            
        UNION ALL

        SELECT
                so.TranNo
              , sol.SOLineKey
              , item.ItemID
              , null AS Width
              , null AS Height
              , null AS Qty
              , solc.ComplItemQtym
              , item.ItemID AS ComplItemID
        FROM SO
        INNER JOIN SOL ON so.SOKey = sol.SOKey
        INNER JOIN SOLC ON sol.SOLineKey = solc.SOLineKey
        INNER JOIN ITEM ON solc.ComplItemKey = item.ItemKey
;
            
SELECT
*
FROM bizarro
ORDER BY
        TranNo
      , SOLineKey
      , ItemID




CREATE TABLE SO
	([SOKey] int, [TranNo] varchar(20))
;
	
INSERT INTO SO
	([SOKey], [TranNo])
VALUES
	(150, '00000123')
;

CREATE TABLE SOL
	([SOKey] int,[SOLineKey] int, [SOLineNo] int, [Qty] int, [ItemKey] int)
;
	
INSERT INTO SOL
	([SOKey], [SOLineKey], [SOLineNo], [Qty], [ItemKey])
VALUES
	(150, 250, 1, 5, 22),
	(150, 251, 2, 6, 23),
	(150, 252, 3, 7, 24)
;

CREATE TABLE SOLX
	([SOLineKey] int, [Width] int, [Height] int)
;
	
INSERT INTO SOLX
	([SOLineKey], [Width], [Height])
VALUES
	(250, 12, 12),
	(251, 12, 12),
	(252, 12, 12)
;

CREATE TABLE SOLC
	([SOLineKey] int, [ComplItemQtym] int, [ComplItemKey] int)
;
	
INSERT INTO SOLC
	([SOLineKey], [ComplItemQtym], [ComplItemKey])
VALUES
	(250, 10, 26),
	(250, 11, 27),
	(251, 12, 25),
	(251, 13, 26),
	(251, 14, 27),
	(251, 15, 28),
	(252, 16, 23),
	(252, 17, 25),
	(252, 18, 26)
;

CREATE TABLE ITEM
	([ItemKey] int, [ItemID] varchar(5))
;
	
INSERT INTO ITEM
	([ItemKey], [ItemID])
VALUES
	(22, 'ABC22'),
	(23, 'ABC23'),
	(24, 'ABC24'),
	(25, 'ABC25'),
	(26, 'ABC26'),
	(27, 'ABC27'),
	(28, 'ABC28')
;

http://sqlfiddle.com/#!3/67e66/2

Open in new window

0
 
LVL 1

Author Closing Comment

by:NCIT
ID: 39691009
Both answers are working so I accepted both, gave a little more points to the earliest response with answer though. Thanks guys for the quick and correct work!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question