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
 General SQL Server Forums
 New to SQL Server Programming
 Delete from database using JOIN or not how to

Author  Topic 

mamor90
Starting Member

5 Posts

Posted - 2013-03-26 : 10:37:40
i have 3 tables

EMPLOYEES_TECHNICAL_SKILLS
technical_skill_id(PK/FK)
employee_id(PK/FK)
technical_skill_level_id

TECHNICAL_SKILL_LEVEL
technical_skill_level_id (PK)
skill_level

TECHNICAL_SKILLS
technical_skill_id (PK)
skill_name
skill_type

i am trying to delete all data from the tables
But i only get it to work to delete EMPLOYEES_TECHNICAL_SKILLS Columns
and in the other tables i still have data

I have trying to do it without any JOIN like this.
using (knowitCVdbEntities db = new knowitCVdbEntities())
{
SPWeb theSite = SPControl.GetContextWeb(Context);
SPUser theUser = theSite.CurrentUser;
string strUserName = theUser.LoginName;

var theEmplDatabaseRem = (
from p
in db.EMPLOYEES
where p.username == strUserName
select p).FirstOrDefault();

_emp = theEmplDatabaseRem;

if (_emp != null)
{
LabelPleaseSelectDbListBox.Visible = false;

if (ListBoxDataBase.SelectedItem != null)
{


string vItem = ListBoxDataBase.SelectedItem.Value;
string valueListBox = vItem;
int indexOf = valueListBox.IndexOf("-", StringComparison.Ordinal);
string valueDatabase = valueListBox.Substring(0, indexOf - 1);

EMPLOYEES_TECHNICAL_SKILLS dbRemove = (from p
in db.EMPLOYEES_TECHNICAL_SKILLS
where p.employee_id == _emp.employee_id && p.TECHNICAL_SKILLS.skill_name == valueDatabase
select p).FirstOrDefault();

if (dbRemove != null)
{
_emp.EMPLOYEES_TECHNICAL_SKILLS.Remove(dbRemove);
db.SaveChanges();
ListBoxDataBase.Items.Remove(ListBoxDataBase.SelectedItem);
}

{
LabelPleaseSelectDbListBox.Visible = true;
LabelPleaseSelectDbListBox.Text = "You didn't choose anything";
}

}
// and this result in that i only delete from the first table not the others.

and i have also tried with JOIN like this.
var dbRemove = (from p in db.EMPLOYEES_TECHNICAL_SKILLS.AsEnumerable()
join m in db.TECHNICAL_SKILLS.AsEnumerable() on p.technical_skill_id equals
m.technical_skill_id
join l in db.TECHNICAL_SKILL_LEVEL.AsEnumerable() on p.technical_skill_level_id
equals l.technical_skill_level_id
where p.employee_id == _emp.employee_id && m.skill_name == valueDatabase
select p).FirstOrDefault();
and have still not got it to work , what could i have missed?

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-03-26 : 12:07:56
you can always write 3 different commands and call them sequentially; according to the referential integrity you have.

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

mamor90
Starting Member

5 Posts

Posted - 2013-03-26 : 15:35:25
quote:
Originally posted by xhostx

you can always write 3 different commands and call them sequentially; according to the referential integrity you have.

--------------------------
Joins are what RDBMS's do for a living



I think know what u mean but i dont know how to do it , because i am new in sql development
Go to Top of Page
   

- Advertisement -