3 Selects In One Query One Of Which A Where Condition With Multiple Tables

Question

I use Microsoft Access. Down here I implement OleDb and show you my connectionstring. My Database consists of 3 Tables. Computer, Users, and a "Junction" Table Computerusers. A user can have more than one pc. My goal is to read out every single component of the computer in a DataGridView and it's id, and next to the last component, in the last column the users of the Computer in that Row. For that I need the query, which I have googled for for ages, but haven't found anything besides group concat, which didn't seem realistic for Access, and since I'm almost completely new to programming, a bit too complicated for me, or atleast I didn't find any good explained videos/posts about this for VB.NET

Imports System.Data.OleDb

ConnectionString: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Persist Security Info=False;

Table Users:

 us_id | us_firstname   | us_lastname | us_departement
  1    | Michael        | Rotter      | IT
  2    | Peter          | Parker      | Photographing
  3    | Jonas          | Klos        | Director

Table Computer

  co_id | co_name       | co_gpu      | co_cpu    (6 more columns to this
   1    | PC090         | 1080        | 2700X     table, ram, psu, etc)
   2    | PC040         | 1070        | R5 1600
   3    | PC066         | 1060        | i5-6600K

Table Computerusers:

 co_id | us_id   
    1  | 1        
    1  | 2         
    2  | 2
    3  | 3

How I want the outcome to be (ignored ram,psu, etc on purpose)

   co_id | co_gpu         | co_cpu      | us_firstname
    1    | 1080           | 2700X       | Michael, Peter
    2    | 1070           | R5 1600     | Peter
    3    | 1060           | i5-6600K    | Jonas

Code that I use for simply reading out all components from Computer into the DataGridView

 Dim con as new oledbconnection(conn), cmd as new oledbcommand(Nothing, 
 con)
 Dim reader as oledbdatareader
 cmd.commandtext = "SELECT * FROM Computer"
 con.open
 reader = cmd.executereader
 DataGridView1.Rows.Clear
 While reader.read
 DataGridView1.Rows.Add(reader("co_id"),
                        reader("co_name"),
                        reader("co_gpu"),
                        reader("co_cpu"))
 End While
 Con.Close
 Reader.Close

I really hope I have made this as clear as possible. If not, ask right away, and I will try my best. Also this was my first post ever here, I hope I haven't done anything wrong.

Answer 1

You need to join the tables. Your query, based on the descriptions provided, should probably something look like this:

SELECT ComputerUsers.*, Users.*, Computers.* FROM (ComputerUsers INNER JOIN Computers ON ComputerUsers.co_id = Computers.co_id) INNER JOIN Users ON ComputerUsers.us_id = Users.us_id