none
How to use result of one query in another query?

    Question

  • Hello,

     

    I have a Data Flow.

     

    I have 2 databases.

     

    I need

     

    for example

     

    1. Get a user name from Database 1 (Table 1)

    2. Get a user name ID from Database 2 based on the user name from above. (Table 1) (it will be query using INNER JOIN)

    3. Use the user name ID result in my Data Flow.

     

    What control can I use for it and how?

    Should I use Lookup? If yes - how can I use the result from the first query?

     

    So, In the end I should have User name ID for each user name in my new table which will be in Database 2.

     

    Thanks.

    Tuesday, May 15, 2007 4:12 PM

Answers

  • Vita,

     

    If I am understanding your issue correctly, I would recommend using a LookUp object within your dataflow.

     

    The proper steps would be to create a database source (point to table or query to obtain user name from database 1) then map that to a LookUp object (point to table or query to obtain user name and user id from database 2). 

     

    *LookUp allow you to specify connections, tables, queries, etc just like sources

     

    You would specify that you want to match on user name and return the user id in the LookUp. 

     

    *Another note would be to pay attention to the error output if you wish to return rows that do not match or push them elsewhere but from the note about Inner Join I don't think this is the case.

     

    You can then use this to map to any future transformations or your destination.

     

    Hope this helps!

     

     

    Tuesday, May 15, 2007 6:08 PM

All replies

  • Hey,

     

    You could use a script component to do this in a VB.NET Script.  The script component could be use as a transformation, but it can also be used as a source or destination component.  Though this script, you could programmatically use that.  It seems like it should be possible another way, but I too am having a hard time thinking which combo of controls would do it...  that's the first thing that came to mind, especially with connecting two databases.

     

    Brian

    Tuesday, May 15, 2007 4:49 PM
  • Vita,

     

    If I am understanding your issue correctly, I would recommend using a LookUp object within your dataflow.

     

    The proper steps would be to create a database source (point to table or query to obtain user name from database 1) then map that to a LookUp object (point to table or query to obtain user name and user id from database 2). 

     

    *LookUp allow you to specify connections, tables, queries, etc just like sources

     

    You would specify that you want to match on user name and return the user id in the LookUp. 

     

    *Another note would be to pay attention to the error output if you wish to return rows that do not match or push them elsewhere but from the note about Inner Join I don't think this is the case.

     

    You can then use this to map to any future transformations or your destination.

     

    Hope this helps!

     

     

    Tuesday, May 15, 2007 6:08 PM