Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions


Request for help

647960 Member Posts: 1
edited July 2008 in SQL & PL/SQL
Dear All

I Have The Following Tables

Table A As Val As Column Contains - 1,2,3,5,6
Table B As Val As Column Contains- 2,3,4

When I Am Using Query

Using Intersect

Select Val From A Where Val In (1,2,3) - Q1
Select Val From B Where Val In (2,3) -Q2

Now I Get Result As 2,3 Only When Two Q1,Q2 Are Executed.

Then I Am Using

Select Val From A Where Val In (5,6) - Q1
Select Val From B Where Val In (2,3) -Q2

Now I Got No Rows Return

But I Want The Query Q2 Result Separatly When Q1 Is Also Not Fired. If Q1,Q2 Is Fires Common Values
Only To Be Return....

If Any Idea Bout Query ??????????




  • BluShadow
    BluShadow Member, Moderator Posts: 40,986 Red Diamond
    Sorry, but your issue isn't clear.

    The output you are getting is what I would expect from those queries. What are you wanting exactly? Perhaps show us by giving an example of the data if you have trouble explaining it in words.
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    First run your intersect without a WHERE clause.
    Examine the returned rows.

    Anything you put in the WHERE clause filters (limits) the rows that will be returned by that portion of the query BEFORE the intersection takes place.
  • Sven W.
    Sven W. Member Posts: 10,511 Gold Crown
    there is also a MINUS operator. Maybe you like that one more then INTERSECT? But since you didn't tell us your business case, we can only guess.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited July 2008
    Maybe below.
    select aVal
    from (select a.Val as aVal,b.Val as bVal,
    max(b.Val) over() as hasNull
    from A Left join B
    on a.Val = b.Val)
    where hasNull is null
    or bVal is not null
This discussion has been closed.