Solved

find information in other table

Posted on 2014-03-07
11
346 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 108

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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 108

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This article discusses how to create an extensible mechanism for linked drop downs.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 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

18 Experts available now in Live!

Get 1:1 Help Now