Many to Many Relationship, How To use

paulthepaddy

Well-known member
Joined
Apr 9, 2011
Messages
222
Location
UK
Programming Experience
Beginner
Hi guys, im hoping some one will be able to help me with this problem.

Here is my tables
tables.png

I dont really want to use a many to many but if i have to, just say

As far as i know and have read, MS Access allows for multiple values in a single field, but i cant get it to work.

an invoice will have multiple cars on it, and for most parts each car shall only be on 1 invoice, but often enough it will appear on 2 so really this is a many to many

The relationship works when an invoice only have 1 reg on it, but it wont show ANY when it has 2 and the same goes for cars when it has 2 invoice numbers in its invoice field.

if i have to go the many to many relationship route then how do i use that in VB.NET

It Will be as follows

Selecting a car Should pull the details of the invoice its on, so this is sometimes 2 or more BUT selecting an invoice should pull all cars even if they appear on mroe than 1 invoice

Thanks For any help in advance
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
A m:m relationship is generally implemented using a third table. You might have Table1 with Table1ID as the PK and Table2 with Table2ID as the PK and then Table3 with Table1ID and Table2ID columns as foreign keys. That combination of columns might be the PK or you might have a separate Table3ID column for that.

On an unrelated note, I HATE inconsistency in nomenclature. Why do you have an Invoice (singular) table and a Cars (plural) table? There's no reason that I can see for them to be different. Decide whether you want to use singular or plural for table names and stick with it for every table. I prefer singular but it doesn't really matter either way. Just be consistent. Further to that, why do you have an OrderNumber column with no underscore when every other column name that contains multiple words contains one? Again, I don't like underscores when using camel-casing because they serve no useful purpose, i.e. the upper-case letter marks the new word so what's the underscore for? That said, if you want to use underscores then use them but use them EVERY time.
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
As a little trick to combine the two keys you need, you can use a hash of the two foreign key values to act as a 3rd virtual foreign key, depending on what exactly you want to do. In SQL Server the HASHBYTES() function can help you do this. I am unsure if there is an equivalent in Access.

Also I agree with John here, consistency is a crux you must not deviate from. I personally prefer plural for table names, just because it makes more common sense (many Car rows in the Cars table...).
 

paulthepaddy

Well-known member
Joined
Apr 9, 2011
Messages
222
Location
UK
Programming Experience
Beginner
Thank you both for your replys

believe it or not i also agree with the consistency issue, but i end up changing things that often that and it always falls apart so i decided,(this being a test DB) to leave it as it is untill everything to do with the database works as intended then going to sort out the naming issues as well as forms i give components names that come to me untill it all works, then go over it and sort it out. it is the wrong way of doing it, but as i don't get much time at this and it could be weeks in between times i get to look at this it just happens :S

back to subject,
I understand how to make the table (slightly) BUT i dont understand how i then use the relationship in VB in my appliction

here is a small table
EG.png

with the relationship info

and here is how i plan on using it

earch Form.png

basic idea is select the car im lookinf for and it will display the rows of invoice related to that car in the table below

but if i have to use a M:M relationship, how do i go about impimenting it into my application

EDIT: sorry more specific, how do i use that table when SEARCHING through records, inserting shouldn't be a problem i assume its just like inserting into any other table

thanks guys
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Let's say that you have Car with CarID, Invoice with InvoiceID and CarInvoice with CarID and InvoiceID. You list Car records and the user selects one. You get the CarID and then feed it to a query like this to get the corresponding Invoices:
VB.NET:
SELECT Invoice.*
FROM CarInvoice INNER JOIN Invoice
ON CarInvoice.InvoiceID = Invoice.InvoiceID
WHERE CarInvoice.CarID = @CarID
 

paulthepaddy

Well-known member
Joined
Apr 9, 2011
Messages
222
Location
UK
Programming Experience
Beginner
ahh right ok thanks, i kinda of understand what to do, but il check out w3schools to read up more on inner join satements. i do have a few more questions tho.

do i have to use inner joins to insert and update records? (EDIT) No, because i have read up on inner joins

and do i have to run this statement in the table adapter? (EDIT) Still not sure on this one

I have used this code before to find a reg and then pass it to a statement that confirms it as paid, so im PRETTY sure tis code will work for getting the reg for a JOIN

