Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

Request for help

647960
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
Intersect
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
Intersect
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 ??????????


Thanks

K.Prabakaran

Comments

  • 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.