Using result of one SQL query to find the result from another table?

user_13893588 0
Viewed 42

I have two tables: Con and CFSK Both tables have a common column: ConID

Con has the following columns (atleast the ones I care about: ConID, Firstname, Lastname, SSN, DOB, HDate) CFSK has the following columns: Keyval, ConID, SystemID, Active, StChDt

Usually I do this:

SELECT * 
  FROM Con
 WHERE Firstname = 'Jon' 
   and Lastname = 'Snow'

After I run this, I copy the value for ConID (let's say it is 99999) then run it against the CFSK table:

SELECT * 
  FROM CFSK
 WHERE ConID IN (99999)

I would like to have a SQL script which consolidates both of these commands.

So let's say for example, I want to find what entries Arya Stark has in the CFSK table. I would like a script where the user has to enter only the Firstname, Lastname and it shows the columns for BOTH tables that I have mentioned above.

Any idea? Sorry I'm a noob in SQL, tried a couple noobie tricks, none worked. :( Any help would be greatly appreciated.

THANK YOU GUYS FOR YOUR KIND ANSWERS! Solved my problem, all of them are good answers! :) You guys are beautiful!

sql-server sql
3 Answers

You join the two tables together:

SELECT Con.*, CFSK.*
FROM Con 
JOIN CFSK ON Con.ConID = CFSK.ConID
WHERE
    FirstName = 'Jon'
AND LastName = 'Snow'
user_3791810

Use subquery

SELECT * FROM CFSK WHERE ConID in 

(SELECT ConID FROM Con WHERE Firstname = 'Jon' and Lastname = 'Snow')
user_51082200

IN or EXIESTS is a typically approach:

SELECT *
FROM CFSK
WHERE EXISTS (SELECT 1
              FROM CON
              WHERE Con.Firstname = 'Jon' and CON.Lastname = 'Snow' AND
                    CFSK.ConID = CON.ConID
             );

Note: This assumes that the appropriate id for comparison is CON.ConID.

I like this approach because it is suitable for an index on CON(ConID, FirrstName, LastName).

user_11440350
Related