VB.NET:
 reg = Me.CarsDataGridView.SelectedRows.Item(0).Cells(0).Value

This might seem stupid but that fact you have said SELECT Invoice. * makes me think i am not meant to run this on the Invoice Table adapter otherwise it would just be SELECT *, so i dont think its ment to be on the invoice tables adapter.

even thought i have read the info on the MSDN and W3Schools, i still dont understand the inner join

You have said 'SELECT Invoice.*' is the invoice.* kinda of like an object of an invoice row.

but its this that confuses me, im sorry for being a pain, but i really need to understand this, encase there is errors or i need to use more M:M
VB.NET:
FROM CarInvoice INNER JOIN Invoice ON CarInvoice.InvoiceID = Invoice.InvoiceID WHERE CarInvoice.CarID = @CarID
i understand i have to use 3 tables and bring both primary keys into the 3rd table as two foreign keys to create the link between that car and invoice

but w3schools says 'The INNER JOIN keyword returns rows when there is at least one match in both tables.'

how can their be a match in both tables when i am only giving it a reg value.

i am sorry if i am being stupid and am missing something very obvious

Thanks jmcilhinney
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Yes, you do put that query in the Invoice table adapter. Because it's using "Invoice.*" it is getting every column from the Invoice table. You already have a DataTable that contains every column from the Invoice table so you don't want to create another. As long as the schema of the result set of two queries is the same, those two queries can be part of the same table adapter.

As for inserting and updating, the Invoice table adapter is only for inserting and updating in the Invoice table. If you need to insert or deleted (you shouldn't need to update) in your join table then you need another table adapter for that.
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
The best way to understand the join is to visualize it.

Schema.png

With this schema, you could get all invoices and car models for one particular client, or get all clients that have a particular model, or all invoices for a particular model, etc, by joining the tables you need and matching a value for different fields in separate tables. For example to get the invoice ID, amount and the associated car model of every invoice:

SELECT inv.InvoiceId, inv.InvoiceAmount, c.CarModel
FROM CarInvoices ci
INNER JOIN Invoices inv ON inv.InvoiceId = ci.InvoiceId
INNER JOIN Cars c ON c.CarId  = ci.CarId


This works by matching the value of one field in one table to the value of another field in another table. For example, InvoiceId is a primary key in the Invoices table, but it is also a foreign key in the CarInvoices table. The database knows that rows in one table are associated with rows in another table (JOIN) when the value is the same for those fields (ON).

To get only invoices for every model of Mustang, you would add:

WHERE c.CarModel LIKE '%mustang%'


To get all invoices for one particular client, you need to join the customers table using the CustomerId foreign key, and a WHERE clause to choose which customer:

SELECT inv.InvoiceId, inv.InvoiceAmount, c.CarModel
FROM CarInvoices ci
INNER JOIN Invoices inv ON inv.InvoiceId = ci.InvoiceId
INNER JOIN Cars c ON c.CarId  = ci.CarId
INNER JOIN Customers cust ON cust.CustomerId = ci.CustomerId
WHERE cust.CustomerName = 'John Candy'
 
Last edited:

paulthepaddy

Well-known member
Joined
Apr 9, 2011
Messages
222
Location
UK
Programming Experience
Beginner
Hi guys, sorry for the long wait for a reply, been very busy with work last week.

il be honest i still didn't understand it lol, but my aunt knows SQL from work so asked her to have a look at it and see if she could explain it to me so i could get the grips of whats happening lol, so she taught me a bit and i have done a bit of googling witch has helped with this :D.

So thanks guys for your input, very helpful for this confusing subject to me.

got it working in the end, only i do have one other problem.

i put the SQL into the table adapter, then the variable gets passed to it from the datagridview depending on the row selected

BUT In the picture below,
In the left image i can select a row and then hold The Down Arrow key and it will change the selected row and the relationships automatically show, eg the order number

But in the right image i must actually click on the cell to activate the cellcontectlick event (understandably) so i googled around and only came to a few posts that the answer was to use row leave, i tried that and row enter but it throws errors in different scenarios

is their anyway i can get it to act like the order number relationship?

i hope this is explained well enough
 
Top Bottom