CODEWITHSUNDEEP
sql-server
sd_dracula
sd_dracula

Reputation: 3896

SQL SELECT from SELECT

I am trying to build a single select statement from two separate ones.

Basically I have a list of Names in a table which do repeat like so:

Name| Date
John 2014-11-22
John 2013-02-03
Joe  2012-12-12
Jack 2011-11-11
Bob  2010-10-01
Bob  2013-12-22

I need to do a Select distinct Name from Records which returns John, Joe, Jack, Bob.

I then want to so a Select on another table where I pass in the rows returned above.

SELECT Address, Phone From dbo.Details
WHERE Name = {Values from first SELECT query}

Having trouble with the syntax.

Upvotes: 0

Views: 1480

Answers (5)

Pred
Pred

Reputation: 9042

If you do not want to return any values from the subquery, you can use either IN or EXISTS

SELECT Address, Phone From dbo.Details
WHERE Name IN (SELECT DISTINCT Name FROM Records)

-- OR --

SELECT Address, Phone From dbo.Details D
WHERE EXISTS (SELECT 1 FROM Records R WHERE R.Name = D.Name)

(In most RDBMS the EXISTS is less resource intensive).

If you want to return values from the subquery, you should use JOIN

SELECT
  D.Address,
  D.Phone,
  R.Name -- For example
FROM
  dbo.Details D
  INNER JOIN dbo.Records R
    ON D.Name = R.Name

SIDENOTE These are sample queries, it is possible that you have to fine tune them to match your exact requirements.

Upvotes: 4

Evan Volgas
Evan Volgas

Reputation: 2911

So there are two ways you can go about doing this. One, create a temporary table and perform a join (*actually in retrospect you could also join to your second table as a subquery, or use something like a CTE if you're using SQL SERVER, but the modifications if you wanted to go that route should be pretty obvious) 

CREATE TEMPORARY TABLE my_table AS 
{your first select query};

SELECT Address, Phone From dbo.Details
INNER JOIN my_table AS mt
ON mt.name = dbo.name

Another option would be to perform an IN or EXISTS query using your select query

SELECT Address, Phone From dbo.Details
WHERE name IN (SELECT name from my_table)

Or, better yet (eg SQL Server IN vs. EXISTS Performance), 

   SELECT Address, Phone From dbo.Details
    WHERE EXISTS (SELECT * from my_table WHERE my_table.name = dbo.name)

You might have to modify the syntax slightly, depending on if you are using MySQL or SQL Server (not sure about that later, honestly). But this should get you started down the right path

Upvotes: 1

Eduard Uta
Eduard Uta

Reputation: 2617

When using a subquery that is not scalar (doesn't return only one value) in the where clause use IN and of course only one column in the subquery:

SELECT Address, Phone 
From dbo.Details
WHERE Name IN (Select Name from Table)

Upvotes: 0

Kritner
Kritner

Reputation: 13765

You can use:

SELECT Address, Phone, name
FROM   details
-- "in" is the difference from your first query, needed due to multiple values being returned by the subquery
WHERE  name in ( 
    SELECT distinct name
    FROM namesTable
)

Additionally the following should work:

SELECT d.Address, d.Phone, n.name
FROM   details d
inner  join (
    select distinct name
    from namesTable
) n on d.name = n.name

Upvotes: 1

cf_en
cf_en

Reputation: 1661

This will give you the names and their address and phone number:

SELECT DISTINCT N.Name, D.Address, D.Phone
FROM dbo.Details D INNER JOIN dbo.Names N ON D.Name = N.Name

Upvotes: 0

Related Questions