• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

SQL Query one-to-many-to-many

(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
NCIT
Asked:
NCIT
  • 5
  • 4
  • 3
2 Solutions
 
PortletPaulCommented:
are there really 2 [width] columns in the same table? (solx)

any chance of getting sample data in a re-usable format?
0
 
chaauCommented:
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
 
PortletPaulCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
chaauCommented:
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
 
NCITAuthor Commented:
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
 
chaauCommented:
Sorry PortletPaul, while you were doing typing for the schema, I was typing the query
0
 
chaauCommented:
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
 
chaauCommented:
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
 
NCITAuthor Commented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
NCITAuthor Commented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now