CODEWITHSUNDEEP
mysqljoininner-join
Tiago
Tiago

Reputation: 653

INNER JOIN - Mysql and PHP

I'm having some problems with inner join in mysql.

I have the following code:

DELETE T1,T2 FROM table1 AS T1 INNER JOIN table2 AS T2 ON T1.id = T2.pageid WHERE T1.ID = 2

The problem here is if there's nothing in the table2 to delete.

For example if table1 and table2 have an ID 2, they delete both. If there's an ID 2 only in table1, it doesn't delete the record in table1.

I already tried to use foreign key but it doesnt work either:

CREATE TABLE banners (
  id int(11) NOT NULL AUTO_INCREMENT,
  username TEXT NOT NULL,
  pageid int(11) NOT NULL,
  sitename TEXT NOT NULL,
  imgurl TEXT NOT NULL,
  refurl TEXT NOT NULL,
  clicks int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY pageid (pageid),
  CONSTRAINT edits_ibfk_1 
  FOREIGN KEY (pageid) 
  REFERENCES pages (id) 
  ON DELETE CASCADE
)

If I use the SQL:

DELETE FROM banners WHERE id= ?

It only deletes from table1 and left table2 records there :(

I'd prefer to do that with SQL query. Could someone help me in this case? Thanks!

Upvotes: 0

Views: 69

Answers (2)

Shadow
Shadow

Reputation: 34294

  1. Use a left join instead of inner join (t1 seems to be the parent table). Left join takes all records from the left table, regardless how many matching records you have in the right table.

  2. if you use foreign keys, then delete from the parent table, not from the child to trigger the cascaded delete. The banners table is your child table, pages is the parent because banners references the pages table. Deleting a record from the child will not affect the parent table.

Upvotes: 1

Michael
Michael

Reputation: 445

From your description, it appears that your banners table (table1) is dependent on table 2.

The foreign key of table 1 references table 2, but table 2 "doesn't care" about table 1 (table 1 is the independent entity). When you delete an item from table 1, the data integrity is not ruined as there is no other entity that references that specific row in table 1.

ON DELETE CASCADE would take effect if you deleted a row in table 2 (pages). The rows in the banners table that reference the deleted row would also be deleted.

In other words, if a parent is deleted, ON DELETE CASCADE will delete the children as well. However, if a child is deleted, the parent will remain in tact.

You need to verify that this parent-child relationship is correct. If banners are supposed to be children of pages, you will need to move the foreign key column to the pages table. Then, your second query would result in the deletion of the children pages, as well as the specified banner.

If your current schema is correct, you can simply delete the parent pages and the children banners will be removed.

Upvotes: 1

Related Questions