SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question i want query all users from users_table which it's ids not stored in first table.id ?

    hi ,

    i have table to store users informations , it's name is (users_table) and it's columns are : id,name

    and i have second table with name (first_table) and it's columns are : id,users_tableid

    i want sql query to fetch all users name from users_table but which it's id (users_table.id) not stored in the table first_table(first_table.users_tableid)

    so what's the true sql cmmand to do this ?

    thanks,

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it's a LEFT OUTER JOIN with an IS NULL test in the WHERE clause

    you will find this in most SQL tutorials

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it's a LEFT OUTER JOIN with an IS NULL test in the WHERE clause

    you will find this in most SQL tutorials

    thanks you mr.r937 , hmm it's new information to me , so please if it possible can you give me sql command for example

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks i tried and it's done

    thanks again

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm sorry , my try didn't work good , can u plz give me the example

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    plz show ur try and i will fix it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    plz show ur try and i will fix it
    Code SQL:
    SELECT users_list.*,reportsd_list.* FROM reportsd_list LEFT JOIN users_list ON users_list.id=reportsd_list.users_listid WHERE reportsd_list.thread_listid='1' AND ISNULL(reportsd_list.users_listid) ORDER BY reportsd_list.id DESC

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT users_list.*
         , reportsd_list.* 
      FROM users_list  
    LEFT OUTER
      JOIN reportsd_list
        ON reportsd_list.users_listid = users_list.id
       AND reportsd_list.thread_listid = 1 
     WHERE reportsd_list.users_listid IS NULL
    ORDER 
        BY users_list.id DESC
    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
  •