OraFAQ Forum: SQL & PL/SQL » Table Join Query?

Home » SQL & PL/SQL » SQL & PL/SQL » Table Join Query?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table Join Query? [message #21620]

Mon, 19 August 2002 01:10

Mike
Messages: 417
Registered: September 1998

Senior Member

I have a 2 tables - stations and nodes which are as follows

STATIONS
NAME
----
DEL
PAR
LON

NODES
NAME ROUTE
---- -----
DEL 1
PAR 1
BMF 1
LON 1
DEL 2
PAR 2
NYC 3
LON 3
.......etc.

Now I want to select those unique route values from Nodes table which contain ALL the name values of Stations table.

(In the above example I should get 1 because the group of names which have route value 1, contain all the name values of the stations table).

What query should I write?

Thanks,
Mike

Report message to a moderator

Re: Table Join Query? [message #21627 is a reply to message #21620]

Mon, 19 August 2002 09:06

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Since you have nodes with stations that do not exist in your stations table, we have to filter those out first. Then we look for nodes that have a number of stations equal to the number of stations.

sql>select * from stations;
 
NAM
---
DEL
PAR
LON
 
sql>select * from nodes;
 
NAM     ROUTE
--- ---------
LON         3
NYC         3
PAR         2
DEL         2
LON         1
BMF         1
PAR         1
DEL         1
 
8 rows selected.
 
sql>select route
  2    from (select route
  3            from nodes
  4           where name in (select name
  5                            from stations))
  6   group by route
  7   having count(*) = (select count(*)
  8                        from stations);
 
    ROUTE
---------
        1

Report message to a moderator

Previous Topic:	passing index-by tables to stored procedure
Next Topic:	trigger errors

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 00:31:49 CDT 2024