Help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a query

Author

Topic

ernie99
Starting Member

12 Posts

Posted - 2008-11-14 : 07:51:26

Hi everyone, I have only a base level knowledge of SQL, can anyone help me achieve the following?

I need to create a script that removes all records from the JobTypeByLocationCode table where the Location Name = 'Inner London', 'Outer London', 'Fringe', 'Rest of England & Wales' and the Job Type Name = 'Main Grade', 'Music Instructor', 'Quality Improvement Officer'

The JobTypeLocationCode table only contains code foreign keys for the Location & JobType tables so these codes have to be retreived from the Location and JobType based on the 'Name' strings provided in order to be able to delete the right records.

The tables are as follows

JobTypeByLocationCode
JobTypeByLocationCode int
JobTypeCode int
LocationCode int

Location
LocationCode int
Name varchar(50)

JobType
JobTypeCode int
Name varchar(50)

Thanks in advance for your help.

acollins74
Yak Posting Veteran

82 Posts

Posted - 2008-11-14 : 07:57:03

This is a pretty straight forward query. If you know what foreign keys are, you should certainly know how to write this query.
Is this a homework question. Check out www.w3schools.com/sql/default.asp its a nice site that will teach you sql to a more comfortable level. Then when your stumped, post your problems and people will help you along.

ernie99
Starting Member

12 Posts

Posted - 2008-11-14 : 08:08:59

Solved this is the sql

delete from jobtypebylocation where jobtypebylocationcode in
(
select jobtypebylocationcode
from jobtypebylocation j
inner join jobtype jt on jt.jobtypecode = j.jobtypecode
inner join location l on l.locationcode = j.locationcode

where
j.jobtypecode in
(
SELECT JOBTYPECODE
FROM JOBTYPE
WHERE '%Main Grade, Music Instructor, Quality Improvement Officer%' LIKE '%' + [NAME] + '%'
)
and j.locationcode not in
(SELECT locationcode
FROM LOCATION
WHERE '%Scotland%' LIKE '%' + [NAME] + '%')
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources