Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: [help] INNER JOIN QUESTION

    hello sql gurus,

    i have a table called Establishments and another table called EstablishmentContacts. one establishment can have zero or more contacts, BUT only one contact is ever considered to be the primary contact. this is denoted by a field in the EstablishmentContacts table. so the tables look like this:

    Establishments
    --------------
    ID Name Address Postcode


    Establishment Contacts
    -----------------------
    ID Name Tel IsPrimary EstablishmentId


    i need a single SQL statement that returns all the establishments along with their PRIMARY contact name. bear in mind that an establishment may have zero contacts. so i have this statement:

    Code:
    SELECT e.Name, e.Address, e.Postcode, c.Name FROM Establishments e, FULL OUTER JOIN EstablishmentContacts c 
    ON e.ID = c.EstablishmentId
    however this statement returns a row for each contact, ie if an establishment has 3 contacts it will return 3 rows for the same establishment. i only want the primary contact. so then i added:

    Code:
    SELECT e.Name, e.Address, e.Postcode, c.Name FROM Establishments e, FULL OUTER JOIN EstablishmentContacts c 
    ON e.ID = c.EstablishmentId
    WHERE c.IsPrimary=1
    but of course this now only returns establishments that have a primary contact set. ie it will not return a row if the establishment has zero contacts, or even if it has many contacts but none of them have a primary contact set.

    what i need is to return a row for every establishment, along with the primary contact if there is one, or NULL or just an empty string if there is not one.

    can this be done ?
    any help appreciated.
    -Paul

    [edit]
    i should also have said that the way i was doing this before was to do an sql query for all the establishments, and then loop through each one and use the ID to do another sql query to get the primary contact for that establishment. clearly as the number of establishments increase, the number sql queries increase and its causing my web page to be pretty slow now...hence the need for a single sql statement. thanks !
    [/edit]

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    ...if there is one, or NULL...
    You just need to go with this idea a little further in your where clause.

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Quote Originally Posted by MCrowley View Post
    You just need to go with this idea a little further in your where clause.
    ok think i got it...



    Code:
    SELECT e.Name, e.Address, e.Postcode, c.Name FROM Establishments e, FULL OUTER JOIN EstablishmentContacts c 
    ON e.ID = c.EstablishmentId
    WHERE c.IsPrimary=1 
    OR
    ((COUNT(id) FROM EstablishmentContacts WHERE EstablishmentId=Establishmens.Id AND IsPrimary=1) =0)
    seems to do the trick, please let me know if you know of a better more effiecient way ! thanks !

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't know if your solution gives the right answer, but it is way too complicated.

    First, I would change the FULL OUTER JOIN to a LEFT OUTER JOIN. In this particular case both will give you the same result, but in other situations it will give you a lot more than what you asked for.
    Code:
    SELECT e.Name as eName, 
    	c.Name as Cname
    FROM Establishments e 
    	LEFT OUTER JOIN EstablishmentContacts c ON 
    		e.ID = c.EstablishmentId 
    WHERE c.IsPrimary=1
    Now how does this work: You start from Establishments and the DBMS will try to relate every record in it to a record from EstablishmentContacts by using e.ID = c.EstablishmentId.
    • If it can relate them, you get all those combined records, possibly multiple records when multiple records from EstablishmentContacts relate to one Establishments record.
    • If it can't relate them, you still get 1 record, namely a record from the LEFT side of the JOIN, in this case the Establishments record. All the columns of EstablishmentContacts that you reference in your script are considered to have a NULL value.
    Now the WHERE clause comes into play. The DBMS will only keep those records that comply with the rules in it. In this case there is only one rule: IsPrimary=1. So from the above result, only the records with IsPrimary=1 will be in the result set.

    You found your error? Think about the hint you got from MCrowley.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2010
    Posts
    3
    Quote Originally Posted by Wim View Post
    I don't know if your solution gives the right answer, but it is way too complicated.

    First, I would change the FULL OUTER JOIN to a LEFT OUTER JOIN. In this particular case both will give you the same result, but in other situations it will give you a lot more than what you asked for.
    ok so i changed the FULL to LEFT...thank you for the explanation but aside from making this change your sql is the same as mine.

    Quote Originally Posted by Wim View Post
    You start from Establishments and the DBMS will try to relate every record in it to a record from EstablishmentContacts by using e.ID = c.EstablishmentId.
    • If it can relate them, you get all those combined records, possibly multiple records when multiple records from EstablishmentContacts relate to one Establishments record.
    • If it can't relate them, you still get 1 record, namely a record from the LEFT side of the JOIN, in this case the Establishments record. All the columns of EstablishmentContacts that you reference in your script are considered to have a NULL value.
    yes i think i said this in my original post.

    Quote Originally Posted by Wim View Post
    Now the WHERE clause comes into play. The DBMS will only keep those records that comply with the rules in it. In this case there is only one rule: IsPrimary=1. So from the above result, only the records with IsPrimary=1 will be in the result set.
    yep, again i said this my original post.

    Quote Originally Posted by Wim View Post
    You found your error? Think about the hint you got from MCrowley.
    my god you guys are cryptic. i posted for help and i got a 'hint'. ok fair enough, so i had a think and came up with a solution (which appears to work) which i posted asking if you experts can tell me if you know of a better way. now you respond saying its too complicated...and tell me to look at the 'hint' again. im sure you are both nice guys (or girls) and i do appeciate that you dont know me and have taken time out of your day to respond to me, but i am not at school i am a developer with a timescale who isnt overly proficient at sql and has asked for help. i have clearly demonstrated an effort to solve the problem myself and am in need of assistance so perhaps a more simple answer would be to just post the sql. in the meantime im just going with what i got.

    thank you.

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    unclepauly, I think what Wim was trying to say with 'it is too complicated' was the FULL OUTER JOIN. This generates extra rows (that you don't want anyway) for all the EstablishmentContacts rows that are NOT Primary.

    I have a slight variation on his query below. It uses a derived table to filter out the non-primary rows before they are joined with Establishments table.
    Code:
    SELECT e.Name as eName, 
           c.Name as Cname
    FROM Establishments e 
           LEFT OUTER JOIN 
           (SELECT Name, EstablishmentId
            FROM EstablishmentContacts
            WHERE IsPrimary=1
           ) AS c
            ON e.ID = c.EstablishmentId
    In general, it is better to filter out rows as soon as possible.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by unclepauly View Post
    ok so i changed the FULL to LEFT...thank you for the explanation but aside from making this change your sql is the same as mine.
    Indeed. I wanted you to discover the source of your error. As I think "Give a man a fish and he has food for a day. Teach a man to fish and he has food for his life." also counts for learning SQL.
    yes i think i said this in my original post.
    I wanted you to show the way a DBMS will handle that query.
    my god you guys are cryptic. i posted for help and i got a 'hint'.
    ...
    but i am not at school i am a developer with a timescale who isnt overly proficient at sql and has asked for help. i have clearly demonstrated an effort to solve the problem myself and am in need of assistance so perhaps a more simple answer would be to just post the sql.
    OK, I will give you the answer, but if you don't put an effort in it to discover what went wrong, you haven't learned a thing. And that is a pity, because in a day/ a week/ a month / ... you are faced with another deadline, a similar problem, and you will have to post another similar question here, hoping that someone will give you that answer quick. I wouldn't like to be dependent on someone else in such situations.

    I didn't want to be cryptic or mean to you, I just wanted to give you all the relevant information so you could learn. Most people on this forum are here to learn.
    Code:
    SELECT e.Name as eName, 
        c.Name as Cname
    FROM Establishments e 
        LEFT OUTER JOIN EstablishmentContacts c ON 
            e.ID = c.EstablishmentId 
            AND c.IsPrimary=1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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