CODEWITHSUNDEEP
sqlsql-serverms-access
Jeff Cox
Jeff Cox

Reputation: 333

Using multiple columns in query for drop down list

I have a form with 3 material subforms, each with a "name" column. What I would like to do is capture these names in a query to make a dropdown list for a Usage subform rather than relying on the user to type in the names exactly as they show in the related material subtable.

The closest I've been able to get is the following SQL

SELECT    mo.OrderID, mos.SheetName, moe.ExtrusionName, mom.MiscName
FROM        dbo.tblMatOrder AS mo INNER JOIN
                     dbo.tblMatOrderExtrusion AS moe ON mo.OrderID = moe.OrderID INNER JOIN
                     dbo.tblMatOrderMisc AS mom ON mo.OrderID = mom.OrderID INNER JOIN
                     dbo.tblMatOrderSheet AS mos ON mo.OrderID = mos.OrderID

This gets results that look like this:

| ORDERID | SHEETNAME | EXTRUSIONNAME | MISCNAME |
| 123     | SHEET1    | EXT1          | MISC1    |
| 123     | SHEET2    | EXT1          | MISC1    |

What I would like to get is something like the following:

| ORDERID | MATERIALNAME |
| 123     | SHEET1       |
| 123     | SHEET2       |
| 123     | EXT1         |
| 123     | MISC1        |

But for the life of me I can't figure out the WHERE clause or how to get an aggregate function to combine the 3 "name" columns into a single column if it is even possible.

Upvotes: 1

Views: 637

Answers (4)

James Ralston
James Ralston

Reputation: 1208

You could do something like this

SELECT DISTINCT mo.OrderID, mos.SheetName AS 'MaterialName'
FROM dbo.tblMatOrder AS mo
INNER JOIN dbo.tblMatOrderSheet AS mos ON mo.OrderID = mos.OrderID
WHERE mo.OrderID = @orderId    
UNION
SELECT DISTINCT mo.OrderID, mom.MiscName AS 'MaterialName'
FROM dbo.tblMatOrder AS mo
INNER JOIN dbo.tblMatOrderMisc AS mom ON mo.OrderID = mom.OrderID
WHERE mo.OrderID = @orderId      
UNION
SELECT DISTINCT mo.OrderID, moe.ExtrusionName AS 'MaterialName'
FROM dbo.tblMatOrder AS mo
INNER JOIN dbo.tblMatOrderExtrusion AS moe ON mo.OrderID = moe.OrderID 
WHERE mo.OrderID = @orderId

Upvotes: 3

Ashu
Ashu

Reputation: 482

SELECT Distinct  OrderID,SheetName as 'MaterialName' FROM dbo.tblMatOrderSheet 
UNION 
SELECT Distinct   OrderID,ExtrusionName as 'MaterialName' FROM  dbo.tblMatOrderExtrusion
UNION 
SELECT Distinct  OrderID,MiscName as 'MaterialName' FROM dbo.tblMatOrderExtrusion

Upvotes: 0

Jeff Cox
Jeff Cox

Reputation: 333

Well, I managed to trip over a different solution with a little more googling. I couldn't get the UNION to work. As written, I got an error that the scalar @OrderID needed to be declared. With that line removed, I got an error that the Multi-part identifier "mom.MiscName" could not be bound.

What ended up working for me was saving my original query as its own view, then run a CROSS APPLY on that query.

SELECT   DISTINCT   OrderID, MatName
FROM            dbo.vueMatOrderMatNames
CROSS APPLY
(
     VALUES  ('SheetName', SheetName), ('ExtrusionName', ExtrusionName), ('MiscName', MiscName)
) c(col, MatName)

Upvotes: 0

Nagashree Hs
Nagashree Hs

Reputation: 853

If orderID is present across all tables, then it is should be simple as below

SELECT  OrderID,SheetName as 'MaterialName' FROM dbo.tblMatOrderSheet 
UNION ALL
SELECT OrderID,ExtrusionName as 'MaterialName' FROM  dbo.tblMatOrderExtrusion
UNION ALL
SELECT OrderID,MiscName as 'MaterialName' FROM dbo.tblMatOrderExtrusion

Upvotes: 1

Related Questions