Delete wiyh inner join

[Roulaa]

helleo all
i want to delete from 5 tables with inner join like that:
Category=CategoryId,CategoryName,..
Item=ItemId,ItemName,pictureId,VideoId,AudioId,CategoryId
Picture=pictureId,pictureName
Video=VideoId,VideoName
Audio=AudioId,AudioName
the user will Enter category name in text box iwant to delete Category+Item+picture+audio+video
help me please :)

[Ketsuekiame]

In your database design you should have your Foreign Keys set up to reference each table in the chain.

You can set your Foreign Keys to perform an action when you perform an operation on the database. In your case you want it to delete. This will cascade the deletion along your table chain.

Which database are you using?

[Roulaa]

i use access database
i know by forign key
but does not wwork with me
can you give mw query?
i do update with inner join iit's work full
but delete no:(

[Ketsuekiame]

You can't pass inner join to delete. You will have to delete each one individually.

So when you goto the table to delete, you need to get the ID's used in the other tables before you delete it and then do the same with the rest of the tables.

Your Foreign Key action shouldn't be failing when you try and delete a row...

[Roulaa]

ok thank you i'll try and tell uoy later

[Roulaa]

hi all,
im to do some query inside other
do not work with me
i'm Tension
can any one help me
tables written in the first thread
:(

[Ketsuekiame]

I'm sorry I don't understand what you want.

Try writing in code what it is you want to do (even if the code doesn't work, using pseudocode may be a good idea)

[Roulaa]

helleo
thi sis tables and it's colume
Category=CategoryId,CategoryName,..
Item=ItemId,ItemName,pictureId,VideoId,AudioId,CategoryId
Picture=pictureId,pictureName
Video=VideoId,VideoName
Audio=AudioId,AudioName
each category contain item
each item has picture ,video ,audio
user enter category name
by writte delete query
category and item delete
but picture ,video ,audio still in data base
iwrite that code

string query10 = "Select ItemId.ITEM  inner join  category on  ITEM.CategoryId=CATEGORY.CategoryId && CATEGORY.CategoryName='" + textBox1.Text + "'";
                    OleDbCommand aCommand10 = new OleDbCommand(query10, conn);

                    aCommand10.ExecuteNonQuery();
                    OleDbDataReader drr = aCommand10.ExecuteReader();
                    while (drr.Read())
                    {
                         int y = drr.GetInt32(0);                        string query1 = "DELETE * FROM PICTURE WHERE PicturId='" + y + "' ";
                        OleDbCommand aCommand50 = new OleDbCommand(query1, conn);
                        aCommand50.ExecuteNonQuery();
                        string query2 = "DELETE * FROM VIDEO WHERE VideoId='" + y + "' ";
                        OleDbCommand aCommand52 = new OleDbCommand(query2, conn);
                        aCommand50.ExecuteNonQuery();
                        string query3 = "DELETE * FROM PICTURE WHERE AudioId='" + y+ "' ";
                        OleDbCommand aCommand52 = new OleDbCommand(query3, conn);
                        aCommand50.ExecuteNonQuery();                    }

                        string DeleteCategoryCommand = "DELETE  FROM CATEGORY  WHERE CategoryName ='" + textBox1.Text.ToString() + "' ;";

help
help
help

[Ketsuekiame]

Ok, firstly, please stop writing help help help all the time. It makes me feel pressured. I'm already here, helping you when I can, I also have my own things to do you know ;)

Ok, your code itself looks fine, so I imagine you're getting errors from your foreign key constraints.

So let's go back to what I said earlier. You need to make your foreign keys propagate the delete throughout the database. Unfortunately, I don't use MS Access as a database, but you should have the option to set what happens "OnDelete". At which point you need to make it "Cascade".

Other than that you need to goto the top of the chain (the table that references other tables, but has no tables referencing to it) and start deleting from there first.

What is happening is, you're trying to delete a row from a table, but other tables are referencing that row. Therefore, you aren't allowed to delete them. You must delete whatever is referencing that row first BEFORE you can delete that row specifically. This is the Foreign Key Constraint

[Roulaa]

O.K
i'll stop say.......:)
error occure fristly in

string query10 = "Select ItemId.ITEM  inner join  category on  ITEM.CategoryId=CATEGORY.CategoryId && CATEGORY.CategoryName='" + textBox1.Text + "'";

pervious code i writte it by forign key assumption.

there are no large different between sql and access.

[Ketsuekiame]

there are no large different between sql and access.

The SQL isn't, no. But the UI is and I don't know how to use the Access UI ;)

In the string in your comment you have the table and the property backwards. It should be "SELECT ITEM.ItemId INNER JOIN category ON ITEM.CategoryId = CATEGORY.CategoryId WHERE CATEGORY.CategoryName='myCategory'" =)

I know you have foreign keys, I'm saying that's what is stopping you from deleting.

[Roulaa]

you mean why i mwritte select
?

to save forign key
then delete
because no relation ship between category and(pictur, video, audio)

[Ketsuekiame]

Hm, no. When you create your foreign key you can specify an action to perform when a row gets deleted. Commonly it's either "NO ACTION" or "CASCADE"
I think that yours must be set to "RESTRICT"/"NO ACTION"

Foreign Keys on Wikipedia

[pritesh2010]

Ok i got your problem and the solution is as follow.
create your table that is Item table
in tabel
Itemid is Primary key and pictureId,VideoId,AudioId,CategoryId make all this column as
alternate key.
and after that create your other table with the name Category,Picture,Video, and audio.
and alll this table make it's respected id as forigen key constriant with cascade/Delete
of Item table.
then fire a query on item table

like.
Delete from Item Where ItemId='xyz'
when query fire it will also delete all the item from the respected table so no need to passing
query with innser join

[Roulaa]

ok thank for all
an di'm sorry because i do not visit this page last time
i have some opearations.:)