SQLTeam.com | Weblogs | Forums

How do properly write a TSQL query when simple join doesn't seem to work


#1

I'm trying to create a Result Set from two tables, but have not found a good way to accomplish this. If you know how to solve this, can you please share?

First table has multiple rows for every farmID (because farmID --> appID is a 1 to many)
Second table has multiple rows for every farmID (because FarmID --> Server is a 1 to many)

Using columns and data as an example

TABLE1
Env, farmID, appID, pct
E2, f1234, a4567, .005
E2, f1234, a4568, .009

TABLE2
Env, FarmID, server
E2,f1234,server1
E2,f1234,server2

Required result is:

Env,FarmID,Server,appID,pct
E2,f1234,server1,a4567,.005
E2,f1234,server1,a4568,.009
E2,f1234,server2,a4567,.005
E2,f1234,server2,a4568,.009


#2
SELECT  t2.Env,
	t2.FarmID,
	t2.server,
	t1.appID,
	t1.pct
FROM TABLE2 t2
INNER JOIN TABLE1 t1 ON t2.Env = t1.Env
	AND t2.farmID = t1.farmID

#3

Thank you!
Your solution worked exactly as I had hoped.
I'm not sure why I couldn't get to that solution tonight, might be working too late this evening/morning.
It was very kind of you to help me out.