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;
Peng Kuang
  • 11
  • 1
  • 4
  • 2
    You can't use a `join` in a `DELETE` statement in Oracle –  Mar 30 '16 at 09: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](http://stackoverflow.com/q/28623339/266304) 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? – Alex Poole Mar 30 '16 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. – Trinimon Mar 30 '16 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? – Peng Kuang Mar 31 '16 at 00: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. – Peng Kuang Mar 31 '16 at 00:47
  • @KapKuang: you should keep in mind, that a `JOIN` (comma separated tables) is the cartesian product of _all_ rows. So, for instance, `mp.MPlanNo=l.MPlanNo OR mp.MPlanNo=d.MPlanNo` combines matching rows from `diary` with _all_ non-matching (regarding `MPlanNo`) from `laboratory`, i.e. in the end it depends on other rows as well. What you are most likely aiming for is `UNION`, `UNION ALL` or using `AND` with an outer join. – Trinimon Mar 31 '16 at 08:49

0 Answers0