SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select issues when relying on original select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

detlion1643
Yak Posting Veteran

67 Posts

Posted - 02/26/2013 :  13:36:45  Show Profile  Reply with Quote
I've done this using multiple temp tables before, but having to select every field by name out of temp tables just to make a new temp table with 1 field different is quite obnoxious. I'm wondering if there is a way to do this without temp tables?

Basically, I'm looking to select a value from 1 table based off a value from the original select. It's probably so easy however my brain must be fried or something...

SELECT f1, f2, f3... *f7*, f8 ...
FROM TableName
WHERE Criteria

*f7* = a value from another table based on f7 (SELECT name FROM Table2 WHERE Value = 'f7' AND Name = 'SomeName').
The original f7 that is used in the where clause is NOT a primary key or even foreign key. The 'f7' and 'SomeName' make the returned 'name' unique.

Edit - Adding that there is NO relationships or anything to use for Joins between TableName and Table2.

Edited by - detlion1643 on 02/26/2013 13:39:53

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/26/2013 :  22:45:11  Show Profile  Reply with Quote
you can still use join. join does not require columns to be linked by foreign key constraint or being a primary key

SELECT t1.f1, t1.f2, t1.f3... t2.*f7*, t1.f8 ...
FROM TableName t1
INNER JOIN Table2 t2
ON t2.Value = t1.f7
AND t2.Name='SoameName'
WHERE Criteria


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

detlion1643
Yak Posting Veteran

67 Posts

Posted - 02/27/2013 :  08:43:48  Show Profile  Reply with Quote
Wow, never knew you could join with "critera clauses". I've only ever done it with a single t1.key = t2.key setup. Works awesome, thank you so much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/27/2013 :  23:10:55  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000