How to write a Select query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to write a Select query?

Author

Topic

sequins
Starting Member

3 Posts

Posted - 2009-06-19 : 07:38:51

I have 3 tables:

Table 1
CustomerID OrderID
3 6920
4 4899
5 7825

Table 2
OrderID Name
6920 Sarah
4899 Chris
7825 Jason

Table 3
CustomerID Name
1 Natalie
2 David
5 Jason

I want to get the Name given the CustomerID, something like
SELECT CustomerID, Name
FROM ?????
WHERE CustomerID IN (1, 3, 5)
I want first join Tables 1 and 2 then combine that with Table 3 before I write the above query. Any ideas how I can do this? Would I have to use a UNION?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 07:59:06

You can do something like this, if this is what you mean. If this ins't want you want, please givean example of what results you'd like to see.

Jim


SELECT  
     t1.CustomerID,t1.OrderID,t2.[Name]
FROM
    @table1 t1
INNER JOIN
   @table2 t2
ON
  t1.Orderid = t2.Orderid
UNION ALL
SELECT CustomerID ,null,[Name]
FROM
   @table3

sequins
Starting Member

3 Posts

Posted - 2009-06-19 : 08:11:43

I want the results to look like this

CustomerID Name
1 Natalie
3 Sarah
5 Jason

I only need the Names of certain Customers. The only reason I want to Join Tables 1 and 2 is becuase Table 3 does not contain all the Customers and their Names.

Also, in reality, these tables contain thousands of customers, and I will not know which customers I am looking for are in Table 3.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 08:28:50

SELECT DISTINCT customerId,[name]
FROM
(SELECT
t1.CustomerID,t2.[Name]
FROM
@table1 t1
LEFT JOIN
@table2 t2
ON
t1.Orderid = t2.Orderid

WHERE t1.CustomerID in (1,3,5)
UNION ALL
SELECT
customerID, [Name]
FROM
@table3
WHERE
customerID in (1,3,5)
) t4

sequins
Starting Member

3 Posts

Posted - 2009-06-19 : 08:45:53

That worked!

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources