Solved

SQL Query one-to-many-to-many

Posted on 2013-11-21
12
212 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
  • 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 24

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
 
LVL 24

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 24

Expert Comment

by:chaau
ID: 39667772
Sorry PortletPaul, while you were doing typing for the schema, I was typing the query
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 24

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 24

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Scheduling Jobs for Execution: 4 22
Mssql SQL query 14 45
Haw to apply join on 2 tables with this scenario 4 22
Insert Salary Period that has 2 months 11 22
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

912 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now