1

I need to select a person's record from multiple tables, I can get the results by using two seperate states, but cannot do the same by combining them in one statement.

The tricky thing here is that both the Laboratory table and the Diary table have a foreign key referencing the MPlan table, but they do not have any relation with each other.

How can I accomplish this? I need to do that just in one single statement.

--I get 2 rows by running the codes below
SELECT *
FROM Participant p, PCASE pc, Meeting m, MPlan mp, Laboratory l
WHERE p.PartID=pc.PartID
AND pc.PCaseNo=m.PCaseNo
AND (m.PCaseNo=mp.PCaseNo
    AND m.MeetingNo=mp.MeetingNo)
AND mp.MPlanNo=l.MPlanNo
AND p.PartID=12345;

--I get 4 rows by running the codes below
SELECT *
FROM Participant p, PCASE pc, Meeting m, MPlan mp, Diary d
WHERE p.PartID=pc.PartID
AND pc.PCaseNo=m.PCaseNo
AND (m.PCaseNo=mp.PCaseNo
    AND m.MeetingNo=mp.MeetingNo)
AND mp.MPlanNo=d.MPlanNo
AND p.PartID=12345;

--but I get 38 rows by running the below codes, apparently it is not correct
SELECT *
FROM Participant p, PCASE pc, Meeting m, MPlan mp, Laboratory l, Diary d
WHERE p.PartID=pc.PartID
AND pc.PCaseNo=m.PCaseNo
AND (m.PCaseNo=mp.PCaseNo
    AND m.MeetingNo=mp.MeetingNo)
AND (mp.MPlanNo=l.MPlanNo OR mp.MPlanNo=d.MPlanNo)
AND p.PartID=12345;
6
  • 2
    You can't use a join in a DELETE statement in Oracle Commented Mar 30, 2016 at 9:42
  • 2
    Please don't ask two questions in one. Fortunately the second part is a duplicate of Oracle DELETE sql with JOIN doesn't work anyway, so you can just remove that part and focus on the first part. What result are you expecting from that - six rows? You might just be looking for a union of your first two queries? Commented Mar 30, 2016 at 10:32
  • Build a cursor for data selection (including table identifiers), loop through it's items and remove data from tables by using the selected ids. Take care of foreign keys. Commented Mar 30, 2016 at 11:26
  • thanks @Trinimon but I haven't learned how to write a loop. Any other method that I can delete the data for one particular participant just in one statement and without changing the foreign keys to ON DELETE CASCADE? Commented Mar 31, 2016 at 0:45
  • @AlexPoole yes, I think 6 rows would be correct. not union of the first two queries, but because I cannot get the correct result just in one statement thus I have to split the query into two. Commented Mar 31, 2016 at 0:47

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.