May 1, 2016 at 6:19 pm
Good Evening All,
I'm new to the forum and apologize if this post is not in the correct place.
I'm looking for help on a report I'm building for work. To give you a little background I'm fairly new to SQL and can only run basic reports. My restrictions in the workplace do not give me access to use functions. I'm using SQL Server 2008 and our restrictions only give us access only to to build/run queries.
In the report I'm looking to build I have 2 main parts that connect. A Customer and a Site. In our program they are linked. I'm looking to run a query so I can identify where a Customer has 10 or more Site's linked to it. Any advice to a WHERE statement clause would be much appreciated.
Thank you!
May 1, 2016 at 7:04 pm
USE tempdb;
GO
CREATE TABLE Customer (
CustomerID INT IDENTITY,
FirstName VARCHAR(15) NOT NULL,
LastName VARCHAR(20) NOT NULL
CONSTRAINT pkCustomer PRIMARY KEY (CustomerID) );
CREATE TABLE CustSite (
SiteID INT IDENTITY(100,1),
SiteName VARCHAR(10) UNIQUE NOT NULL,
RelatedCustomerID INT NOT NULL
CONSTRAINT pkCustSite PRIMARY KEY (SiteID) );
GO
INSERT INTO CustSite (SiteName, RelatedCustomerID) VALUES
('Site1',1),('Site2',1),('Site3',1),('Site4',2),('Site5',2);
INSERT INTO Customer(FirstName, LastName)
VALUES ('Homer', 'Simpson'),('Moe','Syzlak'),('Monty','Burns');
SELECT FirstName
, LastName
FROM Customer c INNER JOIN CustSite s ON c.CustomerID=s.RelatedCustomerID
GROUP BY FirstName, LastName
HAVING COUNT (SiteID)>2;
May 1, 2016 at 7:09 pm
This looks like a homework assignment. If you're genuinely going to be working with this stuff, I suggest you look into how to write SQL - the basics aren't overly difficult.
To answer your question, to join those two tables you need to know which column in Customer indicates which Site each customer relates to, and then build a query like below:
SELECT CUSTOMER.CUSTOMER_NAME
, COUNT(SITE.SITE_ID) AS SITE_COUNT
FROMCUSTOMER
INNER JOIN SITE
ON CUSTOMER.SITE_ID = SITE.SITE_ID
GROUP BY CUSTOMER.CUSTOMER_NAME
HAVING COUNT(SITE.SITE_ID) >= 10
May 1, 2016 at 7:12 pm
This looks like a homework assignment. If you're genuinely going to be working with this stuff, I suggest you look into how to write SQL - the basics aren't overly difficult.
To answer your question, to join those two tables you need to know which column in Customer indicates which Site each customer relates to, and then build a query like below:
SELECT CUSTOMER.CUSTOMER_NAME
, COUNT(SITE.SITE_ID) AS SITE_COUNT
FROMCUSTOMER
INNER JOIN SITE
ON CUSTOMER.SITE_ID = SITE.SITE_ID
GROUP BY CUSTOMER.CUSTOMER_NAME
HAVING COUNT(SITE.SITE_ID) >= 10
May 3, 2016 at 8:47 am
Thank you for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply