Help with SQL JOIN

flangeman

Hi all,

Doing a simple enough Asset Management app for my own use, its ASP and Access backend (I'll move it to something else later).

Anyway, here is an example of the tables (I can't post the actual ones, two bloody big, bit this should explain what I'm trying to do).

TType
Key Desc
1 Phone
2 Computer

TModel
Key Desc
1 6630
2 D600

TManu
Key Desc
1 Dell
2 Nokia

And this table holds the actual Asset Detail

TAsset
Type Model Manu Serial
2 1 2 234234234
1 2 1 234232342

I have managed to do a SQL join that gives the following output (list all Assets)

Type Model Manu Serial
Computer 1 2 234234234
Phone 2 1 234232342

But I can't work out how to do more JOIN's to fillout the rest of the report with the proper model and manu descriptions

select TType.Desc, TAsset.Model, TAsset.Manu, TAsset.Serial from TAsset, TType where TType.Desc=TAsset.Type

Any help would be gratful, I'm going to keep trying.

tempest

select TType.Desc, TModel.Desc, TManu.Desc, TAsset.Serial from TAsset, TType,TModel, TManu where TType.Desc=TAsset.Type
AND TModel.Key=TAsset.Model
AND TManu.Key=TAsset.manu

will do it for you although for readability you would be better off using JOIN syntax and making it.

SELECT TType.Desc, TModel.Desc, TManu.Desc, TAsset.Serial 
FROM TAsset
JOIN TType ON TType.Desc=TAsset.Type 
JOIN TModel ON TModel.Key=TAsset.Model
JOIN TManu ON TManu.Key=TAsset.manu

Not a where clause in sight

Your original SQL doesn't work by the way (at least not according to the tables you posted)

It should surely be:

where TType.Key=TAsset.Type

flangeman

Sorry about the mistake, I lost a little in translation there with that mistake. Funny thing is I went straight back (after posting) and just added an 'AND' with the extra JOIN stuff and it worked. Sometimes just explaining stuff to your self is better!!

I like that middle JOIN, JOIN, JOIN example, very very neat. I will use and explore.

Thats again!!

tempest

Yeah JOIN syntax is better.

you can use where clauses but then you are using "where" to perform two different operations:
1. restriction of the results based on the contents of the data, and
2. joining between the tables.

It's much clearer when you can see exactly what is for joining tables and exactly what is for restricting the results.

Zaph0d

One problem with the data model you have chosen is that the asset table determines the type and manufacturer of a model. As a model can appear twice in the asset table (you might have two 6630s), you are now storing redundant data. There is the possibility of mistakenly recording a 6630 as a Dell in one asset table row and as a Nokia in another.

Your schema is in violation of Second Normal Form and you will cause yourself hassle if you follow this path.

A more conventional schema would be like this:

Type
TypeID Desc
1      Phone
2      Computer

Manu
ManuID Desc
1      Dell
2      Nokia

Model
ModelID Desc ManuID TypeID 
1       6630 2      1
2       D600 1      2

Asset
AssetID ModelID Serial
1       1       87686876sdf
2       2       a8d8hjehe

note:

• KEY is a reserved word and shouldn't be used to name fields, although you can do it if you are happy to deal with the syntax pitfalls.
• Tables aren't normally prefixed with a T or anything (except in Access where people sometimes use the prefix tbl
• Your Asset table didn't seem to have a key and Serial won't do because a phone and a computer might just have the same serial number (I know: billion to one). So I have added a key field AssetID

Your query would look like this:

SELECT Type.Desc, Model.Desc, Manu.Desc, Asset.Serial 
FROM Asset
JOIN Model ON Model.ModelID=Asset.ModelID 
JOIN Type  ON Model.TypeID=Type.TypeID
JOIN Manu  ON Model.ManuID=Manu.ManuID

flangeman

I simplified my database structure in order to explain it here anyway, so sorry about the mistake. I didn't want you guys thinking I was getting you all to do the work! something one person on here trolled at me one day!

Again thanks Zaph0d.

I already had small amendments to the TModel table with some conformaty to the 'Second Normal Form', but hadn't gone as far as you, I'll make my amendments.

Sorry about the use of reserved words (sloppy!), and I had a key column for the TAsset table (I didn't put it here), I'll be using this 'generated' number to produce bar codes for placement on the actual assets themselves.

I'll have to revisit, asp a few times, I might see if the guys in the liberum.org project would be interested in the code. I couldn't find a decent inventory/Asset tagging web system anywhere really.