find information in other table

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?
LVL 1
joyacv2Asked:
Who is Participating?
 
GaryConnect With a Mentor Commented:
$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
 
Ray PaseurCommented:
Please post the CREATE TABLE statements for the table(s) you want to use in the query, thanks.
0
 
joyacv2Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
joyacv2Author Commented:
i want to do a vlookup excel function for the distrito field
0
 
joyacv2Author Commented:
don't work gary, i think that something needs to have a relationship. any other idea?
0
 
Ray PaseurCommented:
Learn about SHOW CREATE TABLE queries.  I'm sure there is some analog in Oracle.
0
 
joyacv2Author Commented:
I found the answer add inner join to the gary statement and problem solved! Thanks to all!
0
 
slightwv (䄆 Netminder) Commented:
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
 
joyacv2Author Commented:
thanks for the link!
0
 
slightwv (䄆 Netminder) Commented:
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
 
joyacv2Author Commented:
perfect!
 thanks again!!!
0
All Courses

From novice to tech pro — start learning today.