CODEWITHSUNDEEP
c#asp.netsqlms-accessinner-join
Jelmer Holtes
Jelmer Holtes

Reputation: 73

SQL 2 INNER JOINS with 3 tables

I've a question about a SQL query.. I'm building a prototype webshop in ASP.NET Visual Studio. Now I'm looking for a solution to view my products. I've build a database in MS Access, it consists of multiple tables.

The tables which are important for my question are:

Below you'll see the relations between the tables

For me it is important to get three datatypes: Product title, price and image. The product title, and the price are in the Product table. The images are in the Foto table.

Because a product can have more than one picture, there is a N - M relation between them. So I've to split it up, I did it in the Productfoto table.

So the connection between them is:

product.artikelnummer -> productfoto.artikelnummer 
productfoto.foto_id -> foto.foto_id

Then I can read the filename (in the database: foto.bestandnaam)

I've created the first inner join, and tested it in Access, this works:

SELECT titel, prijs, foto_id
FROM Product
INNER JOIN Productfoto
ON product.artikelnummer = productfoto.artikelnummer

But I need another INNER JOIN, how could I create that? I guess something like this (this one will give me an error)

SELECT titel, prijs, bestandnaam
FROM Product 
(( INNER JOIN Productfoto ON product.artikelnummer = productfoto.artikkelnummer )
INNER JOIN foto ON productfoto.foto_id = foto.foto_id)

Can anyone help me?

Upvotes: 1

Views: 7780

Answers (2)

jos
jos

Reputation: 823

One thing about the use of linking tables

The ProductFoto table allows for N-M relations between Product and Foto indeed. Is this what you really want/need? In other words, can one Foto belong to more than one Product? If not, put the Product_Id on the Foto table. If so,...

...let's discuss JOIN.

Say we have two tables, A and B. doing a 

SELECT * FROM A, B

will give you all permutations of A's rows with B's rows. We could limit the resultset by adding a WHERE clause, like WHERE A.a='lekker hoor!', or a way cooler WHERE A.id=B.a_id. Which actually starts to look like a JOIN result!

Lets do a proper JOIN then: 

SELECT * FROM A JOIN B ON A.id=B.a_id

JOINs actually come in LEFT OUTER, RIGHT OUTER and FULL INNER or FULL OUTER joins.

  • A LEFT JOIN (use of OUTER is optional) will contain all records in the left (first) table, even if there is no corresponding records(s) in the right (second) table.
  • A RIGHT JOIN obviously works the same way, but mirrored.
  • With a FULL OUTER JOIN both tables are optional (not quite the same as SELECT * FROM A, B though!).
  • A FULL INNER needs matching records from both tables (this is the default).

When you do want to do more than one JOIN, say 

SELECT * FROM 
  A 
  JOIN B ON A.id=B.a_id
  JOIN C ON B.id=C.b_id

You can think of the extra JOIN as joining on an intermediate table, formed by joining A and B, especially when mixing some LEFT/RIGHT/INNER/OUTER JOINs.

As to your question Use something along the lines of

SELECT TOP (1) titel, prijs, bestandnaam
FROM 
( -- this bracket is MS Access specific (and awkward)
  Product 
  INNER JOIN Productfoto ON product.artikelnummer = productfoto.artikelnummer
) -- this one too
  INNER JOIN foto ON productfoto.foto_id = foto.foto_id
  • to satisfy MS Access, use brackets around first two tables, see Ms-Access: Join 3 Tables
  • Normally no brackets required (you'll get to use them like this when you discover sexy sub-selects, for which the rule is: only use them if there is no alternative).
  • Because there are multiple matches in your ProductFoto table, there are multiple matches in your result. Use TOP 1 (or LIMIT 1, depending on your DB) to 'fix' this.

Veel success, en doe jezelf een plezier en switch to English!

Upvotes: 1

Thousand
Thousand

Reputation: 6638

something like this should work:

SELECT Product.titel, Product.prijs, Foto.bestandnaam FROM Product INNER JOIN
(Foto INNER JOIN Productfoto ON Foto.[foto_id] = Productfoto.[foto_id]) ON
Product.[artikelnummer] = Productfoto.[artikelnummer];

Upvotes: 3

Related Questions