mySQL join question

stephank · #1 (**permalink**) 09-06-07, 13:41

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

baburajv · #2 (**permalink**) 09-07-07, 05:08

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......

gvee · #3 (**permalink**) 09-07-07, 05:35

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

stephank · #4 (**permalink**) 09-07-07, 11:28

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

gvee · #5 (**permalink**) 09-08-07, 08:34

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech