Inline View Qustion

Question

Perhaps someone can assist with designing a query to meet these requirements. I'm going to need this to be part of an inline view which will be joined to a larger query.

I have the basic table of email addresses:

EMAIL
jon@a.com
art@b.com

Then I have a table, called UPLOAD_HISTORY. Sample data:

EMAIL        FILETYPE   FILENAME
jon@a.com    1          file1.txt 
jon@a.com    2          filex.txt 
jon@a.com    4          fileb.txt

Then there is the table of file types:

FILE ID     FILE TYPE
   1         TYPE A
   2         TYPE B
   3         TYPE C
   4         TYPE D

If I perform a query, and outer join the UPLOAD_HISTORY and FILE_TYPES, I would get something like this:

EMAIL     FILETYPE     FILENAME
jon@a.com    1          file1.txt
jon@a.com    2          filex.txt
             3
jon@a.com    4          fileb.txt

What I need for the missing record, is to fill in the missing values from the UPLOAD_HISTORY table. My ideal result set would look like this:

EMAIL        FILETYPE   FILENAME      STATUS
jon@a.com    1          file1.txt     1
jon@a.com    2          filex.txt     1
jon@a.com    3                        0
jon@a.com    4          fileb.txt     1

I am looking to get all file types, an email for every line, and a status of 1 if there is a record for that file type, or 0 if there is not........

Sometimes there may be criteria passed. I may ask for a specific file type, or not.

If a particular email does not have any entries in the UPLOAD_HISTORY table, is it possible to get empty records?

EMAIL        FILETYPE   FILENAME     STATUS
jon@a.com    1          file1.txt     1
jon@a.com    2          filex.txt     1
jon@a.com    3                        0
jon@a.com    4          fileb.txt     1
art@b.com    1                        0
art@b.com    2                        0
art@b.com    3                        0
art@b.com    4                        0

However, I could ask for just File Type 1:

EMAIL     FILETYPE     FILENAME    STATUS
jon@a.com    1          file1.txt     1
art@b.com    1                        0

Thanks

Ask one question a time. Please confirm which output do you actually want to see and restrict this question to that one only. — Kaushik Nayak, Jul 22 '18 at 05:41
Please don't use HTML tags when posting code. The markdown for code is much simpler, works nicely with the StackOverflow renderer and is easier to fix. To format a code block use the `{}` button on the editor menu or simply indent each line by four spaces. For inline use backticks `\`` — APC, Jul 22 '18 at 06:57

score 0 · Answer 1 · answered Jul 21 '18 at 23:59

0

Left join from file types to history:

select EMAIL, FILETYPE, FILENAME, nvl(STATUS, 0) as STATUS
from file_type
left join upload_history on FILETYPE = FILE_ID

answered Jul 21 '18 at 23:59

Bohemian

412,405
93
575
722

Unfortunately this query is still omitting the missing file types. – Landon Statis Jul 22 '18 at 00:09
1

@LandonStatis impossible. This query is guaranteed to return all file types. – Bohemian Jul 22 '18 at 00:26

score 0 · Answer 2 · answered Jul 22 '18 at 06:43

Use a CROSS JOIN on email and file_type to get all the combinations, then LEFT OUTER JOIN on upload_history to get the results you want.

Assuming there is at most one record in upload_history for each combination this should work nicely:

select eft.email
       , eft.filetype
       , uh.filename
       , nvl2(uh.filename, 1, 0) as status
from   ( select email, file_id, filetype
         from email 
         cross join file_type ) eft
left outer join upload_history uh
    on uh.email = eft.email
    and uh.filetype = eft.filetype
order by eft.email
       , eft.filetype

You haven't specified what you want to happen if there are multiple records in upload_history for each combination. Maybe one row per filename is correct. But the purpose of the status column seems a bit redundant.

Yes, there can be multiple record in UPLOAD_HISTORY. But the reason for status is: if the particular email has no records in UPLOAD_HISTORY, we will want it returned, just with the email value and status, which the application will use. 1 = data, 0 = no data. But, I'll check out this query you provided. — Landon Statis, Jul 22 '18 at 15:17

Inline View Qustion

2 Answers2