Home » SQL & PL/SQL » SQL & PL/SQL » Challenging Query
Challenging Query [message #375723] Sat, 13 December 2008 11:17 Go to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Hi,

I need a query that will join two tables and provide me with data on if an entry in table one exists in table two or not. I need it to tell me how many times it is used in table two and if it is not used then it should return 0. Possible?
Re: Challenging Query [message #375724 is a reply to message #375723] Sat, 13 December 2008 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing challenging there.
But we don't do homework here but we can help to do them, you have to post what you aleady tried.

Before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste your tries.

Regards
Michel

[Updated on: Sat, 13 December 2008 11:34]

Report message to a moderator

Re: Challenging Query [message #375725 is a reply to message #375724] Sat, 13 December 2008 11:59 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Thanks for the reply, I am new to this forum,--

Here is a brief background on the problem---

I have two tables-- Tests and Test Sets , these are used in mercury quality center defect tracking tool.

My query is some thing like--select ts_name, count(ts_name), tc_cycle from test, test_cycl where ts_test_id = tc_test_id

Now this works well and gives me some result like--

Test1 TestSet1 2
Test1 Testset2 1

However I need it to tell me those test sets also where it i snot used: so finally my result will look like (assuming it si not used in test set 3)

Test1 Testset1 2
Test1 Testset2 1
Test1 Testset3 0

In general how do we get a value for a column when it does not exist in the other column (and no i am not talking about null values)

I will really appreciate your help.
Re: Challenging Query [message #375726 is a reply to message #375725] Sat, 13 December 2008 12:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Use an OUTER JOIN.
Re: Challenging Query [message #375727 is a reply to message #375725] Sat, 13 December 2008 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read JOINS in SQL Reference.

Regards
Michel
Re: Challenging Query [message #375728 is a reply to message #375727] Sat, 13 December 2008 12:40 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Ok, I will do that. But in general, will you be so gracious to provide me a query template that can solve this problem?

As an example, suppose there are two pies an apple pie and a banana pie. They use in between themselves 3 ingredients--Ingredient1, Ing2, and Ing3. These are represented by 2 tables Pies and Ingredients.

I write a query that tells me what quantity of each ingredinet is used in each pie:

so I have Select Ingredients, Pie, Count(Ingredients) from Pie, Ingredients where ..some conditions....

This query will return me something like this:

Apple PIE1 2
Banana PIE1 3
Apple PIE2 4

But suppose I also want to include the zeroes. So I want my result to look like----

Apple PIE1 2
Banana Pie1 3
Almonds PIE1 0
Apple Pie2 4
Banana Pie2 0
Banana Pie2 0


I understand the joins but I dont know how do I get the zero counts as well. This is very important for me..Help!!!
Re: Challenging Query [message #375729 is a reply to message #375728] Sat, 13 December 2008 12:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
When you use an outer join, it will add another row with a null value in the count. You can then wrap an NVL function around the count to show a 0 instead of a null value. If you will just read the section on outer joins first, you will see plenty of examples and it will become clearer. We generally avoid writing such simple queries for beginners like yourself as you will learn more by reading what we refer you to and writing it yourself. The purpose of this forum is not to do your work or homework for you. This is not a "challenging" query to anybody but a beginner.


Re: Challenging Query [message #375730 is a reply to message #375729] Sat, 13 December 2008 12:56 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Barbara,

I am going to try and use the outer join on my query---I may have some problems and I may take liberty in asking some more questions on this thread Smile
The thing I am most concerned about is that there are 3 columns in my query, the join may embed null values in one of the columns say the count but then I need a string value in the second column and not a null value..But i am going to try and play wiht this.

I appreciate your time
Re: Challenging Query [message #375731 is a reply to message #375730] Sat, 13 December 2008 12:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
As previously suggested, wrap an NVL function around the count to display null values as zeroes. You can find NVL in the online documentation as well.
Re: Challenging Query [message #375733 is a reply to message #375730] Sat, 13 December 2008 13:06 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
by the way can joins be only used for two tables or can they be used for more than two tables??
Re: Challenging Query [message #375735 is a reply to message #375733] Sat, 13 December 2008 13:09 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
I see a ray of light in using Partitioned Outer Joins

I hope it can help me!!
Re: Challenging Query [message #375736 is a reply to message #375733] Sat, 13 December 2008 13:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
More than two. In your case what you need is an OUTER JOIN. Notice the difference between the types of joins. With outer joins there are restrictions on how you can join more than two tables.
Re: Challenging Query [message #375740 is a reply to message #375736] Sat, 13 December 2008 14:16 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Barbara and Michael,

I used the outer partitioned join on two tables and I have reasons to believe that the monster of a problem that was staring at me has been solved 90%. I cannot find ways to thank you enough--you not only solved my problem but showed me a whole new process--in a way you taught me to leanrn fishing!!

I am thankful. I will be moving this to proudction and see how it goes there..I am a newbie so may have a few issues--please do help me if need be!!

Have a great day!
Re: Challenging Query [message #375741 is a reply to message #375740] Sat, 13 December 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a good and appreciated practice to post the solution you found as a matter of examples for future readers.
For instance:
SQL> select d.deptno, d.dname, nvl(count(e.deptno),0) nb_emp
  2  from dept d, emp e
  3  where e.deptno (+) = d.deptno
  4  group by d.deptno, d.dname
  5  order by d.dname
  6  /
    DEPTNO DNAME              NB_EMP
---------- -------------- ----------
        10 ACCOUNTING              3
        40 OPERATIONS              0
        20 RESEARCH                5
        30 SALES                   6

4 rows selected.

Regards
Michel

[Updated on: Sat, 13 December 2008 14:36]

Report message to a moderator

Re: Challenging Query [message #375745 is a reply to message #375741] Sat, 13 December 2008 16:26 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
My requirement was that I needed a count of test cases in all the test sets. I did not matter if the test case existed or not in the test set. Thanks to Michael and Barbara they pointed me to a reference on the outer joins and using the partitioned outer joins I could build a query to satisfy my requirement:

SELECT test.TS_name,COUNT(TC_STATUS), tc_cycle_id from testcycl
PARTITION BY (TC_CYCLE_ID)
RIGHT OUTER JOIN TEST ON tc_test_id = TS_TEST_ID

GROUP BY TS_name, TC_CYCLE_ID
order by ts_name, TC_CYCLE_ID
Re: Challenging Query [message #375750 is a reply to message #375745] Sun, 14 December 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pleaser the forum guide I mentionned above to learn how to format your post.
See the difference in the query between our posts.

In addition, I don't think your query is correct, see the difference if I convert it to my example:
SQL> select d.deptno, d.dname, count(e.deptno) nb_emp
  2  from emp e partition by (deptno)
  3         right outer join
  4       dept d
  5         on (e.deptno = d.deptno)
  6  group by d.deptno, d.dname
  7  order by d.dname
  8  /
    DEPTNO DNAME              NB_EMP
---------- -------------- ----------
        10 ACCOUNTING              3
        40 OPERATIONS              1
        20 RESEARCH                5
        30 SALES                   6

4 rows selected.

When there is no row, you get 1 instead of 0.

Regards
Michel

Re: Challenging Query [message #375776 is a reply to message #375750] Sun, 14 December 2008 08:52 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
I am curious why your row at the Operations is returning 1 instead of a 0 even though you did not use NVL. I checked my output and it looks correct. Again this is the query I used and the output I got--

SQL> select test.ts_name, count(tc_status), tc_cycle_id from
2 testcycl partition by (tc_cycle_id)
3 right outer join test
4 on tc_test_id = ts_test_id
5 group by ts_name, tc_cycle_id
6 order by ts_name, tc_cycle_id
7/

TS_NAME COUNT(TS_NAME) TC_CYCLE_ID
-------- --------------- ------------

Test1 Set1 1
Test1 Set2 3
Test1 Set3 0



Re: Challenging Query [message #375778 is a reply to message #375776] Sun, 14 December 2008 09:31 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 13 December 2008 18:33
Before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste your tries.

Regards
Michel


Third time I ask you in this topic.
Do it!
Previous Topic: where condition | how to form the condition?
Next Topic: What is the best way of forming a query?
Goto Forum:
  


Current Time: Sat Dec 03 17:56:42 CST 2016

Total time taken to generate the page: 0.05809 seconds