Forum Stats

  • 3,838,112 Users
  • 2,262,333 Discussions
  • 7,900,511 Comments

Discussions

Select only records with same Id

597990
597990 Member Posts: 7
edited Sep 14, 2007 10:54AM in SQL & PL/SQL
Hi there,

I am using Oracle 10 and I have the following table:
Id    Name            Region   
------------------------------
1 Acer 3
1 Betula 1
1 Corylus 2
1 Fagus 1
1 Fagus 2
1 Fagus 3
2 Alnus viridis 1
3 Corylus 1
4 Acer 7
5 Fagus 4
6 Acer 6
6 Fagus 9
7 Fagus 8
8 Acer 2
I want to select all rows where Name equals "Acer" or "Fagus" but I want only get records with same Id.
My result should read like this:
Id    Name            Region   
------------------------------
1 Acer 3
1 Fagus 2
1 Fagus 3
6 Acer 6
6 Fagus 9
How do I need to write my SQL Query?

I tried using UNION but this is a real performance killer.
Here is what I tried:
 SELECT Id, Name, Region
FROM mydatenview
WHERE
Name = 'Acer'
AND Region = 3
AND Id IN (SELECT Id
FROM mydatenview
WHERE
(Name = 'Fagus'
AND Region = 1)
)
UNION
SELECT Id, Name, Region
FROM mydatenview
WHERE
(Name = 'Fagus'
AND Region = 1)
AND Id IN (SELECT Id
FROM mydatenview
WHERE
(Name = 'Acer'
AND Region = 3)
)
ORDER BY Id, Name
Thank you in advance for tips.


Regards,
Nico
«13

Comments

  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Your requirement is not clear for me. Please, explain why you don't get all the three records for "Fagus" where id=1.

    Nicolas.
  • jeneesh
    jeneesh Member Posts: 7,168
    If its a typo..
    SQL> select * from test;

    ID NAME REGION
    ---------- -------------------- ----------
    1 Acer 3
    1 Betula 1
    1 Corylus 2
    1 Fagus 1
    1 Fagus 2
    1 Fagus 3
    3 Corylus 1
    4 Acer 7
    5 Fagus 4
    6 Acer 6
    6 Fagus 9

    ID NAME REGION
    ---------- -------------------- ----------
    7 Fagus 8
    8 Acer 2

    13 rows selected.

    SQL> select id,name,region
    2 from (select id,name,region,
    3 count(*) over(partition by id) cnt
    4 from test)
    5 where name in ('Acer','Fagus')
    6 and cnt > 1
    7 /

    ID NAME REGION
    ---------- -------------------- ----------
    1 Acer 3
    1 Fagus 1
    1 Fagus 2
    1 Fagus 3
    6 Acer 6
    6 Fagus 9

    6 rows selected.
  • 597990
    597990 Member Posts: 7
    @ N.Gasparotto

    Indeed, it was a typo. Sorry for that.
    The result should be:

    Id Name Region
    ------------------------------
    1 Acer 3
    1 Fagus 1
    1 Fagus 2
    1 Fagus 3
    6 Acer 6
    6 Fagus 9
  • 388131
    388131 Member Posts: 1,819
    Well, then have a look at jeneesh's answer.
  • 597990
    597990 Member Posts: 7
    edited Sep 14, 2007 3:57AM
    Thank you very much, jeneesh. It seems to work.


    By the way: How do I mark text in postings as code?

    Message was edited by:
    user594987
  • jeneesh
    jeneesh Member Posts: 7,168
    edited Sep 14, 2007 3:55AM
    Thank you very much, jeneesh. It seems to work.
    But You have to check. Depending upon your need WHERE codition may need to be in the inner query.
    Is a wildcard search with LIKE '%acer%' also
    possible?
    Yes, ofcourse
    If so, how must I change the WHERE clause where name
    in ('Acer','Fagus')?
    where name like '%acer%'
    By the way: How do I mark text in postings as code?
    Enclose in [pre] and [/pre] tags
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    How do I mark text in postings as code?
    [pre] and [/pre] around your code.

    Nicolas.
  • 388131
    388131 Member Posts: 1,819
    edited Sep 14, 2007 4:00AM
    As far as I understood, OP wants to get all rows
    where the id is present for every name in the where clause.
    When you use wildcards, how will you know that all names have the appropriate id?
  • 597990
    597990 Member Posts: 7
    You're right, Guido.

    The query seems to be not correct yet.

    I run the query on my full table containing 40.000 records and I also get records where name = Fagus and Id = 8 but there are no records where name = Acer and Id = 8. Therfore, I don't wanna get records where name = Fagus and Id = 8 if no Acer records with Id 8 are present.
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Just an additional question : what should happens if you have twice the same name for the same id, but not the other name ? Do you want to select it, or not ?
    Example :
    SQL> create table mydatenview as
    2 select 1 id, 'Acer' name, 3 region from dual union all
    3 select 1 id, 'Betula' name, 1 region from dual union all
    4 select 1 id, 'Corylus' name, 2 region from dual union all
    5 select 1 id, 'Fagus' name, 1 region from dual union all
    6 select 1 id, 'Fagus' name, 2 region from dual union all
    7 select 1 id, 'Fagus' name, 3 region from dual union all
    8 select 2 id, 'Alnus viridis' name, 1 region from dual union all
    9 select 3 id, 'Corylus' name, 1 region from dual union all
    10 select 4 id, 'Acer' name, 7 region from dual union all
    11 select 4 id, 'Acer' name, 8 region from dual union all

    12 select 5 id, 'Fagus' name, 4 region from dual union all
    13 select 6 id, 'Acer' name, 6 region from dual union all
    14 select 6 id, 'Fagus' name, 9 region from dual union all
    15 select 7 id, 'Fagus' name, 8 region from dual union all
    16 select 8 id, 'Acer' name, 2 region from dual ;

    Table created.

    SQL>
    SQL> select id,name,region
    2 from (select id,name,region,
    3 count(*) over(partition by id) cnt
    4 from mydatenview)
    5 where name in ('Acer','Fagus')
    6 and cnt > 1;

    ID NAME REGION
    ---------- ------------- ----------
    1 Acer 3
    1 Fagus 1
    1 Fagus 2
    1 Fagus 3
    4 Acer 7
    4 Acer 8

    6 Acer 6
    6 Fagus 9

    8 rows selected.

    SQL>
    SQL> select id, name, region
    2 from (select id, name, region,
    3 count(distinct name) over (partition by id) ct
    4 from mydatenview
    5 where name in ('Acer','Fagus'))
    6 where ct > 1;

    ID NAME REGION
    ---------- ------------- ----------
    1 Acer 3
    1 Fagus 1
    1 Fagus 2
    1 Fagus 3
    6 Acer 6
    6 Fagus 9

    6 rows selected.
    Nicolas.
This discussion has been closed.