-7

I have a table with some data:

 id     month     price
 100    May       2000
 100    June      3000
 100    July      2500
 192    March     2000
 192    May       2000
 101    May       2000
 101    June      3000
 100    August    3100

I need to get the ids, by using only INTERCEPT.

1) If the "id" has month "May" and price "2000", and the same "id" also has month "June" and price "3000". Example: 100, 101 have "May=2000" and "June=3000"

I need to get the ids, by using only EXCEPT.

2) If the "id" has month "May" and price "2000", but the same "id" does not have month "June" and price "3000"

Example: 192 has "May=2000" but not "June=3000"

Please don't use IN.

Esoteric Screen Name
  • 6,082
  • 4
  • 29
  • 38
SSRaavi
  • 16
  • 7
  • 5
    Did you try anything at all ? This is not write my code service ? – Mahesh Feb 09 '15 at 04:59
  • 5
    Why two RDBMS tags, `mysql` and `oracle`? – Lalit Kumar B Feb 09 '15 at 05:20
  • 4
    Is there a reason for the restriction other than a homework assignment? – Justin Cave Feb 09 '15 at 05:45
  • Yes I already tried. But it simply giving check syntax. @Coder of Code – SSRaavi Feb 09 '15 at 09:11
  • I want to know the simple way @Lalit Kumar – SSRaavi Feb 09 '15 at 09:11
  • Yes @JustinCave I tried the query as SELECT id FROM TB_NAME WHERE month="May" AND price="2000" INTERSECT SELECT id FROM TB_NAME WHERE month="June" AND price="3000" I also tried and tired with alias, but not working. I want to know is the things really work on same table or not. If works how? – SSRaavi Feb 09 '15 at 09:14
  • Please edit your question to show us what you tried. Edit your question to specify which of the two databases you've tagged it for you're actually using. And edit it to specify what "not working" means. If you're getting an error, specify the error. If you're not getting the results you want, tell us what you're actually getting vs. what you want. – Justin Cave Feb 09 '15 at 13:06

1 Answers1

0

Yes, you did exactly correct, Change the column name [month] to some other name[months] then you will get the Result as you expected.

select id from table_name where months='May' and price='2000'
INTERSECT
select id from table_name where months='June' and price='3000'

select id from table_name where months='May' and price='2000'
EXCEPT
select id from table_name where months='June' and price='3000'
Nandha MV
  • 469
  • 4
  • 14