[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

One to Many - SQL

I have a master table called MasterType of types of appliances - which contain the master id and description as follows:

TypeID - Int [Primary Key]
Desc

I have another table called AppMaster that has 10 fields:

 appType1 ... appType10  

I want to output the Desc field from MasterType for each appType[x] in AppMaster.  Do i have to do a left outer join 10 times on the AppMaster table for each appType?  Isn't there a way to utilize the MasterType table  one time to accomplish this?

I hope i've made sense here!  If not, i can calrify.

Cheers,

Gp.
0
timbersnow
Asked:
timbersnow
  • 6
  • 3
  • 3
  • +3
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello timbersnow,
>Do i have to do a left outer join 10 times on the AppMaster table for each appType?  
yes, that's the best option



Aneesh R
0
 
Patrick MatthewsCommented:
Hello timbersnow,

I'm not sure what you're trying to do with that AppMaster table; if it's supposed to be
something like the appliances a particular person/entity either has or wants to buy,
then that is an awful design.  Please elaborate.

I the meantime, to answer the question:

SELECT (SELECT m.Desc FROM MasterType m WHERE m.TypeID = a.appType1) AS Type1,
    (SELECT m.Desc FROM MasterType m WHERE m.TypeID = a.appType2) AS Type2,
    (SELECT m.Desc FROM MasterType m WHERE m.TypeID = a.appType3) AS Type3,
...
    (SELECT m.Desc FROM MasterType m WHERE m.TypeID = a.appType10) AS Type10
FROM AppMaster a

Regards,

Patrick
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to (left) join the MasterType table 10 times, once per each field. joining once will not work, as each column can have a different value.

you should consider creating a function (oracle, ms sql server) or use the dlookup function (ms access) to avoid the joins.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
aesmikeCommented:
I hate to sound like I'm lecturing but that's not the best table structure.  Mind you, you can do whatever you like but it often leads to situations like this.  

Rather than having multiple fields (Type1, Type2, type3, etc), you are better off creating another table to hold the different types that apply to the master record.  Then you can join the master, once, to this new table and then join this new table (once) to the descriptions.  

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have to agree with aesmike!

now, just to show the implementation of the function I spoke of:

CREATE FUNCTION dbo.GetMasterType(@TypeID int)
returns VARCHAR(MAX)
AS
BEGIN
  RETURN ( SELECT Desc FROM MasterType  WHERE typeid = @typeid )
END


and in your query, you would make it like this:

select t.appType1, dbo.GetMasterType(t.appType1) Type1
, t.appType2, dbo.GetMasterType(t.appType2) Type2
...
, t.appType10, dbo.GetMasterType(t.appType10) Type10
from yourtable t



0
 
timbersnowAuthor Commented:
Hey Guys ...

Thanks for the "lecturing" ... i'm totally good with that and in agreement that this IS a horrible design.

This data is being pushed to us via a local Real Estate board - and this - however horrible - is their architecture.  I really do NOT want to reinvent the wheel.  What makes matters worse is that this data is pushed to us 3 times daily via a stupid comma delimited text file ... arghhh.

Ok ... so in its CURRENT state, multiple left outer joins is the only way to go ... is that right?

Cheers,

Gp.
0
 
timbersnowAuthor Commented:
angelIII ... i'm gonna try that implementation ... quite interesting.

BRB

Gp.
0
 
aesmikeCommented:
Well, everyone knows real estate brokers make the best DBA's <g>
Lucky you :)

Good news is that since it's pushed to you in one direction, you could still transform the data on your side.  In other words, import it as is, then translate into a proper structure on your end.  
0
 
timbersnowAuthor Commented:
angelIII ... or anybody

which way, iyho, would be less costly ... FUNCTION or Joins?

Gp.
0
 
timbersnowAuthor Commented:
aesmike ...

SO ... if that were the case ... what would you suggest?

Gp.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to check the execution plan, but I think that usually, the join syntax is better up to a few joins. with 10 joins, I would prefer the function version, and that not only because of the readability.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Functions are usually slow, but depending on the no of records, it can perform well especially an inline function
0
 
timbersnowAuthor Commented:
Here's the deal though ... these dorks have Multiple MULTIPLE fields ... i.e. Equipment1-Equipment9, Exterior1-Exterior9, Disclosure1 - Disclosure9, etc.

I really need to consider aesmike's suggestion ... rethinking architecturing it.

Gp.
0
 
bamboo7431Commented:
Yes, by all means. Do the breaking of the 1 master record with 10 appliances into 10 child records with 1 appliance each once - right after you load the data from their CSV file. Otherwise, you'll need to do it every time you use this table
0
 
aesmikeCommented:
This is perfectly ok and we do this all the time when move our systems in to coexist with a legacy system.  It sounds like the data flow is one directional--it comes to you from the R.E Board and you don't send anything back

So what you do is keep a set of "intermediate" tables -- ones whose structure closely resembles the comma delimited files.  You import your text files into these tables.  Now you create the table structure you want to live with on your end.  This would contain tables that have each child records for all those appliances, etc.  You then have another level of code that converts from the intermediate table to your final structure.  It may be more work up front but it will make your life much simpler downstream.
0
 
timbersnowAuthor Commented:
Thanks guys ... this has been helpful.

Cheers,

Gp.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now