Do I need to make a view for this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Database Design and Application Architecture

Do I need to make a view for this?

Author

Topic

ashishduh
Starting Member

1 Post

Posted - 2010-04-24 : 10:38:21

Hello I am making a database for a package tracking company and have a table set up as such:

CREATE TABLE ServiceRoute(
PRIMARY KEY (Carrier, SourceDC, DestinationDC),
FOREIGN KEY (Carrier) REFERENCES Carrier,
FOREIGN KEY (SourceDC) REFERENCES DistributionCenter,
FOREIGN KEY (DestinationDC) REFERENCES DIstributionCenter
);

This table shows all the routes each carrier company services, between two distribution centers. I need to make a query that will return all the carriers that service every distribution center.

I'm thinking of somehow combining the SourceDC and DestinationDC attributes into one attribute and then doing a "COUNT(DISTINCT )" select on them, and then comparing that to a "COUNT(DISTINCT )" select on the distribution center table to see if they are equal.

Do I need to make a view for that or what would be the best way to do it? Thanks.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-02 : 05:56:59

Try this query to get all the carriers that service every distribution center:


SELECT carrier 
  FROM Carrier AS C
 WHERE NOT EXISTS(SELECT *
                    FROM DistributionCenter AS DC
                   WHERE NOT EXISTS(SELECT *
                                      FROM ServiceRoute AS SR
                                     WHERE SR.carrier = C.carrier
                                       AND (SR.SourceDC = DC.distribution_center
                                            OR SR.DestinationDC = DC.distribution_center)))

And if you like you create a view out of this query to encapsulate the logic.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-02 : 09:35:35

quote:
Originally posted by ashishduh

Hello I am making a database for a package tracking company and have a table set up as such:

CREATE TABLE ServiceRoute(
PRIMARY KEY (Carrier, SourceDC, DestinationDC),
FOREIGN KEY (Carrier) REFERENCES Carrier,
FOREIGN KEY (SourceDC) REFERENCES DistributionCenter,
FOREIGN KEY (DestinationDC) REFERENCES DIstributionCenter
);

This table shows all the routes each carrier company services, between two distribution centers. I need to make a query that will return all the carriers that service every distribution center.

I'm thinking of somehow combining the SourceDC and DestinationDC attributes into one attribute and then doing a "COUNT(DISTINCT )" select on them, and then comparing that to a "COUNT(DISTINCT )" select on the distribution center table to see if they are equal.

Do I need to make a view for that or what would be the best way to do it? Thanks.

no need of view. you can use the same logic you posted. merge the SourceDC and DestinationDC using UNION onto a single field and GROUP ON Carrier taking distinct count of them and compare this to distinct count from distribution center table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources