Joining two tables..how? - 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

Joining two tables..how?

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-04-10 : 11:41:14

I have two tables

Customer and Address

Both have a unique identifier CustomerID

How can I write a SQL statement that queries the Address table for postcodes of a certain region. The script I have so fair is this

select c.CustomerID, c.FirstName + ' ' + c.LastName AS CustomerName, c.Email, count(o.OrderNumber) As NoOrders
from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
join dbo.Address a with (NOLOCK) on os.CustomerID = a.CustomerID
and c.IsRegistered = 1
and p.SKU like '%'
and a.Zip Like 'PE30%'
or a.Zip Like 'PE31%'
or a.Zip Like 'PE32%'
or a.Zip Like 'PE33%'
or a.Zip Like 'PE34%'
or a.Zip Like 'PE35%'
GROUP BY c.CustomerID, c.FirstName + ' ' + c.LastName, c.Email
order by c.CustomerID

But this pulls in all orders. I just want the customer name, email and those where the zip is beginning ith PE30 etc.

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-04-10 : 12:00:24

Now have this put its pulling in all customers, not just those with a zip beginning with PE

select c.CustomerID, c.FirstName + ' ' + c.LastName AS CustomerName, c.Email
from customer c
join dbo.Address a with (NOLOCK) on c.CustomerID = a.CustomerID
and c.IsRegistered = 1
and a.Zip Like 'PE30%'
or a.Zip Like 'PE31%'
or a.Zip Like 'PE32%'
or a.Zip Like 'PE33%'
or a.Zip Like 'PE34%'
or a.Zip Like 'PE35%'
GROUP BY c.CustomerID, c.FirstName + ' ' + c.LastName, c.Email
order by c.CustomerID

Neelmani
Starting Member

2 Posts

Posted - 2014-04-10 : 12:15:00

use join to join tables.

Neelmani

Neelmani
Starting Member

2 Posts

Posted - 2014-04-10 : 12:16:58

SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinesEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name

Neelmani

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-04-10 : 12:17:10

You probably need brackets around the ORs.
Maybe try something like:


WITH OrderCount
AS
(
	SELECT CustomerID, COUNT(DISTINCT OrderNumber) AS NoOrders
	FROM dbo.Orders_ShoppingCart
	GROUP BY CustomerID
)
SELECT C.CustomerID, C.FirstName + ' ' + C.LastName AS CustomerName, C.Email, O.NoOrders
FROM customer C
	JOIN OrderCount O
		ON C.CustomerID = O.CustomerID
WHERE C.IsRegistered = 1
	AND EXISTS
	(
		SELECT 1
		FROM dbo.[Address] A
		WHERE A.CustomerID = C.CustomerID
			AND A.Zip LIKE 'PE3[0-5]%'
			-- AND (A.Zip LIKE 'PE30%' OR A.Zip LIKE 'PE31%' OR A.Zip LIKE 'PE32%') -- etc
	)

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-04-10 : 12:23:12

Thanks IFOR!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources