[Solved] Query between 2 tables

[flamen]

sorry but I.m desperate...
I.m a beginner. The issue is this one (simplified)

I've got 2 tables: table1 with 1 key and a table2 (which is also a subform) with 4 primary keys.

I have established a relationship between STUDYID in table1 and STUDYID in table 2 (which are the same).

Then I would like a query that gives me TAble 1 fields repeated depending on whether Table2 contents have been entered or not, LInked by STUDYID. INSTEAD, when I do a query IT gives me absolutely all values of studyID of table1 and then it repeats the values of table2 again and again.

I would like only those STUDies of table1 which studyID in table2 (subform) have been entered. You understand what I mean? I have just started using this and I.ve already spent hours but cannot find the solution by myself. THANK you very much in advance. I am attaching screenshots

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[FJCC]

I think you want something like

Code: Select all

SELECT * FROM "Main table" WHERE "Main table"."STUDYID" IN (SELECT DISTINCT "studyID" FROM "subform")

[flamen]

Hey, thanks. I think that I have not explained myself properly, but yes. That code selects only those records for which subforms have been entered which would be studyID=1 and studyid=2

STUDYid=1 has 3 other entries in the subform (table2) and studyID=2 Has a single entry in the subform (table2)

What I want is a table that gives me:
StudyID=1 (all variables *) and then 1 different record for each of the entries in the subform(table2) . This would mean that STUDYID=1 (table1) would be repeated 3 times followed by the table2 fields.

[flamen]

HEY!! thanks. I have added a little something and now I.ve got what I wanted.

SELECT "Main_table".*, "Subform".* FROM "Subform", "Main_table" WHERE "Subform"."studyID" = "Main_table"."STUDYID" AND "Main_table"."STUDYID" IN ( SELECT DISTINCT "studyID" FROM "Subform" )

Sorry but I have just started and I learn as I go. Thanks very much

[FJCC]

Try

Code: Select all

SELECT * FROM "Main table" INNER JOIN   "subform" ON  "subform"."studyID" = "Main table"."STUDYID"

Edit: Oops, I didn't see the last response.

[flamen]

HEY FJCC, that one works very well too. Thank you very much for your responses. I really appreciate it.
Best wishes