Solved

find information in other table

Posted on 2014-03-07
11
352 Views
Last Modified: 2014-03-07
Hi,

I have the following query:

$stid = oci_parse($conn, "SELECT fer, distrito, count(fer),COUNT(CASE WHEN PARC_TOT = 'P' THEN 1 END) COL_P,COUNT(CASE WHEN PARC_TOT = 'T' THEN 1 END) COL_T,COUNT(CASE WHEN (CAUSAS LIKE '%RECE%' OR  CAUSAS LIKE '%RECI%') AND PARC_TOT = 'T' THEN 1 END) COL_REC  FROM INR WHERE FECHA_REC BETWEEN TO_DATE('2014-01-01','YYYY-MM-DD') AND TO_DATE('2014-02-01','YYYY-MM-DD') GROUP BY fer, distrito ORDER BY count(fer) DESC");

The distrito is a number, but I have a table that contains that table with the name. The table is like that


distrito   name
1               lert
2               bcs
3               dffg

how i can change this query to use distrito name and not the number?
0
Comment
Question by:joyacv2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39913881
Please post the CREATE TABLE statements for the table(s) you want to use in the query, thanks.
0
 
LVL 1

Author Comment

by:joyacv2
ID: 39913908
Hi,

I don't have the create table statements, because the tables are already created and i don't have the code, i only have access to the tables, sorry about that
0
 
LVL 1

Author Comment

by:joyacv2
ID: 39913914
i want to do a vlookup excel function for the distrito field
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 39913919
$stid = oci_parse($conn, "SELECT fer, name, count(fer),COUNT(CASE WHEN PARC_TOT = 'P' THEN 1 END) COL_P,COUNT(CASE WHEN PARC_TOT = 'T' THEN 1 END) COL_T,COUNT(CASE WHEN (CAUSAS LIKE '%RECE%' OR  CAUSAS LIKE '%RECI%') AND PARC_TOT = 'T' THEN 1 END) COL_REC  FROM INR WHERE FECHA_REC BETWEEN TO_DATE('2014-01-01','YYYY-MM-DD') AND TO_DATE('2014-02-01','YYYY-MM-DD') GROUP BY fer, name ORDER BY count(fer) DESC");

Open in new window

0
 
LVL 1

Author Comment

by:joyacv2
ID: 39913931
don't work gary, i think that something needs to have a relationship. any other idea?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39913985
Learn about SHOW CREATE TABLE queries.  I'm sure there is some analog in Oracle.
0
 
LVL 1

Author Comment

by:joyacv2
ID: 39913989
I found the answer add inner join to the gary statement and problem solved! Thanks to all!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39913994
If the number in one table is a lookup value in another table that has a value you want, it is a simple table join.

We would need actual table definitions, sample data and expected results for us to write the exact SQL statement you need.

If you are not familiar with joins:
http://en.wikipedia.org/wiki/Join_%28SQL%29
0
 
LVL 1

Author Comment

by:joyacv2
ID: 39913999
thanks for the link!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39914011
Focus on the INNER JOIN unless you can have values in the one table that don't exist in the other (then it is an OUTER JOIN).
0
 
LVL 1

Author Comment

by:joyacv2
ID: 39914023
perfect!
 thanks again!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question