Home » SQL & PL/SQL » SQL & PL/SQL » required query
required query [message #187574] Mon, 14 August 2006 09:14 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #187716 is a reply to message #187709] Tue, 15 August 2006 02:37 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

I wonder if we'll ever find out which was the correct answer?


I doubt it.
Re: required query [message #187776 is a reply to message #187581] Tue, 15 August 2006 10:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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 Smile ) 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

Previous Topic: How to find database name from sqlplus prompt
Next Topic: scripts - export info party(customer)
Goto Forum:
  


Current Time: Fri Dec 09 15:35:31 CST 2016

Total time taken to generate the page: 0.24296 seconds