?
Solved

SQL Query one-to-many-to-many

Posted on 2013-11-21
12
Medium Priority
?
232 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 49

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 49

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 1400 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 49

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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 600 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 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