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.

score 0 · Accepted Answer · answered Jul 15 '19 at 18:59

0

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

answered Jul 15 '19 at 18:59

MarkL

1,721
1
11
22

Hey MarkL. That definitely brought me one step ahead. Now the thing is, now when a PC is being used from more than one person, the row gets inserted as often as there are users using it. Do you know a workaround for that? Like separating the users if there is more than one on one pc, separating the users with a comma in one column. – Jul 15 '19 at 19:53
Here's an answer (https://stackoverflow.com/a/14789896/162313) that does what you're looking for (comma separating columns from different records), but it's for SQL Server and uses `CROSS APPLY` and `FOR XML PATH`, both of which are not supported by Access, I believe. You can probably perform a self-join instead of the `CROSS APPLY`, but I don't know off the top of my head how to get the `FOR XML PATH` in Access. I'd suggest googling for both of those sql commands with Access to research them. – MarkL Jul 16 '19 at 01:06
Hey MarkL. Thanks for your generous helpfulness. I have to say, looking for the equivalent of **CROSS APPLY* and **FOR XML PATH** hasn't brought me any far, since it's just too deep. But I'm happy to say that I was able to reach my goal of having those things on the DataGridView. I did normal selects, and the rest with VB. – Jul 16 '19 at 14:22
You are welcome. Please consider accepting this answer if it has provided you with the information you were asking for. – MarkL Jul 16 '19 at 18:32

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

1 Answers1