Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: Joining a table twice

    Hi

    I have tables Companies, CompaniesDetails (the company branches), Addresses and Companies_Addresses.

    The addresses table contain street and city while the Companies_Addresses has the keys for both companies and branches ,ie,
    they are linked to Companies and CompaniesDetails via CompanyID and CompanyDetailID and to Addresses via addressID.

    Companies_Addresses
    id (PK)
    companyID (FK)
    companyDetailID (FK)
    addressID (FK)

    I am able to get the branch address at the moment with this code but I would like to get the company address as well using a single select statement. Any suggestions please?


    Code:
    SELECT DISTINCT		Addresses.city as branchCity, Addresses.street as branchStreet
    										
    FROM			Companies 
    LEFT JOIN		CompaniesDetails AS cd ON companies.companyID = cd.companyID 
    
    LEFT JOIN		Companies_Addresses AS c ON c.companyDetailID = cd.companyDetailID 
    LEFT JOIN		Addresses ON c.addressID = Addresses.addressID 
    
    WHERE		        Companies.name LIKE 'abc'
    AND			Companies.status_indicator like 'Current'
    Last edited by kpeeroo; 01-26-15 at 10:49.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what would the SQL be to get the company address? By the VERY limited info you have given above. I would think the branches and the company address would all be using the same companyID. I do question the need for the join to CompaniesDetails, you already have the companyID to go straight to the Companies_Addresses table. Lastly, do you need the outer join between Companies_Addresses and Addresses? I would think that would be an inner. Same logic would appliy to Companies_Addresses if you really do need the CompaniesDetails table. In other words the one outer join then inner join those to each other.
    To answer the question in your post title, yes you can have the same table as many times as you like in a query.
    Dave

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Join the Addresses table using two aliases with different join criteria, such as Addresses_Company and Addresses_Branch and you ought to be good.

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

  4. #4
    Join Date
    Jul 2009
    Posts
    168
    Thanks for the replies.

    No, I use companyID and companyDetailID (for branch) to link to Companies_Addresses and they are treated as separate as they have different locations. Yes, I need to link up Companies_Addresses (contains only keys) to where the addresses are actually stored Addresses.

    Pat, I tried this but isnt working:

    Code:
    SELECT DISTINCT	 Addresses.city as branchCity, Addresses.street as branchStreet
    			,a1.city as companyCity, a1.street as companyStreet
    
    FROM			Companies 
    LEFT JOIN		CompaniesDetails AS cd ON companies.companyID = cd.companyID 
    
    LEFT JOIN		Companies_Addresses AS c ON c.companyDetailID = cd.companyDetailID 
    LEFT JOIN		Addresses ON c.addressID = Addresses.addressID 
    LEFT JOIN		Companies_Addresses AS cc ON cc.companyID = Companies.companyID
    LEFT JOIN		Addresses as a1 ON a1.addressID = cc.companyID
    
    WHERE		Companies.name LIKE 'abc'
    AND			Companies.status_indicator like 'Current'

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    Oh found the error:

    Addresses as a1 ON a1.addressID = cc.addressID

    instead of Addresses as a1 ON a1.addressID = cc.companyID
    Thanks for your help guys.

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

    Most of the work in SQL is in the DDL, not in query kludges

    >> I have tables Companies, Company_Branches (the company branches), Addresses and Company_A. <<

    Where is the DDL? Ever since CompuServe, the minimal Netiquette has been to post DDL, a clear spec and some sample data.

    The short answer to the question you posted is that when you give a table an alias, SQL “effectively” makes a copy under the new name that exists within a local scope. You can make more than one of these guys and use them as if they were base tables.

    “Effectively” is a standard-speak term which says that SQL has to produce the same results as the model, but has no obligation to implement it that way.

    A branch and the details of a company are totally different concepts; why did you pick an incorrect data element name? A table of addresses is fine. In fact, many industries use a SAN (Standard Address Number) as an identifier or an identifier from a delivery company like FedEx, DHL, etc. Also, there are postal standards (CASS in US) for A.

    But no valid schema would have a “Companies_Addresses” table. Do you also have “Male_Personnel” and “Female_Personnel” tables? No, of course not! This classic design flaw is called “attribute splitting” and it usually comes from mimicking a file system in SQL. The type of addressee is an attribute of an address!

    The industry standard for identifying a company is a DUNS.

    There is no generic “id” in RDBMS or SQL. The Relational Model is based on logic and we use the “Law of Identity” (has nothing to do with the Microsoft proprietary IDENTITY physical locator). It say “To be is to be something in particular; to be nothing in particular or to be anything in general is to be nothing at all” and it why ISO-11179 and data modelers want to see “<something in particular>_id” as a data element name.

    >> The addresses table contain street and city while the Companies_Addresses has the keys for both companies and branches ,ie,
    they are linked to Companies and Company_Branches via duns and company_branch_id and to Addresses via san. <<

    Can I assume that “city” would be “city_name” and that “id” is simply wrong?

    CREATE TABLE Company_Branches
    (duns CHAR(9) NOT NULL
    REFERENCES Companies(duns)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    company_branch_id CHAR(5) NOT NULL,
    san CHAR(7) NOT NULL
    REFERENCES Addresses(san)
    ON UPDATE CASCADE);

    CREATE TABLE Addresses
    (san CHAR(7) NOT NULL PRIMARY KEY,
    street_address_1 VARCHAR(35) NOT NULL,
    street_address_2 VARCHAR(35) NOT NULL,
    city_name VARCHAR(25) NOT NULL,
    state_code CHAR(2) NOT NULL,
    zip_code CHAR(5) NOT NULL);

    CREATE TABLE Companies
    (duns CHAR(9) NOT NULL PRIMARY KEY,
    .. );

    Do you need an address for each company headquarters? Them make it a branch with a special name, like 'HQ', so you can put it with the other branches. There are some DDL tricks to assure that each company has one and only one headquarters, etc.

    >> I am able to get the branch address at the moment with this code but I would like to get the company address as well using a single select statement. Any suggestions please? <<

    With the correct DDL, this is trivial! Your mindset is still back in the 1950's file system and not thinking in sets yet. The code you do post is awful. OUTER JOINs are rare because DRI guarantees a match. Why would a company not have at least the headquarters branch? You implied it has to be somewhere.

    SELECT DISTINCT is a sign of huge design and/or query failure. This removes redundant rows that are rarely if ever in correct SQL.

    Look at “.status_indicator like 'current'” and think about it. There are no wild cards! Why not use = instead of LIKE? But this shows use another design flaw; you write with assembly language bit flags. There can be a “<something>_status” in RDBMS and it needs a temporal dimension, in the form of (start_time, end_time) pairs. A status is a state of being. SQL programmers will keep VIEWs with the current status, which will have a NULL for the “end_time” instead of a DATE or timestamp. In other SQL products, materialized VIEWs can be shared, so this is a good programming practice.

    SELECT C.company_name, A.city_name,
    A.street_address_1, A.street_address_2,
    A.city_name, A.state_code, A.zip_code
    FROM Company_Branches AS CB,
    Addresses AS A,
    Companies AS C
    WHERE C.duns = CB.duns
    AND A.san = CB.san
    AND A.address_status = 'current'
    AND A.end_date IS NULL
    AND C.company_name LIKE 'abc%';

    I am not trying to hurt your feelings, I am trying to educate you. Hey, how often do you get a private lesson from one of the creators of the language you are using?

    I have to write books and teach classes, so I have to know what mistakes noobs make and (more important!) why.

  7. #7
    Join Date
    Jul 2009
    Posts
    168
    Goodness not you again! Guess you are from the 50s right? no wonder..

    Well for the sake of your classes and books, let me answer. First, i dont care about your industry standards, i have read all those from Michael Hernandez fabulous book and I know am not following all of his good rules as this is a small project.

    But no valid schema would have a “Companies_Addresses” table.
    Mate, I know this is not the real name, to make it short I changed the name for the post. It is Companies_Branches_Addresses and is a LINKING TABLE to ADDRESSES. Industry standards are best practices but the conventions of all these variables and tables names can be anything as long as they are documented properly in the Field Specifications Sheet.


    Can I assume that “city” would be “city_name” and that “id” is simply wrong?
    What do you think City means when you see it somewhere? Search it in Oxford Dictionary mate. Do you also have “Male_Personnel” and “Female_Personnel” tables? No, of course not!

    SELECT DISTINCT is a sign of huge design and/or query failure. This removes redundant rows that are rarely if ever in correct SQL.
    You are trying to say there can be no more than 2 rows sharing a business key, which world you live in mate? in the 50s!

    Look at “.status_indicator like 'current'” and think about it. There are no wild cards!
    Why the hell do I need wildcards here! Go to Kimball's Data warehouse book and see that for yourself. The Status_Indicator is a flag for "Expired" or "Current" to keep track of SCD2 type as an option to the dimensions Start_Date and End_Date.

    which will have a NULL for the “end_time” instead of a DATE or timestamp
    It can be NULL or a distinct date like '9/9/9999' and using a DATE allows a date to be placed in that field when row is deleted so its status_indicator will be 'Deleted' and we can keep track of history. All these are from Kimball.

    Why would a company not have at least the headquarters branch?
    Of course they do mate, That is why there are COMPANIES and BRANCHES tables which I named here in the post, not in my database, as companies_details. So all HQ go in COMPANIES as this is assuring ONLY ONE HQ while the branches go in the BRANCHES table with Company_ID as foreign. Some CUSTOMERS are also registered as COMPANIES and according to the business rules they are to be treated separately so that is why to link them, OUTER JOINS are being used with the LINKING table Companies_Customers_Addresses to the Addresses table.
    Last edited by kpeeroo; 01-27-15 at 02:58.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Aha --- The missing piece of information. For the life of me I couldn't figure out why your original SQL did not return the head quarters address. I had the mistaken idea that all of them should have been returned as I would have had a type to denote if it was HQ or a Branch.
    Why would a company not have at least the headquarters branch?
    Of course they do mate, That is why there are COMPANIES and BRANCHES tables which I named here in the post, not in my database, as companies_details. So all HQ go in COMPANIES as this is assuring ONLY ONE HQ while the branches go in the BRANCHES table with Company_ID as foreign. Some CUSTOMERS are also registered as COMPANIES and according to the business rules they are to be treated separately so that is why to link them, OUTER JOINS are being used with the LINKING table Companies_Customers_Addresses to the Addresses table.
    Based on this, then yes, you need to have a separate join from the details table to the address table and add the address for the HQ address to your select list.
    Dave

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    First, I don't care about your industry standards, I have read all those from Michael Hernandez fabulous book and I know am not following all of his good rules as this is a small project.
    Okay, we have established your level of professionalism.

    Mate, I know this is not the real name, to make it short I changed the name for the post. It is Companies_Branches_Addresses and is a LINKING TABLE to ADDRESSES.

    Where was thew DDL for that? The term “linking” is not RDBMS; it goes back to the old network databases. It is a pointer structure, not a relationship table. A relationship table would have DRI actions.

    [quote] What do you think City means when you see it somewhere? [\quote]

    In a database, I look for the attribute property and possible role. In COBOL, which is what you are actually writing, the field names are ALWAYS in a hierarchical record structure and therefore have a context (or a really long full name). But RDBMS uses a set model of data, so three is no local context. The data element is the same in any table, present or future, and preferably in any other table in the universe.

    You are trying to say there can be no more than 2 rows sharing a business key, which world you live in mate? in the 50s!
    Rows do not share keys. They can reference the same key in the base table where it is a proper key. Also a date or an INTERVAL is a unit of measurement for a dimension. I think you missed what a slowly changing dimension means in Ralph's system. The Calendar can be regarded as static unless the new Pope has something in mind

    It [end_date] can be NULL or a distinct date like '9/9/9999' and using a DATE allows a date to be placed in that field [sic] when row is deleted so its status_indicator will be 'Deleted' and we can keep track of history. All these are from Kimball.
    In SQL all, data types have be NULL-able. Your dummy date string is wrong in so many ways. First of all, your “all-9's” will not cast the a DATE data type. This is how we did it in COBOL in the 1950's! When we did not have temporal data types! You are doing what you accused me of! LOL! The last date in ANSI/ISO Standard SQL is '9999-12-31' and it is a real date, not a marker.

    Why would a company not have at least the headquarters branch?
    I like to get specs instead of playing 20-Questions, with DDL.

    So all HQ go in COMPANIES as this is assuring ONLY ONE HQ while the branches go in the BRANCHES table with company_id as foreign key.
    Onre way to do this with Standars SQL is simply:

    CREATE ASSERTION One_HQ_per_Company
    AS
    ((SELECT COUNT (C.duns) FROM Companies)
    =
    (SELECT COUNT (DISTINCT C.duns) FROM Branches))

    It is tempting to use an outer join and look for NULLs, but this counting trick is faster if you have the DRI in place. In T-SQL you still have to use a VIEW with a WITH CHECK OPTION. There is no need to destroy the data model by making the HQ branch into a special attribute instead of an entity.

    Some CUSTOMERS are also registered as COMPANIES and according to the business rules they are to be treated separately so that is why to link them, OUTER JOINS are being used with the LINKING table Companies_Customers_Addresses to the Addresses table.
    So there is a company_type that has 'customer” as one of the options? Instead of a kludge, create A view and then use his DUNS to look him up as a company.

Tags for this Thread

Posting Permissions

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