Select issues when relying on original select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select issues when relying on original select

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2013-02-26 : 13:36:45

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 22:45:11

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/

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2013-02-27 : 08:43:48

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 23:10:55

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources