Using the API How can i get “file” attachment. I am able to get “attachment” correctly

I used the Salesforce UI to add a File to an Opportunity. Then checked the resulting ContentDocumentLink records with SQOL.

Select Id,LinkedEntityId,ContentDocumentId,IsDeleted,ShareType,Visibility from ContentDocumentLink where ContentDocumentId = '0694000000548HHAAY'

Note how there are two records, one to the Opportunity and one to my user record (this will be why you can't see it in your private library). The one to the Opportunity has the ShareType V (Viewer permission) and Visibility AllUsers

If you have a relatively small number of types that you want to find the related content for, a query of this form for each type (Account, Contact, ...):

ContentDocumentLink[] cdls = [
    select LinkedEntityId, LinkedEntity.Name
    from ContentDocumentLink
    where LinkedEntityId in (select Id from Account)
    order by LinkedEntity.Name
    limit 1000
];

will work. The two queries are combined, allowing the platform's query optimiser to remove redundant work.

You can then combine the results using Apex code and sort as you wish in that Apex code.

Note that this construct that could avoid the need for Apex code:

    where LinkedEntityId in (select Id from Account)
    or LinkedEntityId in (select Id from Contact)

is not allowed as it results in this error:

Semi join sub-selects are not allowed with the 'OR' operator