By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
select distinct (tb1.SupporterID), tb1.lotteryNumber, tb1.DrawNumber, tb1.PrizeDrawID, tb1.prizeValue, tb1.winningTicket, tb1.title, tb1.initials, tb1.surname, tb1.address1,tb1.address2,tb1.address3,tb1.address4, tb1.supporterNumber, tb1.prizeName from ( select distinct pw1.SupporterID, pw1.PrizeWinID, Lpt.lotteryNumber, pw1.DrawNumber, pw1.PrizeDrawID, pd.prizeValue, pw1.winningTicket, pw1.title, pw1.initials, pw1.surname, pw1.address1,pw1.address2,pw1.address3,pw1.address4, s.supporterNumber, p.prizeName from prize_win pw1 inner join ( select pt.purchasedTicketID,L.lotteryNumber from purchased_Ticket pt inner join lottery L on pt.lotteryID = L.lotteryID where L.lotteryNumber between 215 and 230) Lpt on pw1.PurchasedTicketID = Lpt.purchasedTicketID inner join prize_draw pd on pw1.PrizeDrawID = pd.PrizeDrawID inner join supporter s on pw1.SupporterID = s.SupporterID inner join prize p on pw1.PrizeDrawID = p.PrizeDrawID) tb1 inner join (select pw1.SupporterID, pw1.PrizeWinID from prize_win pw1 inner join ( select pt.purchasedTicketID from purchased_Ticket pt inner join lottery L on pt.lotteryID = L.lotteryID where L.lotteryNumber between 215 and 230) Lpt on pw1.PurchasedTicketID = Lpt.purchasedTicketID ) tb2 on tb1.SupporterID = tb2.SupporterID where tb1.PrizeWinID <> tb2.PrizeWinID order by tb1.SupporterID asc;
Dig Deeper on Oracle and SQL
Related Q&A from Mike Ault
I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to...continue reading
How to find the definition or structure of a dropped table? I know the table's name but I don't know the columns and datatypes. It no longer exists.continue reading
I'm having a problem with dynamic SQL, specifically getting my incoming parameter into the SQL statement. I am using Oracle 9i and this procedure ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.