Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

find information in other table

Posted on 2014-03-07
11
Medium Priority
?
357 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
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 111

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
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!

 
LVL 58

Accepted Solution

by:
Gary earned 2000 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 111

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 78

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 78

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

879 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