Solved

find information in other table

Posted on 2014-03-07
11
347 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 109

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 109

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 76

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 76

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
This article discusses four methods for overlaying images in a container on a web page
This video shows how to recover a database from a user managed backup
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.

816 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now