Mysql java question?

Hello,

Could anyone advise, if would it be possible to write a single sql delete statement, which for each orderno in the order table below. Also deletes the records in the items tables, with the same orderno?

Or would I need to use two delete statements? or even use the inner join?

Thank you

table (order)
orderno:
7823
4633
1788

table (items)
orderno:             item:                   quantity:
7823              Danish pastry                13
7823              Apple pie                      20
4633              Steak pie                      100
AntoniRyszard656Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
You can if you have set a cascade delete rule in the table definition, but not otherwise (you'll need two)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AntoniRyszard656Author Commented:
Hello,

Though, Would I be correct in saying it would be possible to use the select statement to find records in both the order, and items table with the same orderno field. Perhaps using the inner join?
0
CEHJCommented:
Yes you would be correct
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

AntoniRyszard656Author Commented:
Can the delete statement not be used like the select?  
0
CEHJCommented:
No
0
CEHJCommented:
If you set the cascade delete rule, both tables will be deleted from automatically when you delete from the parent table
0
AntoniRyszard656Author Commented:
Thank you,

Could anyone demonstrate how to use the select statement, and find the records in the order table and item table with the same orderno? Would I possibly need to use the inner join?

table (order)
orderno:
7823
4633
1788

table (items)
orderno:             item:                   quantity:
7823              Danish pastry                13
7823              Apple pie                      20
4633              Steak pie                      100
0
CEHJCommented:
select o.orderno, i.item from order o, item i where o.orderno = i.orderno
0
objectsCommented:
not so sure you can't do it in a single query, let me ask my db people and get back to you.
0
AntoniRyszard656Author Commented:
Thank you,

And to perform this select statement on each of the different orderno's in the order table.

Would I need to create a resultset from the order table, and loop through this, to get the orderno values to plug into the select statement CEHJ gave.
0
objectsCommented:
what version of mysql?

(and btw, why aren't you asking this in the mysql ta?)
0
objectsCommented:
> And to perform this select statement on each of the different orderno's in the order table.

you don't need a select, just two deletes (or possibly one).
0
AntoniRyszard656Author Commented:
I was just asking about setection, rather than deleting records.
0
objectsCommented:
what do u need to do a select for? what did u want to select
0
AntoniRyszard656Author Commented:
Hello,

I was trying to select the records in the items table which match a specific orderno in the order table.

table (order)
orderno:
7823
4633
1788

table (items)
orderno:             item:                   quantity:
7823              Danish pastry                13
7823              Apple pie                      20
4633              Steak pie                      100
0
objectsCommented:
but don't you just want to delete them?
you don't need a select to achieve that

delete from items where orderno=?
0
RuadRauFlessaCommented:
delete from items where orderno=? or orderno=? or orderno=? or orderno=?

for multiple deletes in a single query pertaining to diffirent orders.
0
CEHJCommented:
>>I was trying to select the records in the items table which match a specific orderno in the order table.

select o.orderno, i.item from order o, item i where o.orderno = i.orderno and o.orderno = 7823
0
CEHJCommented:
(
Parameterizable in a PreparedStatement as

select o.orderno, i.item from order o, item i where o.orderno = i.orderno and o.orderno = ?

)
0
AntoniRyszard656Author Commented:
Thank you,

Could I ask finally, if I had two tables, named author and book and tried to test if there isbn field was equal to a variable isbn.

And wrote a select statement as here: would using the AND tool mean the isbn would (have) to be found in both tables?

sqlquery = "SELECT * FROM author a, book b WHERE a.isbn =" + isbn + " AND b.title =" + isbn;

Thank you
0
AntoniRyszard656Author Commented:

And rather than using the AND tool is there an alternative OR. So the isbn could be found in both tables or just one?

Thank you
0
objectsCommented:
> And wrote a select statement as here: would using the AND tool mean the isbn would (have) to be found in both tables?

yes

> And rather than using the AND tool is there an alternative OR. So the isbn could be found in both tables or just one?

yes
0
AntoniRyszard656Author Commented:
Hello,

If I changed to sql to include OR:

sqlquery = "SELECT * FROM author a, book b WHERE a.isbn =" + isbn + " OR b.title =" + isbn;

Normally in programming (or) means to take one action, or an alternative. Does this mean if I use (or) in this sql statement, the records will be selected from the author or book, but could not gather be from both.

I just wanted to sure, I understood.
0
objectsCommented:
no it means to only include records in the result set where either of the conditions are met.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.