Complex left join problem

[bkiser]

Hello. Please bear with me. This is a little complex and I'm pulling my hair out. I hope you can help (not pull my hair out, but with this problem.

I have 4 tables that look like this:

CATEGORIES
ID Category
1 Contacts
2 Credit Cards
3 Work Related
...

SUBCATEGORIES
ID SubCategory CategoryID
1 Mark 1
2 Ben 1
3 VISA 2
4 Capital One 2
5 Dawn 1

FIELDS
ID FieldName CategoryID
1 Phone 1
2 Email 1
3 Number 2
4 Expiration Date 2
5 Mobile Phone 1

FIELDVALUES
ID FIELDID SUBCATEGORYID VALUE
1 1 2 502-555-BEN1
2 1 1 502-555-MARK
3 3 3 5044123412341244
4 5 5 502-555-DAWN
5 2 1 mark@bellsouth.net

All I really need is to do a left join on fields and fieldvalues, getting all field columns FOR A PARTICULAR SUBCATEGORY ID (which is supplied) and all MATCHING fieldnames from fields.

The select below isn't right--it gets all fields in a particular category, but I need one that gets all fields AND field values for a particular subcategory.

SELECT fields.fieldname
from categories left join fields on fields.categoryid = categories.id
where categories.id = 3

In plain terms, if I supply a SubCategory ID of 1 (for Mark), I want to see all fields that map to him. In the case above, that will be:

Phone 502-555-1212
Email mark@bellsouth.net

I've been out of SQL for years, and honestly can't seem to make this work. Can anyone help me with this query?

Thanks for any info.

-Brian

 

[lespaul]

SELECT FIELDS.FIELDNAME, FIELDVALUES.VALUE
FROM FIELDVALUES
INNER JOIN FIELDS ON FIELDS.FIELDID = FIELDVALUES.FIELDID
WHERE SUBCATEGORYID = 1

Leslie

 

[bkiser]

Hi, Leslie.

That's actually the SQL statement I've been using, but doesn't quite fix my problem. For example, if you use SubCategory = 1, you get this:

FIELDNAME VALUE
Phone 502-555-MARK
Email mark@bellsouth.net

But I'd like to return all the values under that category, even if some of them are blank, like this:

FIELDNAME VALUE
Phone 502-555-MARK
Email mark@bellsouth.net
Mobile Phone


Notice how the field "Mobile Phone" shows up, even if there is no corresponding value in the FIELDVALUES table. I need to show every record in the category (not just the matching records) when I display the result set.

Am I making any sense?

Thanks for the help--I really appreciate it.

 

[PHV]

Have you tried this ?
SELECT FIELDS.FIELDNAME, FIELDVALUES.VALUE
FROM FIELDS
LEFT JOIN FIELDVALUES ON FIELDS.FIELDID = FIELDVALUES.FIELDID
WHERE SUBCATEGORYID = 1

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.

That actually gives the same result as lespaul's select statement. It's probably my fault that I wasn't clear about needing all the FIELDNAME/FIELDVALUE combinations for a specific category.

Can you guys come up with a query to do that?

If it's something that's just not possible, I can modify the database, although I'd prefer to stay with the current design if possible.

Thanks again,
Brian

 

[lespaul]

PHV's query should return what you are looking for. The Left join states bring all the results from fields and only those that match from fieldvalues.



Leslie

 

[JonFer]

I think what you want are all fields for a CATEGORY for the SUBCATEGORY you provide. Because there is some circular referencing between the tables, I would do this in two steps. First write a query that generates all fields for the subcategory's category:

qryCategoryFields:
SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID
from Fields Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID
Where Subcategory.ID=[Enter SubCat ID]

Then do the left join:

SELECT q.Subcategory, q.Fieldname, fieldvalues.value
from qryCategoryFields as q left join fieldsvalues on q.FieldID = Fieldvalues.FieldID and q.SubcategoryID=Fieldvalues.SubcategoryID

 

[bkiser]

Hi, Jonfer--yes, that's it!

I'm not at my PC right now, but that query looks right. Thanks SO tremendously much to all of you, especially you Jonfer!

Hopefully this will put me on the road again. Thanks, guys.

-Brian

 

[bkiser]

Hmmm... something's still not quite right. On the first query, Access is prompting me for a parameter called Subcategory.ID

 

[lespaul]

change it to:

qryCategoryFields:
SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID
from Fields Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID
Where Subcategories.ID =[Enter SubCat ID]

Leslie

 

[lespaul]

Maybe I misunderstood. You should be getting a single prompt from the first query that says:

Enter SubCat ID

Do you then get a SECOND prompt for the Subcategory.ID?

If yes, then if you make the change in bold in my first post, that should fix it. I think the table name was wrong before.

Leslie

 

[bkiser]

Leslie,

No, you were right! That works. The table name was wrong, and that's all.

You guys are great. I appreciate all the patience you have showed to me. I am just coming back to the Microsoft world after 5 years, and I am SO glad to be back!

Take care, and thanks again.

-Brian