Solved

i need a tsql query

Posted on 2013-05-23
2
162 Views
Last Modified: 2013-06-09
Hi, i need a query which select a value from a join (join1) and uses this value as input for a second join (join2), which is the endresult.

Join1 contains only one row, because a selection is made on a column which is key:-

SELECT     tableb.code2 as result1 FROM tablea INNER JOIN tableb
                      ON table1.code1 = tableb.code1
WHERE     (tablea.key = 12105880)

Join 2

SELECT     .... FROM tablea INNER JOIN tableb
                      ON table1.code1 = tableb.code1
WHERE     (tableb.code2 = result1)

can this be combined in one query?

thanks
0
Comment
Question by:aatjan
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 39191731
SELECT     .... FROM tablea INNER JOIN tableb
                      ON table1.code1 = tableb.code1
WHERE    tableb.code2 IN (SELECT     tableb.code2 as result1 FROM tablea INNER JOIN tableb
                      ON table1.code1 = tableb.code1
WHERE     (tablea.key = 12105880)
)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193304
alternatively leverage the inner join
SELECT 
  .... 
FROM tablea 
INNER JOIN tableb ON table1.code1 = tableb.code1
AND  tableb.code2 =  (
                        SELECT tableb.code2
                        FROM tablea 
                        INNER JOIN tableb ON table1.code1 = tableb.code1
                        WHERE (tablea.key = 12105880)
                      )

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Generate Scripts of Schema/Data with "WHERE" clause 6 73
Webservices in T-SQL 3 34
Help Required 3 108
query optimization 6 15
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question