- Home /

Hi, I need to join two tables say Table P and Table P2 Table P is like below Orderno Status 1 A 1 B 1 C 2 A 2 B 2 C 2 D 3 A 3 B 3 C 3 D 4 E 4 E 4 F Table P2 is Like Below Orderno Boolean 1 Y 2 Y 3 N 4 N I need to query to join Table P and P2 where Boolean value is Y in Table P2 but there is no D in Table P in Oracle database. The answer would be 1 ( because Ordeno 1, Boolean has'Y' and Status doesn't have 'D') Can someone help me get a query? This is for Oracle Database. Thanks

Comment

**Answer** by anthony.green ·

This is a TSQL answer, but should give you the basis to convert to PL/SQL create table #p (orderno int, status char(1)) insert into #p values (1,'A'), (1,'B'), (1,'C'), (2,'A'), (2,'B'), (2,'C'), (2,'D'), (3,'A'), (3,'B'), (3,'C'), (3,'D'), (4,'E'), (4,'E'), (4,'F') create table #p2 (orderno int, boolean char(1)) insert into #p2 values (1,'Y'), (2,'Y'), (3,'N'), (4,'N') select distinct #p.orderno from #p inner join #p2 on #p.orderno = #p2.orderno where #p2.boolean = 'Y' and #p.orderno not in (select orderno from #p where status = 'd')

Why do you need to do the select from #p around the outside? Surely SELECT OrderNo from #p2 WHERE #p2.boolean = 'Y' AND #p2.orderNo NOT IN (SELECT OrderNo FROM #p WHERE status = 'D') Would do the job without the extra trip to #p? Maybe the optimiser would generate that anyway.

Good point, didn't think of it that way, but yeah your version is a lot more efficient, just checked the plans, your way avoids 2 costly sorts and an extra trip to #p

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges