| required query [message #187574] |
Mon, 14 August 2006 09:14  |
ganeshvadivel
Messages: 2
Registered: August 2006
|
Junior Member |
|
|
Iam having 2 tables:
Table1:
Name----Level
aa----1
bb----2
cc----1
dd----3
Table2:
Name----Level
xx----1
yy----2
zz----1
Now i want count total number of name's in both tables having level=1
Let me know ,how to make the query.
regards
ganesh
|
|
|
|
| Re: required query [message #187581 is a reply to message #187574] |
Mon, 14 August 2006 09:36  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Something like:
(untested)
SELECT count(*) FROM
(SELECT name FROM table1 WHERE level=1
INTERSECT
SELECT name FROM table2 WHERE level=1)
Given the test data you posted, this will return 0, but it seems to match the description of your problem
|
|
|
|
| Re: required query [message #187589 is a reply to message #187574] |
Mon, 14 August 2006 10:17 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
and if the answer you expect is 4 for the data that you mentioned then replace the keyword
intersect
with
Union all
Jim
|
|
|
|
| Re: required query [message #187709 is a reply to message #187589] |
Tue, 15 August 2006 01:50 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Good point - it's a pretty ambiguous way of phrasing the question.
I wonder if we'll ever find out which was the correct answer?
|
|
|
|
|
|
| Re: required query [message #187776 is a reply to message #187581] |
Tue, 15 August 2006 10:17 |
ganeshvadivel
Messages: 2
Registered: August 2006
|
Junior Member |
|
|
(untested)
SELECT count(*) FROM
(SELECT name FROM table1 WHERE level=1
INTERSECT
SELECT name FROM table2 WHERE level=1)
Thanks for reply,
but it is not working.
regards,
ganesh
|
|
|
|
| Re: required query [message #187866 is a reply to message #187776] |
Wed, 16 August 2006 01:45 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Here we go again.
| Quote: |
but it is not working.
|
Give us a chance here. Give us the information we are OBVIOUSLY going to need if we're going to help you.
Tell us WHY it isn't working. What error message do you get when you run it?
If you think we've misunderstood what you want then give us more information, give us examples of what you want, give us test data.
Heck, just give us the things that the Sticky at the top of the group tells you to when posting a new question!!!
Anyway, here is a little demo showing that the query posted does work as does @JSI2001's variation. It may may do what you want, but if that's the case, that's more down to the level of detail you provided in your specification than anything else.
SQL> create table table1 (name varchar2(10), lvl number);
Table created.
SQL> create table table2 (name varchar2(10), lvl number);
Table created.
SQL> insert into table1 values ('aa',1);
1 row created.
SQL> insert into table1 values ('bb',2);
1 row created.
SQL> insert into table1 values ('cc',1);
1 row created.
SQL> insert into table1 values ('dd',3);
1 row created.
SQL> insert into table2 values ('xx',1);
1 row created.
SQL> insert into table2 values ('yy',2);
1 row created.
SQL> insert into table2 values ('zz',1);
1 row created.
SQL> SELECT count(*) FROM
2 (SELECT name FROM table1 WHERE lvl=1
3 INTERSECT
4 SELECT name FROM table2 WHERE lvl=1);
COUNT(*)
----------
0
SQL> SELECT count(*) FROM
2 (SELECT name FROM table1 WHERE lvl=1
3 UNION ALL
4 SELECT name FROM table2 WHERE lvl=1);
COUNT(*)
----------
4 |
|
|
|
| Re: required query [message #187882 is a reply to message #187776] |
Wed, 16 August 2006 03:49 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Can I also point out a few things.
1. It's probably not a great idea to have a column named level as this is a reserved word in Oracle (It's the name of a pseudocolumn)
2. You post reads
| Quote: |
(untested)
SELECT count(*) FROM
(SELECT name FROM table1 WHERE level=1
INTERSECT
SELECT name FROM table2 WHERE level=1)
|
If it's untested (as you say at the top of your post) then how do you know that it doesn't work??
3. Just to reiterate JRowbottom.
One of the most frustrating things about these forums is when it takes 5 or 6 posts just to find out exactly what it is that the Original Poster (OP) is actually looking for. This happens when the OP over simplifies the situation in the example that they give or doen't give an exmple at all.
It's also got to be frustrating for the OP.
Resolution: Provide create table and insert scripts along with expected results, your own attempts, error messages, version of Oracle.
This means that Others will know exactly what you need.
Advantages:
1. More accurate answers.
2. Quicker answers
3. A greater likliehood that people will offer several different approaches to get the required result, some better than other under certai circumstances.
4. The gratitude of the more prolific and skillful experts (note I am not including myself in that lot  ) which means that they are more likely to put etra effort in to helping you solve this and future issues.
It's worth you putting the effort in, others will appreciate it and you will reap the rewards.
Jim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
