SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: SQL Statement

  1. #1
    SitePoint Addict
    Join Date
    May 2003
    Location
    Calgary, Alberta, Canada
    Posts
    275
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Statement

    I cant quite get my head around what to do here, so if anyone could give me a hand it would be greatly appreciated.

    I have three tables:
    ORGANIZATION
    ---------------
    id
    name
    email
    etc

    USER_ORGANIZATION
    --------------------
    id
    userId
    organizationId

    USER
    -----
    id
    name
    email
    etc

    I want to select all from organizations that a user doesnt belong to.

    Heres where I am at now:
    Code:
    SELECT ORGANIZATION.id
           ORGANIZATION.name
      FROM ORGANIZATION,
           USER_ORGANIZATION,
           USERS
     WHERE OGANIZATION.id=USER_ORGANIZATION.organizationId
       AND USER_ORGANIZATION.userId=USERS.id
       AND USER_ORGANIZATION.userId<>1

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    OK, here's one way to do it:

    Code:
    SELECT o.id, o.name, o.email, o.etc
      FROM organization o
     WHERE o.id NOT IN (SELECT organizationID FROM user_organization WHERE userID = 1)
    You could also use an (LEFT?) OUTER JOIN but since I always screw up that syntax I'll let someone else(oh rudy....) point it out to you
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Addict
    Join Date
    May 2003
    Location
    Calgary, Alberta, Canada
    Posts
    275
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response Dave.

    I should have said that Im using MySql, so inner queries arent an option.
    How could I do that in MySql?

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    From the manual

    1.8.5.1 Subqueries
    MySQL 4.1 supports subqueries and derived tables. A ``subquery'' is a SELECT statement nested within another statement. A ``derived table'' (an unnamed view) is a subquery in the FROM clause of another statement. See section 14.1.8 Subquery Syntax.

    For MySQL versions older than 4.1, most subqueries can be rewritten using joins or other methods. See section 14.1.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions for examples that show how to do this.

  5. #5
    SitePoint Addict
    Join Date
    May 2003
    Location
    Calgary, Alberta, Canada
    Posts
    275
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John
    From the manual

    1.8.5.1 Subqueries
    MySQL 4.1 supports subqueries and derived tables. A ``subquery'' is a SELECT statement nested within another statement. A ``derived table'' (an unnamed view) is a subquery in the FROM clause of another statement. See section 14.1.8 Subquery Syntax.

    For MySQL versions older than 4.1, most subqueries can be rewritten using joins or other methods. See section 14.1.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions for examples that show how to do this.
    Didnt know that but I need this to work with versions lower than 4.1 so the subquery is still not an option.

    How can I do the query Dave posted without subqueries?

  6. #6
    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)
    sorry for the delay, real life priorities
    Code:
    select ORGANIZATION.name
         , ORGANIZATION.email
      from ORGANIZATION
    left outer
      join USER_ORGANIZATION
        on ORGANIZATION.id 
         = USER_ORGANIZATION.organizationId
    left outer
      join USER
        on USER_ORGANIZATION.userId 
         = USER.id
       and USER.name = 'fred'
    group
        by ORGANIZATION.name
    having count(USER.name) = 0
    by the way, you do not need and should remove
    the id field from the USER_ORGANIZATION table
    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
  •