Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
-
Joana
- Posts: 2
- Joined: Tue Jan 30, 2024 8:39 pm
Post
by Joana » Tue Jan 30, 2024 9:12 pm
I am using the patstat 2023 autumn version. There I have identified a group of patents (my core dataset; the associated appln_ids are stored in table 'cc_core_dataset'). I am now interested in getting the appln_ids of those patents that have been cited by the patents in the core dataset (i.e. my focus is on backward citations). The idea is to get second dataset based on the core dataset.
I am not sure if my query is correct and I would be happy to get advice. Also, any hint on mistakes or ways to improve my query are very much appreciated. I am also not sure if it makes sense to add a time restriction regarding backward citations (in case of forward citations I find that reasonable, though). Thanks a lot.
This is my query (inspired from here://forums.epo.org/patent-citation-backward-citation-7788):
Code: Select all
SELECT DISTINCT citing.appln_id citingPatentApplnID
, cited.appln_id citedPatentApplnID
, citing.publn_auth + citing.publn_nr AS citingPublNumber
, cited.publn_auth +cited.publn_nr AS citedPublNumber
, citeda.appln_filing_year
, j.citn_origin
FROM patstat2023.tls211_pat_publn AS citing
JOIN patstat2023.tls212_citation AS j ON citing.pat_publn_id = j.pat_publn_id
JOIN patstat2023.tls211_pat_publn AS cited ON j.cited_pat_publn_id = cited.pat_publn_id
JOIN patstat2023.tls201_appln AS citinga ON citing.appln_id=citinga.appln_id
JOIN patstat2023.tls201_appln AS citeda ON cited.appln_id=citeda.appln_id
JOIN patstat2023.tls202_appln_title t ON citinga.appln_id = t.appln_id
JOIN patstat2023.tls203_appln_abstr abstr ON citinga.appln_id = abstr.appln_id
JOIN cc_core_dataset ON cc_core_dataset.appln_id = citinga.appln_id
WHERE cc_core_dataset.appln_id = citinga.appln_id
-
EPO / PATSTAT Support
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
-
Contact:
Post
by EPO / PATSTAT Support » Wed Jan 31, 2024 3:33 pm
Your query is correct, the best way to check results is to look at one single application (all publications),
and see whether the result is the same as what you can see in ESPACENET or the EPO register. (small differences are possible, but it should be sufficient to confirm that all your JOINS in SQL are correct.
Instead of using your list, change your query: WHERE citinga.appln_id = 17397285, to have a look at the citations in EP1000000.
See espacenet :
https://worldwide.espacenet.com/patent/ ... P1000000A1
See EP regsiter:
https://register.epo.org/application?nu ... =citations
Results are the same --> query is ok (sometimes you want to text a couple to be sure.)
Whether or not this is the best depends entirely on what you will use the results for. And I am not sure whether a time frame limitation would provide extra insights or filter out any noise. Examiners are free to cite any document they want, whether it's 10 years old or 1 month, doesn't change anything. (at least not for novelty / patentability searches). But cited documents during opposition procedures or third part observations can of cours give a different dimension. (depends on the nature of your research...)
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org
-
sarausa
- Posts: 1
- Joined: Tue Feb 20, 2024 4:51 am
Post
by sarausa » Tue Feb 20, 2024 4:56 am
In your query you join only patents from the core dataset (cc_core_dataset) with itself, this will return only patents from the core dataset without any back citations. What you want is to get the patents that are cited in the core dataset. This can be done by joining the core dataset with the tls212_citation citation
Suika game table.
-
alicebobby
- Posts: 2
- Joined: Wed Dec 27, 2023 9:40 am
Post
by alicebobby » Wed Feb 21, 2024 3:59 am
You are joining only the core dataset's (cc_core_dataset) patents with itself in your query, which will only return core dataset patents devoid of back citations. Obtaining the patents that are cited in the core dataset is what you desire. The tls212_citation citation table can be joined with the core dataset to achieve this.
tunnel rush
-
Joana
- Posts: 2
- Joined: Tue Jan 30, 2024 8:39 pm
Post
by Joana » Tue Mar 05, 2024 10:21 am
Thanks for your feedback!
Can you provide me with the relevant code for joining the tls212_citation with my core dataset? Shall I replace the last JOIN? Or do I have to add an additional JOIN?
Put differntly: how do I need to modify my code?
Thanks in advance.
-
Gambino
- Posts: 1
- Joined: Tue Apr 02, 2024 7:56 am
Post
by Gambino » Tue Apr 02, 2024 8:00 am
When constructing your query, it's essential to focus beyond just the primary dataset, known as cc_core_dataset, which, by default, merges only with itself, yielding patents solely from this core group without their respective backward citations. To enrich your analysis by including patents referenced within the core collection, you should aim to merge the core dataset with the tls212_citation citation
cool math games table. This approach ensures a comprehensive view by capturing both direct and indirect patents cited within your primary dataset.
-
turcotte2
- Posts: 1
- Joined: Mon Aug 12, 2024 11:04 am
Post
by turcotte2 » Mon Aug 12, 2024 11:09 am
You noted that there may be small differences between the results shown on the EPO register versus what is available in ESPACENET. What types of differences might one encounter, and why is it important to cross-reference the information across these different patent databases?
-
fairlyhistorian
- Posts: 1
- Joined: Mon Sep 09, 2024 9:03 am
Post
by fairlyhistorian » Tue Oct 15, 2024 5:11 am
Let's focus on your requirement to identify backward citations for patents in your core dataset. Your core dataset consists of patents with `appln_ids` stored in the table `cc_core_dataset`. You need to extract `appln_ids` of the patents that are cited by these patents, which typically reside in the citations data table.
-
hannah1207
- Posts: 1
- Joined: Tue Nov 05, 2024 9:05 am
Post
by hannah1207 » Tue Nov 05, 2024 9:08 am
I still don't get it, can someone pls explain more specifically? Really appreciate
-
lilycollins6868
- Posts: 8
- Joined: Thu Aug 11, 2022 2:47 am
Post
by lilycollins6868 » Thu Nov 21, 2024 3:42 am
It was great to see Joana actively seeking help with her PATSTAT query! Understanding back-citations can be tricky, and her willingness to tweak her code for better results is commendable. This collaborative approach to data analysis is key to getting the most out of patent databases
1v1 lol