SQL query to retrieve data from multiple rows

advertisements

Hi I have the requirement to traverse three different tables of Oracle database and fetch data.

I will pass countyId to get all zipCodes from ZIP table. Based on the number of zipCodes retrieved from ZIP table, I will have pass each zip code to verify it in another table. Based on this zipCode the ACTIVITY table will return the rows of that zipCode.

How could I write the SQL query to achieve this. I heard virtual table creation concept is there but I am non-knowledgeable on that.

Please provide me high level view, How could I proceed...?

I tried the below query, but no results are retrieved - 0 rows

select T_ZIP5.zip,
  T_DTV_CNTY_ELIGIBILITY.DTV_eligible,
  T_USER_ACTIVITY.cuid,
  T_USER_ACTIVITY.actvty_date
from T_ZIP5
join T_DTV_CNTY_ELIGIBILITY
  on T_ZIP5.FIPS_CNTY=T_DTV_CNTY_ELIGIBILITY.CNTY_FIPS
join T_USER_ACTIVITY
  on T_DTV_CNTY_ELIGIBILITY.zip_code=T_USER_ACTIVITY.zip
where T_ZIP5.FIPS_CNTY='53033'

Table Structures:

desc t_zip5

----------------- -------- ------------
ZIP               NOT NULL VARCHAR2(5)
FIPS_CNTY                  VARCHAR2(5)
CITY_NAME         NOT NULL VARCHAR2(50)
STATE             NOT NULL VARCHAR2(2)
RATE_CENTER_NAME           VARCHAR2(10)
RATE_CENTER_STATE          VARCHAR2(2)

desc T_DTV_CNTY_ELIGIBILITY

------------- -------- ------------
CNTY_FIPS     NOT NULL VARCHAR2(5)
ZIP_CODE      NOT NULL VARCHAR2(5)
DTV_ELIGIBLE           CHAR(1)
USER_MODIFIED          VARCHAR2(10)
CREATED_DATE           DATE
MODIFIED_DATE          DATE

desc T_USER_ACTIVITY

----------- ---- -------------
ACTVTY_DATE      DATE
ACTVTY_NAME      VARCHAR2(20)
ACTVTY_DSCR      VARCHAR2(200)
CUID             VARCHAR2(10)
ZIP              VARCHAR2(5)

53033 is countyCode

The Zip for county is present in T_USER_ACTIVITY table. But there are multiple zip entries in T_ZIP5 table for the given county 53033.

You can use the join query for this. Suppose you have three table 1. country (country_id) 2. zip (zip_id,country_id,zipcode) 3. test (test_id,zipcode);

So query like

SELECT zip.zipcode
  FROM country
    JOIN zip on zip.country_id = country.country_id
    JOIN test on test_id.zipcode = zip.test_id
  Where country.country_id = 10

Here you can use left,right join depend on your record need and you zipcode will be one entry other wise you get result as multiple records.

Related Articles

SQL query to retrieve data from three different tables

I have to create a SQL query to list all the Nurses in the 'Sparrow' Wing ordered by last name, first name. However, I need to pull Nurse_name and Nurse_surname from the Nurse table, which is linked to another table called Sister by the foreign key S

SQL query to retrieve data from another table

I've been trying to build a little complex SQL query to fetch some keys from a table which has no relation with the one in which I need to "ask". Practical case: I have four tables: users, devices, preferences and user_devices. Table users, ther

SQL Query to retrieve data from the same column?

This question already has an answer here: MySQL pivot table 8 answers How can i fetch data from a single column according to their respective data_id for a and group_id ? -------------------------------------------- id | group_id | data_id |value ---

SQL query to get data from two rows connected by key

I have a question that seems very simple to me at first but I'm relatively new to SQL and I can't solve it. I have two tables: 'Applicants' and 'Family Units'. Applicants: ApplicantID | FirstName ----------- | ---------- 1 | John 2 | Mary Family Unit

Sql query to retrieve data from two tables

There are two tables as below employee table: empid, empname, deptid department table: deptid, deptname then write query to "list all employees for dept name=computer"this should be pretty straight forward, SELECT a.empid, a.empname, b.deptname

Complex SQL Query: Displays online data from multiple tables

Hi i have a proble in finding solution. I have 3 tables: Customer customer_id customer_name Relation subscription_id customer_id Subscriptions One customer can have relation with many subscriptions and i want to display data like so: customer_id, cus

SQL query to retrieve data from 2 tables

can you help me with something rather easy that my head just can't solve I have 2 tables: table 1 = movies table 2 = actors Let's say I have 3 movies: Independence Day, Reservoir Dogs and Forrest Gump. I also have 7 actors: Will Smith, Jeff Goldblum,

SQL Query to retrieve data from last 30 days?

Hi I am new to Oracle. How do I do a simple statement, for example get product id from the last 30, or 20 days purchase date? SELECT productid FROM product WHERE purchase_date ? SELECT productid FROM product WHERE purchase_date > sysdate-30

How do I retrieve data from multiple tables

I need to retrieve data from multiple tables, with a dynamically built filter that might or might not use data from any of the tables. So say I have this: class Solution(models.Model): name = models.CharField(max_length=MAX, unique=True) # Other data

Query to retrieve data from sqlite using = operator

I am using a query to retrieve data from sqlite but I am getting an error. I am using following query: cursor = db.query ( TABLE_NAME, new String[] { EMPLOYEE }, USERID + " = " + NAME, null, null, null, null ); db.query(TABLE_NAME, new String[]