CODEWITHSUNDEEP
sqlsql-server
Campbell Wray
Campbell Wray

Reputation: 75

SQL UNION - Is it possible to use a result from first SELECT in the second SELECT

I am connecting to an SQL server with multiple tables,

As an example lets assume there are 2 tables:

  1. a table (names) linking ids to products names e.g. 5, apple,12,eggs
  2. a table (prices) linking product names to prices e.g. apple, $4, eggs, $5,oranges, $12

I would like to write a select command that can link this data together, something along the lines of:

SELECT id, name 
FROM names 
UNION 
SELECT price 
FROM prices where name = **name from the first bit**

The returned result would be something like:

5, apple, $4
12, eggs, $5

Oranges wouldn't be returned as it wasn't in the names table.

For a bit of background: My initial plan was to first get the names then iterate through them making a new query for every name to get the price.

However this is going to be implemented with C# and Visual Studio won't allow a second query to be opened if there is currently on on the same connection

Changing to an approach similar to what I outlined above seems like a better way to achieve what I want (less queries, tidier, etc.) but other suggestions are welcome

Upvotes: 2

Views: 3259

Answers (2)

JohnHC
JohnHC

Reputation: 11205

This is a JOIN, not a UNION

SELECT n1.id, n1.name, p1.price
FROM names n1
INNER JOIN prices p1
ON n1.name = p1.name

Upvotes: 5

gofr1
gofr1

Reputation: 15997

UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. And you need INNER JOIN to combine records from two (or more) tables:

SELECT  n.id, 
        n.name, 
        p.price 
FROM names n
INNER JOIN prices p
    ON n.name = p.name --or id

Upvotes: 1

Related Questions