November 2, 2008 at 3:19 am
Hi all,
I have a bit of a complex issue that I'm hoping someone can help me with.
In a nutshell, I want 2 tables (Company & Site )joined in a view for the purpose of a search interface built on the view. There is a 1:N realtionship between the 2 tables based upon the comp_companyid primary key in the company table.
An example of the data is as follows:
--===== If the test tables already exist, drop them
IF OBJECT_ID('Test..Company','U') IS NOT NULL
DROP TABLE Company
IF OBJECT_ID('Test..Site','U') IS NOT NULL
DROP TABLE Site
--===== Create the test tables
CREATE TABLE Company
(Comp_CompanyId INT IDENTITY(1,1) --Primary Key column on real table
,Comp_Name NCHAR(60))
CREATE TABLE Site
(Site_SiteId INT IDENTITY(1,1) --Primary Key column on real table
,Site_CompanyId INT --Foreign Key on real table
,Site_Name NCHAR(50))
--===== Insert the test data into the test tables
SET IDENTITY_INSERT Company ON
INSERT INTO Company (Comp_CompanyId, Comp_Name)
SELECT 1,'Alicante Holdings' UNION ALL
SELECT 2,'Contoso Corp.' UNION ALL
SELECT 3,'The Custard Co.'
SET IDENTITY_INSERT Company OFF
SET IDENTITY_INSERT Site ON
INSERT INTO Site (Site_SiteId, Site_CompanyId, Site_Name)
SELECT 1,1,'SITE1' UNION ALL
SELECT 2,1,'SITE2' UNION ALL
SELECT 3,1,'SITE3' UNION ALL
SELECT 4,2,'SITE1' UNION ALL
SELECT 5,2,'SITE2' UNION ALL
SELECT 6,2,'SITE3' UNION ALL
SELECT 7,3,'SITE2' UNION ALL
SELECT 8,3,'SITE3'
SET IDENTITY_INSERT Site OFF
I also have a view which is basically:
CREATE VIEW vSearchListCompany AS SELECT * FROM Company
Now what I want to do is to create a view that will only return one row per company, but allow me to search for site information for example if I run:
SELECT * FROM NewView
I should get 3 results, 1 for each company. However if I were to run :
SELECT * FROM NewView WHERE Site_Name = 'LON1'
I only want 2 results, companies 1 and 2 as they are the only companies using those 2 sites.
I'm not even sure if this is possible, but any thoughts would be highly appreciated.
Regards,
Brett
**Edited to include better sample data
November 2, 2008 at 7:32 am
I think I'm understanding you correctly. I had to compromise a bit with the LIKE, as you only wanted 1 row when you selected everything (which means you can't put Site_Name in the select list), and you wanted to be able to filter. Depending on the size of your table, this could be a bit slow on selecting everything.
[font="Courier New"]CREATE VIEW Test_View
AS
SELECT DISTINCT Comp_Name,
REPLACE((SELECT DISTINCT LTRIM(RTRIM(Site_Name)) AS 'data()'
FROM Site S2
WHERE S2.Site_CompanyID = S.Site_CompanyID
FOR XML PATH('')),' ',', ') SiteList
FROM Company C
LEFT JOIN Site S ON C.Comp_CompanyID = S.Site_CompanyID
--SELECT * FROM test_view WHERE Sitelist LIKE '%SITE1%'[/font]
November 2, 2008 at 7:34 am
I'm not sure you can do what you want. A view is like a table, it has one row per company (based on other data) or it doesn't.
You could do a SELECT DISTINCT with those fields that you need to return (better than SELECT *) and if you left out SITE, you'd get one row per company, if the data supports that.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 3, 2008 at 7:52 am
I'm not sure your data accurately reflects the real tables, but on the assumption that it does, then the following should help:
--===== Create the test tables
DECLARE @Company TABLE (
Comp_CompanyId int PRIMARY KEY CLUSTERED, --Primary Key column on real table
Comp_Name nchar(60)
)
DECLARE @Site TABLE (
Site_SiteId int PRIMARY KEY CLUSTERED, --Primary Key column on real table
Site_CompanyId int, --Foreign Key on real table
Site_Name nchar(50)
)
--===== Insert the test data into the test tables
--===== I had to comment out the IDENTITY insert stuff only because
-- I have to use Table Vars for testing
--SET IDENTITY_INSERT @Company ON
INSERT INTO @Company (Comp_CompanyId, Comp_Name)
SELECT 1,'Alicante Holdings' UNION ALL
SELECT 2,'Contoso Corp.' UNION ALL
SELECT 3,'The Custard Co.'
--SET IDENTITY_INSERT @Company OFF
--SET IDENTITY_INSERT @Site ON
INSERT INTO @Site (Site_SiteId, Site_CompanyId, Site_Name)
SELECT 1,1,'SITE1' UNION ALL
SELECT 2,1,'SITE2' UNION ALL
SELECT 3,1,'SITE3' UNION ALL
SELECT 4,2,'SITE1' UNION ALL
SELECT 5,2,'SITE2' UNION ALL
SELECT 6,2,'SITE3' UNION ALL
SELECT 7,3,'SITE2' UNION ALL
SELECT 8,3,'SITE3'
--SET IDENTITY_INSERT @Site OFF
--===== The query that would fill the bill for a VIEW would be the same as the contents of this CTE
;WITH VIEW_QUERY AS (
SELECT C.Comp_Name, S.Site_Name
FROM @Site AS S INNER JOIN @Company AS C
ON S.Site_CompanyId = C.Comp_CompanyId
)
--===== Then the following would work:
SELECT Comp_Name
FROM VIEW_QUERY
WHERE Site_Name = 'SITE1'
I don't have the facility or permission to create views, so I couldn't actually include that in my code, but it should be easy enough to extract the query from the CTE and put it into a view instead. Just be aware, that your test data suggests that a given site may exist for multiple companies, and that just doesn't sound like a real-world scenario unless the site names are city names, which could then be problematic because so many city names are duplicated between 2 or more states.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
November 3, 2008 at 5:00 pm
I just have to ask... does this have to be a view? If you call a stored procedure you can build a solution that will run more efficiently in production by ignoring the locations table when you want to see everything, and doing a simple join between the two tables when you are searching on location.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply