nested select to as join - how to ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

nested select to as join - how to ?

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-13 : 00:11:57

If I have a select that reurns a table with 2 columns
col1, Col2 what is that tables's name to make a join with a real table's column ? Is this possible? { actual table query is more complex, but I get the same error as this model - Syntax error in From clause
Select TableA.colA, memTable.Col1 from TableA
TableA JOIN memTable
on TableA.colA = memTable.Col1
Select TableB.Co1, TableB.Col2 from TableB as memTable where TableB.z= 'fun'

TABLEA
ColA ColB
0 foo
1 fred

TABLEB
Col1 Col2 z
1 some fun
0

andrewcw

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 00:51:35

Select * From TableA AS TA JOIN (SELECT Col1, Col2 FROM TableB) AS TB ON TB.Col1 = TA.Col1

:) I think that's what your looking for .. Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 02:12:02

check this once


Select TableA.colA, memTable.Col1 
from TableA as TableA 
JOIN (Select Co1, Col2 from TableB  where z= 'fun')memTable 
on TableA.colA = memTable.Col1

if u want only tablea details which are in tableb then use anyone of these

select * from tablea a where exists ( select * from tableb where col1= a.cola and z='fun')

select * from tablea a where cola in ( select col1 from tableb where z='fun')

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-13 : 09:27:10

Hi Guptam & Blkr,

EACH of the SQL statements you provided with JOIN do this:
Syntax error in FROM clause

I do actually need to emd up with parts of TableB. If it is not possible to return them directly, then I will need to try some idea to bring the parts together. My actual task requires me to follow several keys up & do other manipulations on the fields once found 2 tables away - as well as some if statements & work in what would be table b's values. I have the end manipulations almost done but I cannot yet link up the parts through the foreign keys,

Any more ideas would help.

andrewcw

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-13 : 09:42:49

Why not try either of these, based on your table layout from the OP. IF you ony want certain columns, change the '*' to reflect the columns you want.

Select * From TableA as TA left JOIN TableB as TB ON TB.Col1 = TA.ColA

or

Select ta.*, tb.Col1, tb.col2 From TableA AS TA JOIN (SELECT Col1, Col2 FROM TableB) AS TB ON TB.Col1 = TA.ColA (this is Gupta's example - tweaked)

Terry

-- Procrastinate now!

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-13 : 09:59:22

tosscrosby -

Select ta.*, tb.Col1, tb.col2 From TableA AS TA JOIN (SELECT Col1, Col2 FROM TableB) AS TB ON TB.Col1 = TA.ColA

also gave Syntax error in From clause

Because going directly to tableB will give me rows that are not valid, I need a rather length Select statement to just return certain foreign keys and some of the values on that specific row. I have to be able to join on the 'memory table' from the inner select statement.

Thanks for trying to suggest a way out

andrewcw

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-13 : 10:19:20

Did you try my first suggestion - see below? What version of SQL are you running, including any service packs? Are you sure there isn't some additional rogue code on QA that you're not seeing, either up or down within the pane where you're executing your code?

Select * From TableA as TA left JOIN TableB as TB ON TB.Col1 = TA.ColA

Terry

-- Procrastinate now!

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-13 : 10:44:52

Thanks Terry,

If my table B was simple I would not need the complex select statement and could make the join directly to table B ( that does work ).
However my select statement looks more like this -

SELECT First(RID), StimR
FROM tableB
WHERE (((tableB.StimR) In (SELECT DISTINCT StimR
FROM tableB
WHERE Schema ='JUMPER')))
GROUP BY StimR;

The results of this only must be joined to TableA on the keys.
( certain primary keys RID from TableB must join with foreign Keys in table A ) to tease out the relevant keys in TableA

andrewcw

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-13 : 11:52:30

I'm guessing First(RID) is a 2005 or 2008 function? If so, I only have SQL2000 installed here and will have to bow out. Just wondering why you appear to be making it more complex than it is. Would something like below work for you? Also, "schema" is a erserved word.

SELECT distinct(StimR), First(RID)
FROM tableB
WHERE Schema = 'JUMPER'
GROUP BY StimR;

Terry

-- Procrastinate now!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 12:59:08

there's no function called first in t-sql. are you sure you're using sql server?whats the purpose of First() there? to get first record? if yes, based on what order?please keep in mind there's no concept of first or last in a sql table unless you specify order by means of order by

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-13 : 13:05:44

Hi Terry,

Excellent suggestion on cleaning up the inner select statement ! I did find that distinct must be first or I get undefined Function Distinct in expression. All of the tables in this mess are ACCESS.

If only I could get the join to work...

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 13:28:08

Why dont you post this in access forum then?

andrewcw
Posting Yak Master

133 Posts

Posted - 2009-03-13 : 13:50:55

quote:
Originally posted by visakh16

Why dont you post this in access forum then?

DO you have a link ? Essentially all SQL should work. Eventually I have to drive the Queries with C# & .NET, but we prefer to expose some of the query work in the database.

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 13:55:29

nope. syntax b/w Access SQL and SQL Server is not same, though there are some common functions. post this in access forum within sql team.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 14:30:03

Andrew as Visakh16 said .. they are different another point to note is. If you are linking SQL tables into access; the sql is evaluated at Access level not SQL Serer. Wraps the SQL and then submits it to SQL Server. Sometimes in cursors at that as I found out when I was working with my access programmer buddy.

If you want SQL Server to evaluate the SQL and note access, use Access projects. They then it is all pass threw .. Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources