SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: SQL Condundrum

  1. #1
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Condundrum

    Hi there:

    I'm using MySQL 3.33.32 and we have a table which lists contacts.
    Each contact row has a Type column whose value is one of: "General", "Accounts", "Marketing" or "Other".

    I'm performing a JOIN on this table from a Companies table where the two tables are connected by a foreign key.

    However I only want a single row (A Company's Name) from the Companies table and a single row (A Contacts' Name, Email and Type) from the Contacts table where Contact type="General"
    or where Contact type="Other", but to get a Contact marked as "Other" only if no Type="General" exists.

    The Query As it Stands:
    Code:
    SELECT *,Companies.ID AS A,Companies.Name AS B,Companies.Voice AS C,Companies.Fax AS D,Companies.Type AS E,Contacts.Email AS F,Contacts.Name AS G,Contacts.Type AS H 
    FROM Companies
    LEFT JOIN Contacts 
    ON Companies.ID=Contacts.Company_FK 
    WHERE Companies.ID=<id here>
    But if I add "Where Contact='General' I'll only get Contacts who are of type General and the query fails for when there are no Contacts of Type "General".

    Does this make sense?

    Cheers

  2. #2
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I added the following which seems to do the trick - any other ideas are welcome though

    Code:
    .....AND Contacts.Type NOT IN ('Accounts','Marketing','Personnel')
      ORDER BY Contacts.Type ASC, Contacts.Name ASC

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by phptek
    any other ideas are welcome though
    okay, since you asked, don't use the asterisk in the SELECT list, you'll get all columns from all tables

    seriously, what you want is a little tricky, and is not possible in your version of mysql, because you need a subquery

    if you run your solution, you'll find you get both General and Other contacts, i.e. more than a single row when both of those exist

    rudy
    http://r937.com/

  4. #4
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy and thanks for your feedback:

    ..if you run your solution, you'll find you get both General and Other contacts, i.e. more than a single row when both of those exist
    OK I think I understand - but if I'm ordering the results by Contacts.Type excluding all contact types other than "Other" and "General" then if a "General" contact is present I'll get the details for this contact otherwise the details for the "Other" contact will be retrubed. Is this is right? And If neither is present, no rows are returned and I can write a little code to deal with that (PHP).

    What do you think?

    Thanks very much for your input Rudy.
    Russ

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by phptek
    ... then if a "General" contact is present I'll get the details for this contact otherwise the details for the "Other" contact will be retrubed. Is this is right?
    not exactly

    if neither is present, you get neither

    if only one is present, you get that one

    but if both a "General" and an "Other" contact is present, you get them both -- no "otherwise" about it

    nevertheless, if you're going to deal with this in your script, that should be okay, i wouldn't try to do more in the query, simply because you can't, not without subqueries

  6. #6
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    ...but if both a "General" and an "Other" contact is present, you get them both -- no "otherwise" about it
    Sure, I understand about the "otherwise" part.

    If I'm ordering the results alphabetically though, If a "General" contact occurs where an "Other" contact also occurrs, I'll get the "General" contact's details first right? This is how it seems to be working right now, but I'm running it passed you as you seem to be "The Guru"

    Cheers.
    Russ

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, you get both, and G does come before O, so just ignore the O for that particular company

    by the way, i've done this problem where you can have multiple people with the same role, the same person with multiple roles, multiple roles in a particular hierarchical order (sometimes CEO before Accounts, sometimes Other before General...), sometimes all selected contacts per company, sometimes only one, and to top it all off, sometimes the same person is actually in the table more than once with different ids...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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