SQL SELECT from SELECT

Question

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.

I've removed the `mysql` tag since the tables are referenced via two part names and `dbo.` is the default schema in SQL Server. @sd_dracula: Next time please revise your tags before you are posting a question. Thanks. — Pred, Feb 03 '15 at 15:17

Pred · Accepted Answer · 2015-02-03T15:20:13.287

4

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.

edited Feb 03 '15 at 15:20

answered Feb 03 '15 at 15:13

Pred

8,789
3
26
46

using IN of course. could not think of that whatsoever. Thanks, that's what I was looking for. – sd_dracula Feb 03 '15 at 15:35
You are welcome, but I recommend to use `EXISTS` instead of `IN`. Please double check my comment after that part ;) – Pred Feb 03 '15 at 15:36

score 1 · Answer 2 · answered Feb 03 '15 at 15:09

1

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

answered Feb 03 '15 at 15:09

Kritner

13,557
10
46
72

I would recommend EXISTS instead of IN (eg http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance) and certainly over a IN (SELECT DISTINCT) pattern. But otherwise I agree with your answer... mine is basically the same :-) – Evan Volgas Feb 03 '15 at 15:14

score 1 · Answer 3 · edited May 23 '17 at 11:51

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

score 0 · Answer 4 · answered Feb 03 '15 at 15:08

0

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

answered Feb 03 '15 at 15:08

cf_en

1,661
1
10
18

Sorry, posted in a rush before I had finished – cf_en Feb 03 '15 at 15:09
This still isn't what the OP wants. This would return multiple values when the Names table has more than 1 name. Say the Details table has "Chris" and there are 3 rows in Names with "Chris". – Sean Lange Feb 03 '15 at 15:10
Actually it wouldn't. You'd only get duplicates if there were multiple address/phone number rows. – cf_en Feb 03 '15 at 15:11
Actually it would return duplicates because there could be multiple rows in the second table that matches the join predicate. – Sean Lange Feb 03 '15 at 15:14
... which the DISTINCT would eliminate – cf_en Feb 03 '15 at 15:15

score 0 · Answer 5 · answered Feb 03 '15 at 15:10

0

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)

answered Feb 03 '15 at 15:10

Eduard Uta

2,477
5
26
36

2

I would recommend EXISTS for this sort of thing, eg, http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance but otherwise this is a very valid way to approach it – Evan Volgas Feb 03 '15 at 15:13

SQL SELECT from SELECT

5 Answers5