Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Answered: Better way of joining one table to several instances of another table?

    I have a sales table that contains several different "Source Codes" (aka SourceCd) which tells me where the sales came from. Each sales record has multiple SourceCd fields (SourceCd1, SourceCd2, ... SourceCd7). There is one table that will translate that SourceCd into a friendly description (tblSourceCd). Normally when i want to translate all those codes, I have to join to 7 instances of tblSourceCd:

    Code:
    SELECT *
    FROM tblSales a
    JOIN tblSourceCd b1 ON a.SourceCd1 = b1.SourceCd
    JOIN tblSourceCd b2 ON a.SourceCd2 = b2.SourceCd
    JOIN tblSourceCd b3 ON a.SourceCd3 = b3.SourceCd
    ...
    JOIN tblSourceCd b7 ON a.SourceCd7 = b3.SourceCd
    Is there a better way of doing this?

  2. Best Answer
    Posted by Pat Phelan

    "I'd try:
    Code:
    create table #orders
    (  OrdNo        varchar(10)
    ,  SourceCdKey1 int
    ,  SourceCdKey2 int
    ,  SourceCdKey3 int
    ,  SourceCdKey4 int
    ,  SourceCdKey5 int
    ,  SourceCdKey6 int
    ,  SourceCdKey7 int
    ) 
    
    INSERT INTO #orders
       VALUES
          ('X11111',111,111,222,222,333,333,333)
    ,     ('X22222',111,222,333,444,444,444,444)
    ,     ('X33333',333,333,333,444,111,111,222)
    
    create table #SourceCd
    (  SourceCdKey  int
    ,  SourceCd     varchar(10)
    )
    
    INSERT INTO #SourceCd
       VALUES (111,'Catalog'), (222,'Phone')
    ,     (333,'Internet'), (444,'Fax')
    
    SELECT o.OrdNo, s.SourceCd
       FROM #Orders AS o
       JOIN #SourceCd AS s
          ON (s.SourceCdKey IN (o.SourceCdKey1, o.SourceCdKey2, o.SourceCdKey3
    ,        o.SourceCdKey4, o.SourceCdKey5, o.SourceCdKey6, o.SourceCdKey7))
    
    SELECT Ordno,s1.SourceCd,s2.SourceCd,s3.SourceCd,s4.SourceCd,s5.SourceCd,s6.SourceCd,s7.SourceCd
       FROM #orders a
       INNER JOIN #SourceCd s1 ON a.sourcecdkey1 = s1.sourcecdkey
       INNER JOIN #SourceCd s2 ON a.sourcecdkey2 = s2.sourcecdkey
       INNER JOIN #SourceCd s3 ON a.sourcecdkey3 = s3.sourcecdkey
       INNER JOIN #SourceCd s4 ON a.sourcecdkey4 = s4.sourcecdkey
       INNER JOIN #SourceCd s5 ON a.sourcecdkey5 = s5.sourcecdkey
       INNER JOIN #SourceCd s6 ON a.sourcecdkey6 = s6.sourcecdkey
       INNER JOIN #SourceCd s7 ON a.sourcecdkey7 = s7.sourcecdkey
    -PatP"


  3. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    yes. can you show us your table structure and a little test data
    Dave

  4. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Here is a basic structure:
    Code:
    create table #orders (
    	OrdNo varchar(10)
    	,SourceCdKey1 int
    	,SourceCdKey2 int
    	,SourceCdKey3 int
    	,SourceCdKey4 int
    	,SourceCdKey5 int
    	,SourceCdKey6 int
    	,SourceCdKey7 int) 
    
    INSERT INTO #orders
    VALUES ('X11111',111,111,222,222,333,333,333)
    ,('X22222',111,222,333,444,444,444,444)
    ,('X33333',333,333,333,444,111,111,222)
    
    create table #SourceCd (
    	SourceCdKey int
    	,SourceCd varchar(10))
    
    INSERT INTO #SourceCd
    VALUES (111,'Catalog')
    ,(222,'Phone')
    ,(333,'Internet')
    ,(444,'Fax')
    
    
    SELECT Ordno,s1.SourceCd,s2.SourceCd,s3.SourceCd,s4.SourceCd,s5.SourceCd,s6.SourceCd,s7.SourceCd
    FROM #orders a
    JOIN #SourceCd s1 ON a.sourcecdkey1 = s1.sourcecdkey
    JOIN #SourceCd s2 ON a.sourcecdkey2 = s2.sourcecdkey
    JOIN #SourceCd s3 ON a.sourcecdkey3 = s3.sourcecdkey
    JOIN #SourceCd s4 ON a.sourcecdkey4 = s4.sourcecdkey
    JOIN #SourceCd s5 ON a.sourcecdkey5 = s5.sourcecdkey
    JOIN #SourceCd s6 ON a.sourcecdkey6 = s6.sourcecdkey
    JOIN #SourceCd s7 ON a.sourcecdkey7 = s7.sourcecdkey

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try:
    Code:
    create table #orders
    (  OrdNo        varchar(10)
    ,  SourceCdKey1 int
    ,  SourceCdKey2 int
    ,  SourceCdKey3 int
    ,  SourceCdKey4 int
    ,  SourceCdKey5 int
    ,  SourceCdKey6 int
    ,  SourceCdKey7 int
    ) 
    
    INSERT INTO #orders
       VALUES
          ('X11111',111,111,222,222,333,333,333)
    ,     ('X22222',111,222,333,444,444,444,444)
    ,     ('X33333',333,333,333,444,111,111,222)
    
    create table #SourceCd
    (  SourceCdKey  int
    ,  SourceCd     varchar(10)
    )
    
    INSERT INTO #SourceCd
       VALUES (111,'Catalog'), (222,'Phone')
    ,     (333,'Internet'), (444,'Fax')
    
    SELECT o.OrdNo, s.SourceCd
       FROM #Orders AS o
       JOIN #SourceCd AS s
          ON (s.SourceCdKey IN (o.SourceCdKey1, o.SourceCdKey2, o.SourceCdKey3
    ,        o.SourceCdKey4, o.SourceCdKey5, o.SourceCdKey6, o.SourceCdKey7))
    
    SELECT Ordno,s1.SourceCd,s2.SourceCd,s3.SourceCd,s4.SourceCd,s5.SourceCd,s6.SourceCd,s7.SourceCd
       FROM #orders a
       INNER JOIN #SourceCd s1 ON a.sourcecdkey1 = s1.sourcecdkey
       INNER JOIN #SourceCd s2 ON a.sourcecdkey2 = s2.sourcecdkey
       INNER JOIN #SourceCd s3 ON a.sourcecdkey3 = s3.sourcecdkey
       INNER JOIN #SourceCd s4 ON a.sourcecdkey4 = s4.sourcecdkey
       INNER JOIN #SourceCd s5 ON a.sourcecdkey5 = s5.sourcecdkey
       INNER JOIN #SourceCd s6 ON a.sourcecdkey6 = s6.sourcecdkey
       INNER JOIN #SourceCd s7 ON a.sourcecdkey7 = s7.sourcecdkey
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Code:
    SELECT o.OrdNo, s.SourceCd
       FROM #Orders AS o
       JOIN #SourceCd AS s
          ON (s.SourceCdKey IN (o.SourceCdKey1, o.SourceCdKey2, o.SourceCdKey3
    ,        o.SourceCdKey4, o.SourceCdKey5, o.SourceCdKey6, o.SourceCdKey7))
    This does get me the data, but I would have to pivot it to get it into the same format as my way (each sourceCd has it's own column). It must be in that layout for the users, one record per order.

  7. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Will one column do, or do you need all seven columns? Something like:
    Code:
    CREATE TABLE #orders
    (  OrdNo        varchar(10)
    ,  SourceCdKey1 int
    ,  SourceCdKey2 int
    ,  SourceCdKey3 int
    ,  SourceCdKey4 int
    ,  SourceCdKey5 int
    ,  SourceCdKey6 int
    ,  SourceCdKey7 int
    ) 
    
    INSERT INTO #orders
       VALUES
          ('X11111',111,111,222,222,333,333,333)
    ,     ('X22222',111,222,333,444,444,444,444)
    ,     ('X33333',333,333,333,444,111,111,222)
    
    CREATE TABLE #SourceCd
    (  SourceCdKey  int
    ,  SourceCd     varchar(10)
    )
    
    INSERT INTO #SourceCd
       VALUES (111,'Catalog'), (222,'Phone')
    ,     (333,'Internet'), (444,'Fax')
    
    ; WITH c1 AS (
    SELECT o.OrdNo, s.SourceCd
    ,  Row_Number() OVER (PARTITION BY o.OrdNo ORDER BY s.SourceCd) AS u
    ,  Row_Number() OVER (PARTITION BY o.OrdNo ORDER BY s.SourceCd DESC) AS d
       FROM #Orders AS o
       JOIN #SourceCd AS s
          ON (s.SourceCdKey IN (o.SourceCdKey1, o.SourceCdKey2, o.SourceCdKey3
    ,        o.SourceCdKey4, o.SourceCdKey5, o.SourceCdKey6, o.SourceCdKey7))
    ), c2 AS (
    SELECT c1.*, Cast(c1.SourceCd AS VARCHAR) AS SourceList
       FROM c1
       WHERE  1 = u
    UNION ALL SELECT c1.*
    ,  Cast(SourceList + ', ' + c1.SourceCd AS VARCHAR) AS SourceList
       FROM c1
       JOIN c2
          ON c2.OrdNo = c1.OrdNo
          AND c2.u + 1 = c1.u
    )
    SELECT c2.OrdNo, c2.SourceList
       FROM c2
       WHERE  1 = d
       ORDER BY c2.OrdNo
    
    SELECT Ordno, s1.SourceCd, s2.SourceCd, s3.SourceCd
    ,  s4.SourceCd, s5.SourceCd, s6.SourceCd, s7.SourceCd
       FROM #orders a
       INNER JOIN #SourceCd s1 ON a.sourcecdkey1 = s1.sourcecdkey
       INNER JOIN #SourceCd s2 ON a.sourcecdkey2 = s2.sourcecdkey
       INNER JOIN #SourceCd s3 ON a.sourcecdkey3 = s3.sourcecdkey
       INNER JOIN #SourceCd s4 ON a.sourcecdkey4 = s4.sourcecdkey
       INNER JOIN #SourceCd s5 ON a.sourcecdkey5 = s5.sourcecdkey
       INNER JOIN #SourceCd s6 ON a.sourcecdkey6 = s6.sourcecdkey
       INNER JOIN #SourceCd s7 ON a.sourcecdkey7 = s7.sourcecdkey
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    I must have all seven, regardless of duplicates, in that order. Technically what is happening is from 1 thru 7 is showing progression of the revenue through our allocation process. I would take far too long for me to eplani it all here, hence why I am trying to keep the examples simple.

  9. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Normalization

    Is there a better way of doing this?
    Do you know what First Normal Form (1NF) means? One of the violations is the use of repeated groups. A fake array, like you have done. A table has to have a key, but your posting cannot ever have a key. Putting meta-data is a design flaw called a “tibble” (after the error of putting “tbl-” in a table name).

    Your sources are not codes; they are identifiers. A code is defined by an authority.

    CREATE TABLE Sources
    (source_id CHAR(3) NOT NULL PRIMARY KEY,
    source_desc VARCHAR(10) NOT NULL);

    Now we have a key and ISO-11170 data element names. It can be referenced by the orders table:

    CREATE TABLE Orders
    (order_nbr CHAR(10) NOT NULL,
    source_id CHAR(3) NOT NULL
    REFERENCES Sources (source_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    PRIMARY KEY (order_nbr, source_id));

    If you need no more than 7 sources, then you can enforce that with another column. This is called the Information Principle in Codd's 12 rules of RDBMS.

    CREATE TABLE Orders
    (order_nbr CHAR(10) NOT NULL,
    source_id CHAR(3) NOT NULL
    REFERENCES Sources (source_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    source_step SMALLINT DEFAULT 1 NOT NULL
    CHECK(source_step BETWEEN 1 AND 7),
    PRIMARY KEY (order_nbr, source_id, source_step));

    What you want is supposed to be done in a presentation or reporting tier and not the database layer. Look up how tiered architectures work.

  10. #9
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Ah Celko, purveyor of advice as helpful as a rusty knife to the gut and twice as unwanted. If you'd like, I'll give you the contact information to our CIO so you can express your concerns over our existing DB architecture. In the mean time, I am going to work with what I have.

  11. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Celko has what can charitably be described as "crappy delivery", but his points are academically valid. You have to take them with a small mountain of salt (usually being rubbed into the already noxious wound).

    We all have to work with what we've got... If nothing else, save Celko's post and use that as part of your justification when you need to push for a re-write to fix the underlying problems in the schema.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #11
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Your CIO will call me ..

    Ah Celko, purveyor of advice as helpful as a rusty knife to the gut and twice as unwanted. If you'd like, I'll give you the contact information to our CIO so you can express your concerns over our existing DB architecture. In the mean time, I am going to work with what I have.
    Yes, please do pass my name and credentials long to him or her. .

    I have been at this for decades, not years. I have personally watched EIGHT companies fail because they did not want to correct their existing DB architecture. In one case, I was used as expert witness at a lawsuit. My postings on a forum were part of the "prior knowledge/due diligence" (lawyer talk for: did you know or should a competent person have known that ..? ) requirement that was charged again the developers. I found that the CIO of companies with investors or legal responsibility will call me to do code reviews, because ROI really means "Risk of Incarceration" today.

  13. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Joe, we get that... You are certainly one of the brightest minds in the database field today, but the way that you present the important information makes people hostile before they ever read what you have to say. This makes you your own worst enemy because you truly are an expert in the field but many people don't care what you have to say because of your presentation.

    From a technology standpoint, you are amazing. You are my friend, and I back you sometimes when I shouldn't because you are my friend and you are technically correct. Your presentation may be your "schtick", but I don't think that it serves you well when it alienates people that you could help. In the long run, that schtick costs you followers which in turn costs you money.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •