Solved

find information in other table

Posted on 2014-03-07
11
349 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Make check boxes work 8 41
mysql qry 1 18
deprecated mysql extensions 1 22
How do I Enable submit button only if listbox has items 4 27
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

685 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