SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: SQL Problem

  1. #1
    SitePoint Addict bronze trophy
    Join Date
    Sep 2005
    Posts
    318
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    SQL Problem

    Hi there!

    I am working for a client and they have two tables:

    pet_species (ID, type_id, name)
    pet_types (ID, name)

    What I need is to get all pet_species from the table and then their pet type by joining via the type_id. That bit I can easily do with the following:

    Code:
    SELECT pet_species.ID, pet_species.name, pet_types.name FROM pet_species
    INNER JOIN pet_types ON pet_types.ID = pet_species.type_id
    But I also need to get pet_types which have no pet_species link. Is it possible to merge these two use cases together into one single SQL?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Patche View Post
    But I also need to get pet_types which have no pet_species link.
    classic LEFT OUTER JOIN problem
    Code:
    SELECT pet_types.name 
         , pet_species.ID
         , pet_species.name
      FROM pet_types
    LEFT OUTER 
      JOIN pet_species 
        ON pet_species.type_id = pet_types.ID
    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
  •