Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    16

    Unanswered: mySQL join question

    Hi Forum

    I have three tables. A PROJECTS and IMAGES table and a PROJECT_IMAGES table which attaches images to a project.


    PROJECTS
    -ID
    -Title

    IMAGES
    -ID
    -Path

    PROJECT_IMAGES
    -ProjectID
    -ImageID


    I was asked to create a script which erases the images on the server which are not in the PROJECT_IMAGES table. Is there an efficient way of returning only the images which are not present in the PROJECT_IMAGES table in one query?

    Any insight or help appreciated.

    Much thanks

    Stephank

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    dnt know whether this is an efficient method...

    Create Table Project_Images
    (
    PID Int,
    IID Int
    )
    Go

    Create Table Images
    (
    IID Int,
    Path Varchar(10)
    )



    Delete from Project_Images Where IID in (
    Select PI.IID from Project_Images PI
    Left Outer join
    Images I
    On PI.IID =I.IID
    Where I.IID is null)


    not sure whether this is wat u r looking 4......
    Cheers....

    baburajv

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Will this do what you want?
    Code:
    SELECT i.path
    FROM   images i
     LEFT
     OUTER
      JOIN project_images p
        ON p.imageid = i.id
    WHERE  p.imageid IS NULL
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2007
    Posts
    16
    Wow thanks to both of you.

    That actually worked very well. I haven't tried baburajv's Delete Statement yet. But the select with the LEFT OUTER JOIN worked. I'm absolutely not familiar with these joins. So confusing. Also the way you're using a variable ... FROM IMAGES i ... what do you call this method? I'd like to read up on it.

    Thanks

    stephank

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT i.path
    FROM   images As i
     LEFT
     OUTER
      JOIN project_images As p
        ON p.imageid = i.id
    WHERE  p.imageid IS NULL
    The above should make things clearer. I have (unfortunately) got into the habit of omitting the "As" keyword, because it is not essential to the syntax.

    Anyhow, the term you are looking for is "Aliasing".
    You can alias tables, column names, literal strings etc!
    Code:
    SELECT e.FName + ' ' + e.SName As [FullName]
    FROM   employees As [e]
    
    SELECT 'test' As [LiteralStringAlias]
    JOINs are hard to get your head round at first, but (as you can see) are far more important that you might think!

    Here are some lovely links you should take a look at to learn a bit more about JOINs:
    http://www.w3schools.com/sql/sql_join.asp
    http://sqlzoo.net/3b.htm

    If you have any questions that crop up from either link then feel free to post them back here!
    George
    Home | Blog

Posting Permissions

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