Query Help ???

Question

Ten Centuries

Points: 1031

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

steve alexander SSC Enthusiast Points: 108 More actions · Answer 1

SSC Enthusiast

Points: 108

April 8, 2005 at 8:41 am

Try using a cross join in your query

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

This is a simple UNION:

Select MainItems, '' As SubstituteItems, Quantity

From MainItemTable

Union

Select SubstituteItemTable, SubstituteItems, Quantity

From SubstituteItemTable

Order by 1, 2

Sanjay Masawan Ten Centuries Points: 1031 More actions · Answer 3

Ten Centuries

Points: 1031

April 8, 2005 at 8:55 am

CAN i CREATE A VIEW USING ORDER BY CLAUSE

Patrick Duflot SSChasing Mays Points: 660 More actions · Answer 4

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

SSC-Insane

Points: 20805

April 8, 2005 at 9:13 am

Why do you need a join ?

Doug Blystone Ten Centuries Points: 1144 More actions · Answer 6

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.

Cookies on SQLServerCentral