Foreign key

[yurykiselev]

Hi!
I have two indexes: patients and examination

patients: | id name | gender | date_of_birth |
examination: | user_id | exam_type |

How could I get a table of all examinations for males?
Thank you!

[niketn]

Is id_name the foreign key? i.e. can we correlate id_name and user_id?

If so you can create a field alias in one of the indexes to be the same as that in the other which is similar to,

| rename id_name as user_id

PS: Once you create Field Alias you will not need rename command.

(index=patient AND id_name=* gender="male") OR (index=examination AND user_id=*)
| rename id_name as user_id
| stats values(examination) as examinations by user_id

[gcusello · Accepted solution]

Hi yurykiselev,
there are many choices: join, transaction o stats:
join

index=examination
| rename user_id AS id_name
| join id_name type=left [ search index=patients | fields id_name gender date_of_birth ]
| table _time user_id gender date_of_birth exam_type 

transaction

index=examination OR  index=patients
| rename user_id AS id_name
| transaction id_name 
| table _time user_id gender date_of_birth exam_type 

stats

index=examination OR  index=patients
| rename user_id AS id_name
| stats values(gender) AS gender values(date_of_birth) As date_of_birth values(exam_type) AS exam_type by user_id 

Stats is the quickest.

Bye.
Giuseppe

[yurykiselev]

Thank you all!