Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is there a better way to do this? SELECTs within a SELECT Expand / Collapse
Author
Message
Posted Thursday, August 15, 2013 9:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
Hello.

I am working on a database where we have designed a generic "Addresses" table. We have many classes of records where each type might have multiple addresses.

So for example, classes of records could be Salesman. District Manager. Store. Remote Office. Etc. All fictitious but you get the idea. Each record could have one more many addresses so we came up with the universal Address table.

I am starting to write test queries to get the data out. The example below does work but I have to think there's a more efficient way to do this and would appreciate feedback.

LookUpID is the ID of the record from any given table. Could be SalesPersons. Could be Retail Location. Could be a Investor. Etc.

Eventually LookUpID will given a parameter but right now, it's hard coded just to test with.

RecordTypeID identifies what table to look in.

So in this example, the record in the Addresses Table we are testing against belongs to a SalesPerson. So the logic is, the SalesPerson (or whoever) data would be loaded into the form of the application. We already know the ID of the record whatever type it is. That's the LookUpID.


SELECT SalesPersonID, FirstName, LastName,
(SELECT Address1 FROM dbo.Addresses WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS Address1,
(SELECT Address2 FROM dbo.Addresses AS Addresses_2 WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS Address2,
(SELECT City FROM dbo.Addresses AS Addresses_3 WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS City,
...etc....
FROM dbo.SalesPersons



RecordTypes
---------------
1 - Owner
2 - District Manager
3 - SalesPerson
4- etc....


Thanks for the feedback.


Post #1484810
Posted Thursday, August 15, 2013 9:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 4,041, Visits: 9,190
A simple JOIN should work.

DECLARE @LookUpID int
SELECT sp.SalesPersonID,
sp.FirstName,
sp.LastName,
a.Address1,
a.Address2,
a.City,
...etc....
FROM dbo.SalesPersons sp
--LEFT?
JOIN dbo.Addresses a ON sp.SalesPersonID = a.LookUpID AND a.RecordTypeID = 3
WHERE sp.SalesPersonID = @LookUpID




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1484816
Posted Thursday, August 15, 2013 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
The performance would be appalling. Consider Luis' approach, or even APPLY():

SELECT SalesPersonID, FirstName, LastName,
x.Address1,
x.Address2,
x.City,
...etc....
FROM dbo.SalesPersons
OUTER APPLY (
SELECT Address1, Address2, City
FROM dbo.Addresses
WHERE (LookUpID = 1)
AND (RecordTypeID = 3)
) x



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1484818
Posted Thursday, August 15, 2013 9:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
I'll give it a shot. I could tell the performance would suffer by embedding all those selects.....
Post #1484819
Posted Thursday, August 15, 2013 9:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 1,072, Visits: 3,331
This appears to be a significant design flaw.

The first question is would address be considered an entity, or is address really an attribute of Other entities? :Salesman has address" would be the verb phrase. Not "address has salesman or district manager or..". In this case, there should probably be a table to store the addresses for each entity. You could argue that this is repeating data because "123 Main St" is contained over and over, but you can also argue that it is not repeated because it is associated with many different entities.
Think of a name. Many people can have the name "Michael", but we don't normally create a table of first names, and try to create a relationship between the first name table and many other tables in a database.

The second question is, if address is indeed an entity on it's own, then lookupid and record type should probably not be in the address table.
These should be moved into a separate set of tables that resolve the many to many relationship between the address table and the rest of the tables.
So, you will have the address table, the salesman, district manager etc. etc. tables, and a separate table such as Salesman_Address that contains the primary key of the salesman, the primary key of the address, and if required, an address type lookup.

Sorry for all of the theory but I just spent the better part of this morning diagnosing, tuning and patching a really bad query based upon a really bad design that is identical to your design. This table, as well as any code that uses it, have been one of the biggest sources of deadlocks and bottlenecks in the system. I would not want to wish the work required to remove this design on my worst enemies!


Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1484829
Posted Friday, August 16, 2013 6:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
Thanks for the input Michael. I'll try to refine this design and come back in a couple hours and post the results.
Post #1485154
Posted Friday, August 16, 2013 6:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:22 AM
Points: 167, Visits: 691
In addition to the comment above, you can also have the situation that your sales person is also a customer - what do you do then? this is why you need a bridge table.

Your entities in one table with an EntityID as a unique key
Your address table with an addressID as a unique key

The bridge which would look like (e.g.)
(possibly have a surrogate key such as EntityAddressID)
EntityID
AddressID
AddressTypeID

(I would also add EffectiveDate (i.e. you may enter future addresses but the sales person, customer has not 'moved in' yet), it also allows you to keep history; add Status (Active, Inactive); etc etc)

I hope this makes sense,

B
Post #1485158
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse