Access/SQL Query

fini · 16 Jun 2009 at 21:09

I've been working on a database in Access and have managed to complete confuse myself. The relevant details are:

Main table: ID, License1, License 2, License 3, License 4
License table: ID, License-type

The licenses in the main table refer to ID numbers in the license table so an average entry in the main table might be 1, 2, 3, 1, 8 . I want to create a query so that when you load it, it displays the data with the license type so for example :
1, exclusive, non-exclusive, x, y
2, non-exclusive, x, exclusive, y

I've created relationships between license 1, 2, 3 and 4 and the license ID, but can't work out how to create the query to get the data in a query as I'd like. Any ideas?

Pho · 16 Jun 2009 at 21:20

Are the numbers of licenses likely to change? You might be better off adjusting your main table to:

ID, License
with both these columns being the primary key

and filling it with for your example the following data:
1, 2
1, 3
1, 1
1, 8

Then you can query it with the following (untested):

PHP:

SELECT * FROM [MAIN TABLE] mt
INNER JOIN [LICENSE TABLE] lt ON lt.ID = mt.license

fini · 16 Jun 2009 at 21:41

Each ID will always have 4 licenses attached to it (license 1,2,3,4). There's actually more in the main table than just the license references - I left those out as they're not really relevant - but they do mean that have multiple instances of the same ID just to sort out the license issue doesn't really make sense (and would cause other problems unfortunately).

Pho · 16 Jun 2009 at 21:48

You could split it off into a separate table for the maintable->license list though.

However, maybe something like this (again, untested) will do it:

PHP:

SELECT * FROM [MAIN TABLE] mt
INNER JOIN [LICENSE TABLE] lt1 ON lt1.ID = mt.license1
INNER JOIN [LICENSE TABLE] lt2 ON lt2.ID = mt.license2
INNER JOIN [LICENSE TABLE] lt3 ON lt3.ID = mt.license3
INNER JOIN [LICENSE TABLE] lt4 ON lt4.ID = mt.license4

fini · 16 Jun 2009 at 23:01

No, it all works fine until I add the second inner join and then grinds to a halt, really can't work this out

EDIT: Ah ha - it was a parenthesis issue! Got it displaying 2 of them now through the use of the below - scaling up to 4 shouldn't be difficult (touch wood):

PHP:

SELECT * FROM (main mt
INNER JOIN [Licenses] lt1 ON lt1.[ID] = mt.[License1]) 
INNER JOIN [Licenses] lt2 ON lt2.[ID] = mt.[License2];

EDIT: OK, can't work out how to add a third now

Pho · 16 Jun 2009 at 23:17

Access must use a dodgy SQL model.. my example should work fine in MSSQL

.

Least you got it working though

.

Dolph · 16 Jun 2009 at 23:49

That database design looks horrible from the start, the data isn't normalised...

You've got a many to many relationship between the two tables, that is really bad design and going to cause potential problems in the future.

What you want to do is add a link table between the main table and the licence table, so you can have a one - many - one relationship structure which will then make queries much easier.

The most common normalisation would be

Main table
ID
Other data

Link table
ID
Licence ID
(can have multiple entries for same ID each pointing to different licence)

Licence table
Licence ID, licence type

Once you've got this in place, the rest will become much easier.

fini · 17 Jun 2009 at 00:09

Ah you've misunderstood Dolph. The license1, license2, license3, license4 in the main table aren't just there so I can add licenses at will - there will always be four different types of license attached to each ID. They are for different things (license1 for example details the UK license, whilst license2 details the worldwide license) - license1 etc isn't descriptive, but they are better described in the actual database. It is thus a one - many relationship between the tables. The structure you proposed wouldn't work due to license1 and license2 being for different things.

Dolph · 17 Jun 2009 at 00:19

fini said:
Ah you've misunderstood Dolph. The license1, license2, license3, license4 in the main table aren't just there so I can add licenses at will - there will always be four different types of license attached to each ID. They are for different things (license1 for example details the UK license, whilst license2 details the worldwide license) - license1 etc isn't descriptive, but they are better described in the actual database. It is thus a one - many relationship between the tables. The structure you proposed wouldn't work due to license1 and license2 being for different things.

Are the details for license 1, license 2 etc stored in different tables? If not it's still a many to many relationship??

You've said yourself that licence ID points to licences 1, 2, 3, 4. That's definitely a many to many the way you've described it, and how your query is working. They may be different things, but from how you've described the schema it still doesn't make sense to me.

fini · 17 Jun 2009 at 00:25

No, sorry, let me explain with some fake data:

Code:

main:
ID       license1 (UK)     License2(worldwide)     license3(UK special)
---------------------------------------------------------------------------
1              1                1                       3
2              2                1                       2

license:
ID         license type
------------------------------------------------------------------------
1          exclusive
2          non-exclusive
3          non-exclusive, particular field
4          none

So each item has four separate licenses running at the same time - always being one of each kind (in the example UK, worldwide and UK special).

There is thus a one to many relationship between license.[license type] and main.license1 for example as there may be 10 different items with a UK exclusive license, but there will never be an item with a UK exclusive and non-exclusive license.

Hope that makes more sense.

Why I've been asked to do this I have no idea...

Dolph · 17 Jun 2009 at 00:35

fini said:
No, sorry, let me explain with some fake data:

Code:

main: ID license1 (UK) License2(worldwide) license3(UK special) --------------------------------------------------------------------------- 1 1 1 3 2 2 1 2 license: ID license type ------------------------------------------------------------------------ 1 exclusive 2 non-exclusive 3 non-exclusive, particular field 4 none

So each item has four separate licenses running at the same time - always being one of each kind (in the example UK, worldwide and UK special).

There is thus a one to many relationship between license.[license type] and main.license1 for example as there may be 10 different items with a UK exclusive license, but there will never be an item with a UK exclusive and non-exclusive license.

Hope that makes more sense.

Right, but relationships in database design aren't defined by fields, but by tables.

What you have described above is a many to many relationship between the main table and the license table. It doesn't matter that you have one to many relationships between fields, because that's not how normalisation works. Each record in Main links to mulitiple records in licence, and vice versa.

You either need a link table, or you need to split the licence types into tables so each field links via a one to many relationship to a different table. (The link table is the better approach).

fini · 17 Jun 2009 at 00:41

Ah that's where I'm going wrong then. I've always thought it was fields for some reason.

Off to get a snack and then to start again with a link table this time.

Thanks!

Dolph · 17 Jun 2009 at 00:43

No worries, get the link table in and it will become much easier to manage. I'd suggest putting a field in the link table to tell you the licence type (UK, worldwide) when you do it as that'll help prevent confusion.

If you need anything else, feel free to give us a yell