Results 1 to 9 of 9

Thread: select all

  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: select all

    I have 2 tables. The one has a full list of records, and the other only has records where a certain field is populated e.g

    USERS
    name-age
    Tom 27
    Sam 56
    Bugs 76


    and
    JOB
    name-occupation
    Tom technician

    If I do a selection as such
    Code:
    select name, age, occupation from users, occupations 
    where users.name=job.name
    I don't get Sam and Bugs in the output. How do I get the following?

    Tom 27 technician
    Sam 56 null
    Bugs 76 null

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    When selecting from more than one table, use the JOIN syntax. This allows you to use something called OUTER joins (a good intro here: SQLzoo: Two table JOIN)
    Code:
    SELECT users.name
          , users.age
          , job.occupation
    FROM   users
     LEFT
     OUTER
      JOIN occupations 
        ON users.name = job.name
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    SELECT users.name
    , users.age
    , job.occupation
    FROM users
    LEFT
    OUTER
    JOIN JOBS
    ON users.name = job.name

  4. #4
    Join Date
    Nov 2009
    Posts
    10
    Yip, that does the trick... thanks!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Err.. that was a, uhm, deliberate typo?

    Yeah!
    Was just checking you were all paying attention
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mishaalsy View Post
    SELECT users.name
    , users.age
    , job.occupation
    FROM users
    LEFT
    OUTER
    JOIN JOBS
    ON users.name = job.name
    close but no cigar

    SELECT users.name
    , users.age
    , job.occupation
    FROM users
    LEFT
    OUTER
    JOIN job
    ON users.name = job.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well it's some kind of JOB I can tell you that

    Do you want to see the type of JOB(s) That no one has gotten yet?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2009
    Posts
    262
    hahahahaha ... *owing to the masters *

  9. #9
    Join Date
    Nov 2009
    Posts
    3
    Hi Everyone..

    Well I have recently join this site and currently I am working on my project which is base on .net in C#.
    I have started to create database but I want to some useful tutorials regarding database.
    So if anyone have then post it here. Thanks in advance.

Posting Permissions

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