• Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL question

 
Joe Joseph
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Suppose i have 4 tables t1, t2, t3, t4.
The below query will fetch me a resultset i am interested in:
Select * from t1, t2, t3, t4 where t1.trade = t2.trade and t2.date = t1.date and t2.trade = t3.trade and t3.date = t1.date and t3.trade = t4.trade and t4.date = t1.date ( i have simplified the conditions in this example here for simplicity sake).
My problem is that i am also interested in finding the orphan records in each table ( ie records with entries missing on the tables for a trade). Note that i cannot use the outer joins here as this might yield me multiple records. How could i proceed with this ?
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 36031
432
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe,
You would need to do this as two queries (or a union of two queries.) The first is the one to find the non-orphaned data, which you have. The second is an outer join with a distinct filter for the orphaned data.
 
Gravity is a harsh mistress. But this tiny ad is pretty easy to deal with:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!