Inner join

Transact-SQL
1

Hi need help again..
I have an item master table as below

ID Item
001 Ball
002 Jacket
003 Pants
004 Shoes

I Also have a rack table as below

RID Location
R001 Store1
R002 Store2

there's no relation between those table so I try to put the row number for both. The scenario is the item will put on the rack table. and the result will be

RID Location item
R001 Store1 Shoes
R002 Store2 Jacket

It's only show data base on the rack data. I try to use the inner join but the result show several times as much the data of item master

thx

2

Second table shoud hold FK with Rack table ID

RID Location
R001 Store1
R002 Store2

Second table (Item)
ID Item_Name RID
1 Shoes R001
2 Jacket R002

Now select items:

Select Item.ID, Item.Item_Name, Item.RID, Rack.Location
from item
inner join Rack on Rack.RID = Item.ID

Result will be:
ID Item_Name RID Location
1 Shoes R001 Store1
2 Jacket R002 Store2

3

What do you want as the result?

4

there are no key to relate both table, so as far that I try is making the row_number of the record than make them related.

5

I want the result shown as
RID Location item
R001 Store1 Shoes
R002 Store2 Jacket

only two record based on the rack table.

thx

6

Okay... What logic are you using to assign "004 Shoes" to "R001 Store1" and "002 Jacket" to "R002 Store2"?

7

I put row number for both table, and join them base on the row number. Thx

8

So no logic... Just completely arbitrary?

9

yup..thx

10 
IF OBJECT_ID('tempdb..#Master', 'U') IS NOT NULL 
DROP TABLE #Master;

CREATE TABLE #Master (
	ID CHAR(3) NOT NULL,
	Item VARCHAR(20) NOT NULL 
	);

IF OBJECT_ID('tempdb..#Rack', 'U') IS NOT NULL 
DROP TABLE #Rack;

CREATE TABLE #Rack (
	RID CHAR(4) NOT NULL,
	[Location] VARCHAR(20) NOT NULL 
	);

INSERT #Master (ID, Item) VALUES 
	('001', 'Ball'),
	('002', 'Jacket'),
	('003', 'Pants'),
	('004', 'Shoes');

INSERT #Rack (RID, [Location]) VALUES 
	('R001', 'Store1'),
	('R002', 'Store2');


--=======================================

WITH
	cte_Master AS (
		SELECT 
			*,
			RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
		FROM 
			#Master m
		),
	cte_Rack AS (
		SELECT 
			*,
			RN = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
		FROM 
			#Rack r
		)
SELECT 
	r.RID,
	r.Location,
	m.Item
FROM 
	cte_Master m
	JOIN cte_Rack r
		ON m.RN = r.RN
11

great..thx