April 8, 2005 at 8:23 am
| MainItemTable (Table1) | ||
| MainItems | Quantity | |
| Item 1 | 100 | |
| Item 2 | 200 | |
| Item 3 | 300 | |
| Item 4 | 400 | |
| Item 5 | 500 | |
| SubstituteItemTable (Table2) | ||
| MainItems | SubstituteItems | Quantity |
| Item 1 | Substitute Item 1 | 110 |
| Item 1 | Substitute Item 2 | 120 |
| Item 2 | Substitute Item 3 | 210 |
| Item 2 | Substitute Item 4 | 220 |
| Item 3 | Substitute Item 5 | 310 |
| This is the way I want to display data. Please help |
| |
| MainItems | SubstituteItems | Quantity |
| Item 1 | 100 | |
| Item 1 | Substitute Item 1 | 110 |
| Item 1 | Substitute Item 2 | 120 |
| Item 2 | 200 | |
| Item 2 | Substitute Item 3 | 210 |
| Item 2 | Substitute Item 4 | 220 |
| Item 3 | 300 | |
| Item 3 | Substitute Item 5 | 310 |
| Item 4 | 400 | |
| Item 5 | 500 | |
April 8, 2005 at 8:41 am
Try using a cross join in your query
April 8, 2005 at 8:51 am
This is a simple UNION:
Select MainItems, '' As SubstituteItems, Quantity
From MainItemTable
Union
Select SubstituteItemTable, SubstituteItems, Quantity
From SubstituteItemTable
Order by 1, 2
April 8, 2005 at 8:55 am
CAN i CREATE A VIEW USING ORDER BY CLAUSE
April 8, 2005 at 9:03 am
Try this
SELECT MI.MainItems, '' AS Substitute, MI.Quantity
FROM MainItemsTable MI
UNION
SELECT MI.MainItems, SI.SubstituteItems AS Substitute, SI.Quantity
FROM MainItemsTable AS MI
INNER JOIN SubstituteItemsTable AS SI ON MI.MainItems = SI.MainItems
ORDER BY MainItems, Substitute
Patrick Duflot
April 8, 2005 at 9:13 am
Why do you need a join ?
April 8, 2005 at 9:45 am
To answer an above question, no you cannot use an ordery by clause IN a view...but ON a view you can.
Additionally, I think this is pretty straight forward.
Unions listed should work fine.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply