Oracle select, how to access values from 'where exists' subquery in PHP?

Question

I have a select in Oracle which has subquery. I need to access values from subquery in PHP code.

For example I am able to access F.FILE_NAME and F.ID, but I need to access MI.ID and MCI.ITEM_ID as well. How to do this?

    $select ="SELECT F.FILE_NAME, F.ID
                     FROM b_file F  where exists
                    (SELECT '1'from  b_medialib_collection_item MCI, b_medialib_item MI
                            WHERE MI.ID=MCI.ITEM_ID
                            AND F.ID=MI.SOURCE_ID
                    )";

I have updated my query, but the problem now is that F.FILENAME has duplicates in the table. I need to return row only once if there are rows which has the same F.FILENAME.

   $select2 = "SELECT
           F.FILE_NAME,MCI.COLLECTION_ID, MI.DATE_UPDATE,F.ID
           FROM b_medialib_collection_item MCI
            INNER JOIN b_medialib_item MI ON (MI.ID=MCI.ITEM_ID)
            INNER JOIN b_file F ON (F.ID=MI.SOURCE_ID)
           ORDER BY MI.DATE_UPDATE DESC";

Example

ID  FILE_NAME
1   module.xls
2   doc.doc
3   foto.img
4   doc.doc

This is the basic structure of b_file table, what I want to do is to return only one record from b_file table if there are many duplicates - rows with the same FILE_NAME. In this case output would be

1 module.xls
2 doc.doc
3 foto.img

If you want to use field from different table, you have to join them — Hearner, Aug 26 '15 at 08:58

Tharunkumar Reddy · Accepted Answer · 2015-08-26T12:52:44.403

2

you need to use join use below query

 SELECT F.FILE_NAME, F.ID as fid,MI.ID as mid,MCI.ITEM_ID
        FROM b_file F  
        inner join
        b_medialib_item MI
        on
        F.ID=MI.SOURCE_ID
        inner join
        b_medialib_collection_item MCI
        on 
        MI.ID=MCI.ITEM_ID
        where F.FILE_NAME in
        (select FILE_NAME from b_file group by FILE_NAME having count(FILE_NAME)>1)

edit Check with below query it will return the rows as you expected i guess

select FILE_NAME,fid, mid,ITEM_ID from 
(
SELECT F.FILE_NAME, F.ID as fid,MI.ID as mid,MCI.ITEM_ID, row_number() over(partition by FILE_NAME order by f.id ) as r_no
        FROM b_file F  
        inner join
        b_medialib_item MI
        on
        F.ID=MI.SOURCE_ID
        inner join
        b_medialib_collection_item MCI
        on 
        MI.ID=MCI.ITEM_ID)
        where r_no=1

edited Aug 26 '15 at 12:52

answered Aug 26 '15 at 08:58

Tharunkumar Reddy

2,773
18
32

1

Thanks, i updated my question. I don't need duplicate vlaues – MarciSM Aug 26 '15 at 09:01
@MarciSM i have updated my query please check it now. – Tharunkumar Reddy Aug 26 '15 at 09:09
Thank you for help, but this also does not work. This returns all rows wich has duplicate values – MarciSM Aug 26 '15 at 12:15
if you post the sample rows and required output will be more helpful to me. – Tharunkumar Reddy Aug 26 '15 at 12:16
as you mentioned "I need to return row only once if there are rows which has the same F.FILENAME" it means doc.doc only right? if i am wrong please give me more clarity – Tharunkumar Reddy Aug 26 '15 at 12:43
Do you want to select only filename or all columns ?? – Tharunkumar Reddy Aug 26 '15 at 12:46
For example, if there are 5 files wich have the same F.FILENAME then I need to return only 1 row, not all 5. – MarciSM Aug 26 '15 at 12:46
I guess you have to use partition by clause and select the latest record. – Tharunkumar Reddy Aug 26 '15 at 12:48
I checked your latest edit and it does not seem to work for me – MarciSM Aug 26 '15 at 13:06
My fault, I made a little mistake, now everything works great. Thank you very much! – MarciSM Aug 26 '15 at 13:47

Oracle select, how to access values from 'where exists' subquery in PHP?

1 Answers1