[Solved] Query between 2 tables

Creating tables and queries

[Solved] Query between 2 tables

Postby flamen » Wed Jan 06, 2016 8:47 pm

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).
Attachments
Screen Shot 2016-01-06 at 19.45.12.png
Screen Shot 2016-01-06 at 19.47.32.png
Last edited by flamen on Thu Jan 07, 2016 11:23 am, edited 1 time in total.
OpenOffice 4.1.1. Mac OS.X. 10.9.5.
flamen
 
Posts: 7
Joined: Wed Jan 06, 2016 8:29 pm

Re: PLEASE HELP. Desperate

Postby FJCC » Wed Jan 06, 2016 9:14 pm

I think you want something like
Code: Select all   Expand viewCollapse view
SELECT * FROM "Main table" WHERE "Main table"."STUDYID" IN (SELECT DISTINCT "studyID" FROM "subform")
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7537
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: PLEASE HELP. Desperate

Postby flamen » Wed Jan 06, 2016 11:00 pm

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.
Attachments
Screen Shot 2016-01-06 at 22.00.54.png
OpenOffice 4.1.1. Mac OS.X. 10.9.5.
flamen
 
Posts: 7
Joined: Wed Jan 06, 2016 8:29 pm

Re: Query between 2 tables

Postby flamen » Thu Jan 07, 2016 12:11 am

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
Attachments
Screen Shot 2016-01-06 at 23.13.43.png
OpenOffice 4.1.1. Mac OS.X. 10.9.5.
flamen
 
Posts: 7
Joined: Wed Jan 06, 2016 8:29 pm

Re: Query between 2 tables

Postby FJCC » Thu Jan 07, 2016 1:23 am

Try
Code: Select all   Expand viewCollapse view
SELECT * FROM "Main table" INNER JOIN   "subform" ON  "subform"."studyID" = "Main table"."STUDYID"

 Edit: Oops, I didn't see the last response. 
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7537
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query between 2 tables

Postby flamen » Thu Jan 07, 2016 11:23 am

HEY FJCC, that one works very well too. Thank you very much for your responses. I really appreciate it.
Best wishes
OpenOffice 4.1.1. Mac OS.X. 10.9.5.
flamen
 
Posts: 7
Joined: Wed Jan 06, 2016 8:29 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests