• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • 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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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