long story short, I have a coupon system project. in it I have companys, coupons and customer. all of their data is save in an SQL DB, based on apache derby. for example: a company exist on the company table. has id, name, password, email a coupon exist on the coupon table. has id, title, price, etc.. only a company can create a coupon, and what it does, not only it is created on the coupon table mentioned above, it is also populating a second 'index' table, joining company's and coupons= company_coupon. has company_id, coupon_id
now everything else in the project is working beautifully, and the tables are checked by my teachers and everything is configured correctly.
what I'm trying to do next, and just cant seem to figure out the syntax for is: when I delete a company, I also want to delete every coupon this company has created, from the coupon table. this I do by searching the company_coupon table for company_id matches, and deleting based on the coupon_id paired with them.
I'm trying this statment:
String sql = "DELETE FROM coupon INNER JOIN company_coupon ON id = company_coupon.coupon_id WHERE company_coupon.company_id = "
sql += companyObject.getId();
<-- method is getting an object and I have access to it's id, but I tried hard coding a specific company id and still..
the same error keeps coming back in different variations:
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "INNER" at line 1, column 8.
I searched online here and elsewhere, was advised to add an 'alias' after the DELETE. got the same error, pointing at that extra word:
"DELETE c FROM coupon INNER JOIN company_coupon ON id = company_coupon.coupon_id WHERE company_coupon.company_id = " ---->>
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "c" at line 1, column 8.
any ideas? :(
Solved:
String sql = "DELETE FROM customer_coupon WHERE coupon_id IN (SELECT company_coupon.coupon_Id FROM company_coupon WHERE company_coupon.company_id = ";
sql += comp.getId();
sql += ")";