SQL join syntax--help!

[bkiser]

Hello.

I have a SQL statement like this that works fine:

SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID from Fields Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID

But I need to add another table to the mix. The new table is called FieldValues, and contains ID and VALUE columns. The relationship is Fields.ID = FieldValues.FieldID, but I don't know how to add it to the SQL statement above to get the matching value column from FieldValues.

I'm not good with joins and can't get the syntax right.

Thanks for any help.

-Brian

 

[amorous]

Try this:

SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID,FieldValues.ID,FieldValues.VALUE from Subcategories Inner Join Fields on Fields.CategoryID=Subcategories.CategoryID
Inner Join Fieldvalues ON
Fields.ID = FieldValues.FieldID

-VJ

 

[scriverb]

This should be what you are looking for:

SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID, FIELDVALUES.VALUES  
from (Fields Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID) Inner Join FIELDVALUES ON Fields.ID = FIELDVALUES.ID;

Post back with questions.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[lespaul]

Does this work?:

SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID
from Fields
Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID
INNER JOIN FieldValues on FieldValues.FieldID = Fields.ID

If not what kind of error are you getting?

leslie

 

[bkiser]

I'm getting a syntax error "missing operator" on this query. Maybe the parenthesis are off?

 

[scriverb]

Sorry about that. I see that you stated the ID field as ID and FieldID:

SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID, FIELDVALUES.VALUES  
from (Fields Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID) Inner Join FIELDVALUES ON Fields.ID = FIELDVALUES.FieldID;

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jjlogan]

Brian,

I hope this helps. Here is an sql string with two tables joined to a left table (TotalTrans) using inner joins. The fields are different, of course, than yours.

SELECT TotalTrans.NC, TotalTrans.DB_Num, [2003 Joblist].JOB_DESC, NCLookup.NC_Description
FROM (TotalTrans INNER JOIN NCLookup ON TotalTrans.NC = NCLookup.NC) INNER JOIN [2003 Joblist] ON TotalTrans.JOBSUB = [2003 Joblist].JOBSUB;

Suggestion: create the query using Access's Query Grid, then select menu item View/SQL - that's what I did above.
Jeff

 

[bkiser]

Scriverb,

Thanks--something's still not right, though. It's a little hard to describe, but I'll try.

My original SQL statement gave me all the subcategories with their respective fieldnames, your query gives me all the subcategories with the same fieldname repeated for each subcatgory.

Here is an example of my output:

SUBCATEGORY SUBCATEGORYID FIELDNAME FIELDID
Mark 1 Phone 1
Mark 1 Email 2
Mark 1 Mobile Phone 5
Ben 2 Phone 1
Ben 2 Email 2
Ben 2 Mobile Phone 5

This is essentially what I want, except I need corresponding values from the FIELDVALUES table in there as well.

Your query gives me:

SUBCATEGORY SUB...ID FIELDNAME FIELDID VALUE
Mark 1 Phone 1 ben's number
Mark 1 Phone 1 mark's number
Ben 2 Phone 2 ben's number
Ben 2 Phone 2 mark's number

I substituted "ben's number" and "mark's number" for the actual phone numbers, since they wouldn't be helpful.

Any idea what's going on?

 

[scriverb]

Sounds like you need to perform a Group By in this query:

SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID, FIELDVALUES.VALUES  
from (Fields Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID) Inner Join FIELDVALUES ON Fields.ID = FIELDVALUES.FieldID 
GROUP BY Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID, FIELDVALUES.VALUES'

As I don't have anything to test this against please try this and let me know how it works for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[bkiser]

Thanks, Scriverb.

That works syntactically (minus the AS clause in the group by), but the results still don't look right. I'm still getting Ben's Phone listed twice, once for him and once for Mark. The reverse is also true with Mark--I get his phone and Ben's as well.

I am actually working with a larger set of data, and for each Subcategory (person, in this case), I'm getting multiple wrong VALUE fields returned.

Wow, this is SO hard to explain. Am what I saying making any sense?

 

[PHV]

What is the Primary Key of FieldValues ?
Can't be only FieldID, it's why the semi-cartesian product.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bkiser]

Hi, PHV.

The pk of FieldValues is ID. However, when I relate it to the FIELDS table, I use this relationship:

FIELDS.ID = FIELDVALUES.FIELDID


Does that help explain anything?

 

[PHV]

FIELDS.ID = FIELDVALUES.FIELDID
And what is the cardinality of this relation ?
If not 1:1 (as I guess) this will explain the spurious records returned by all this incompletly joined queries.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bkiser]

Ah, I see what you mean. No, it's not 1-to-1 based on that relation. It's a 1-to-many, based on that. I had planned on using the FIELDVALUES.SUBCATEGORYID to differentiate which values goes to which SUBCATEGORY.

Do you think to change the table structure? Although I set out to have a clean, normalized design, I seem to be having a really tough time writing queries.

 

[PHV]

Ben's Phone listed twice, once for him and once for Mark
Your problem is here. Which column in FieldValues in cunjunction with FieldID permits the differentiation between Ben's phone and Mak's phone ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bkiser]

SUBCATEGORYID. Ben and Mark have unique IDs in the SubCategories table, and there's a foreign key in the FieldValues table containing that SUBCATEGORYID.

I have to admit I've lost myself with this database design, although it's normalized pretty well. Perhaps I just need to add an ID field somewhere that would help queries?


Here's the basic structure:

SUBCATEGORIES TABLE
-------------------
ID
SUBCATEGORY (Ben, Mark, Visa, MasterCard, etc)
CATEGORYID (foreign key to CATEGORIES table)

FIELDS TABLE
------------
ID
FIELDNAME (Phone, Email, Mobile Phone, Expiration Date, etc)
CATEGORYID

FIELDVALUES TABLE
-----------------
ID
FIELDID
SUBCATEGORYID
VALUE

CATEGORIES TABLE
----------------
ID
CATEGORY (Bank Accounts, Contacts, Credit Cards, etc)

 

[PHV]

So try this (from scriverb post):
SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID, FIELDVALUES.VALUES
from (Fields Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID) Inner Join FIELDVALUES ON Fields.ID = FIELDVALUES.ID
WHERE FIELDVALUES.SUBCATEGORYID=SUBCATEGORY.ID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bkiser]

That's close, but doesn't return every matching VALUE field.

The result set looks like this:

Ben 2 Phone <Ben's phone number>
Mark 1 Email <Mark's email address>

But Ben actually has multiple values, not just a phone number. Same with Mark.

I tried just switched the INNER joins to LEFT joins, but the result set was the same. I feel like if we could change the WHERE to a LEFT JOIN, it might work. Do you think I'm on the right track?