SQL Join Problem 1

[LadyDev]

I have four tables: (all information consolidated into one table named tblAll

TblHouse
House_ID
House_Descr
House_Type
Image_Loc
Loc_ID
Combo_ID

TblLoc
Loc_ID
Location
House_ID

TblOwner
Owner_ID
Owner_Name
Owner_Addr
House_ID

TblCombo
Combo_ID
Combo_Name
House_ID
Loc_ID
Owner_ID

SELECT tblAll.HOUSE_DESCR, tblAll.House_ID, tblAll.House_Type, tblAll.Image_Loc, tblAll.Location, tblAll.Owner_ID, tblAll.Owner_Name, tblAll.Owner_Addr
FROM tblAll
WHERE (((tblAll.COMBO_ID)=[Forms]![frmDocView]![cmboChoice]));

The problem I am having is that the result only displays one house_type per owner and one location – it should display multiple locations and owners per house_type – provided the house has been owned by more than one person.

The SQL is wrong, but where? Help!

 

[lespaul]

First of all, with a normalized database, you don't have to create a tblAll. At first glance, it doesn't look normalized. If possible perhaps you can change your design:

TblHouse
House_ID
House_Descr
House_Type
Image_Loc
Loc_ID
Combo_ID

TblLoc
Loc_ID
Location

TblOwner
Owner_ID
Owner_Name
Owner_Addr

TblCombo
Combo_ID
Combo_Name

Then you can write an SQL that will show the correct information:

SELECT HOUSE_DESCR, House_ID, House_Type, Image_Loc, Location, Owner_ID, Owner_Name, Owner_Addr
FROM tblHouse
INNER JOIN TblLoc on tblHouse.Loc_ID = TblLoc.Loc_ID
INNER JOIN TblOwner on TblHouse.Owner_ID = TblOwner.Owner_ID
WHERE (((COMBO_ID)=[Forms]![frmDocView]![cmboChoice]));

Since you want to know who owns the house, you only need the Owner_ID in the House table, then you can join into the owner table to get the information that relates to the owner of the house. For an efficient database, there shouldn't be duplicate data and by storing the house id in the tblOwner, you have duplicated data (the house the owner owns, but you already know who the owner is from the house table!)

Let me know if you need any more assistance or details regarding normalization.





Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

Thanks! I haven't tried the SQL out yet, but I do have a question. You referenced having the OWNER_ID in the tblHOUSE, in your SQL and your paragraph, but it's not. Is this a miss print or an oversight?

 

[lespaul]

My oversight, the tblHouse should have the Owner_ID.


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

Thanks, I assumed as much, but the code is not working. I keep getting a sytax error on the inner join -- missing operator. I am not join savvy, which is why I ended up with my original (tblAll).

 

[lespaul]

post the SQL statement and I'll check it over.

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

SELECT PARTS_DESC, PARTS_ID, PARTS_TYPE, PARTS_IMAGE_LOC, OPTION_ID, COMBO_ID, FURNITURE_ID
FROM tblPARTS
INNER JOIN TblOPTION on tblPARTS.OPTION_ID = TblOPTION.OPTION_ID
INNER JOIN TblFURNITURE on TblPARTS.FURNITURE_ID = TblFURNITURE.FURNITURE_ID
WHERE (((tblPARTS.COMBO_ID)=[Forms]![frmDocView]![cmboChoice]));

I have an Option Group that populates the Combo Box. From the Option Group you have five selections; two of which are (1)Parts (2) Furniture. If either one is selected the information about the selection should show ALONG WITH corresponding info about the other. A one to many relationship -- there are Parts to Furniture and all Furniture has Parts.

 

[lespaul]

It looks like all the fields you are trying to select are all in the parts table, you shouldn't need the joins unless you are getting information from the other tables (like from the house table, get the owner's name rather than the owner id).

why don't you try just getting the parts information with out any joins first and then work the query up to include any additional information.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

Okay, this is confusing. This is where my initial question came in. The original query from tblAll was semi-working, but I only got one part per selection -- without the inner joins.

Now what you are saying because the names have changed -- go back to what I was doing originally, but the query is setup like the one you provided. So what you provided is incorrect.

Alrighty then

 

[lespaul]

No, but all the fields that you have listed in the query above are all from the same table! You don't need any joins if all the information is in the same table. Plus your initial question was about houses not parts! What does the raw data look like and what are you expecting to end up with after the query runs?


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

tblPARTS

PARTS_ID PAR001, PAR003, PAR012
PARTS_TYPE SIDE PANEL, BACK PANEL,
PARTS_DESCR General purpose, used for blah, blah
PARTS_LOC AISLE 1,R 26, AISLE 8,R 27
PARTS_PIC BITMAP, BITMAP
FURNITURE_ID
COMBO_ID
OPTION_ID

tblFURNITURE

FURNITURE_ID FUR001, FUR002
FURNITURE_NAME DESK, DRESSER, CHAIR
FURNITURE_DESCR Designed for use,
FURNITURE_PIC BITMAP
FURNITURE_LOC WAREHOUSE 2345, etc
PARTS_ID PAR012, PAR001

 

[lespaul]

So how do the parts and the furniture tables relate?

You have furniture: (and the only thing in here is about A PIECE of furniture)

TABLE: FURNITURE
FurnitureID : FUR001
FurnitureName : Desk
FurnitureDesc : Designed for use
FurniturePic : bitmap
FurnitureLoc : Warehouse 2345


You have parts: (and the only thing in here deals with parts)
TABLE : PARTS

PartID : PAR001
PartType : SidePanel
PartDesc : General purpose
PartsLoc : Aisle 1, R 26
PartPic : bitmap

PartID : PAR003
PartType : BackPanel
PartDesc : General purpose
PartsLoc : Aisle 8, R 27
PartPic : bitmap

Now you need to relate all the parts for each piece of furniture: (for each piece of furniture there can be many parts so you need an intermediary table to store all the different parts for each piece of furniture)

TABLE FURN_PARTS:
FurnitureID : FUR001
PartID : PAR001

FurnitureID : FUR001
PartID : PAR003

Now you can run queries that get all the parts for each piece of furniture.

To get the name of all the parts for a piece of furniture you would right a query:

SELECT PARTID, PARTDESC FROM PARTS INNER JOIN FURN_PARTS ON PARTS.PARTID = FURN_PARTS.PARTID
WHERE FURNITUREID = 'FUR001'

This would give:

PAR001 SidePanel
PAR003 BackPanel

If you want to get a list of all the furniture that uses a specific part:

SELECT FURNITUREID, FURNITURENAME FROM FURNITURE INNER JOIN FURN_PARTS ON FURNITURE.FURNITUREID = FURN_PARTS.FURNITUREID WHERE PARTID = 'PAR001'

This would give you:

FUR001 Desk
FUR005 Child Desk 1
FUR020 Wall Unit






Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

Okay, thanks for the breakdown, but my form is based on the selection from a combo box (COMBO_ID); I don't know what will be selected at any given time.

WHERE (((COMBO_ID)=[Forms]![frmDocView]![cmboChoice]));

Your scenario seems to involve listing every part and every piece of furniture in the database (in code), which puts me in a worse position than I was before I asked original the question.

All I am trying to do is be able to identify, extract, and present, in a subform, each PARTS that are related to FURNITURE and each FURNITURE that is related to the PARTS. Before all I was getting was ONE PART to ONE piece of FURNITURE to display.

So, while I understood what you wrote, how is that helping in my situation?

 

[lespaul]

Because the way your tables are set up, I don't think you will be able to get the information you are looking for. If the tables aren't designed properly then queries don't work!

How are all the existing tables set up and how do they relate to each other? I'm sorry this is frustating, but I'm trying to help! As the tables stand right now, how do you know if a part belongs to a piece of furniture? (ie - what are your relationships?)

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

FURNITURE_ID is part of tblPARTS

 

[lespaul]

So each part is only used for 1 piece of furniture? or do you have each part listed multiple times for each piece of furniture that it is used on?


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

Not the part name, but PART_ID is listed in the furniture table. There is an ID for each part and each piece of furniture should have a corresponding PART_ID and vice versa. Of course the numbers are going to repeat because more than one part is used to makeup a piece of furniture. So the furniture is made up of many parts and many parts go into different pieces of furniture.

 

[lespaul]

You say:

"So the furniture is made up of many parts and many parts go into different pieces of furniture."

Which is why I said you needed the Furn_Parts table! That's the only way to have a many to many relationship!

So in your furniture table you have:
RECORD 1
FurnitureID : FUR001
FurnitureName : Desk
FurnitureDesc : Designed for use
FurniturePic : bitmap
FurnitureLoc : Warehouse 2345
PartsID : PAR001
RECORD 2
FurnitureID : FUR001
FurnitureName : Desk
FurnitureDesc : Designed for use
FurniturePic : bitmap
FurnitureLoc : Warehouse 2345
PartsID : PAR002
RECORD 3
FurnitureID : FUR001
FurnitureName : Desk
FurnitureDesc : Designed for use
FurniturePic : bitmap
FurnitureLoc : Warehouse 2345
PartsID : PAR003
RECORD 4
FurnitureID : FUR001
FurnitureName : Desk
FurnitureDesc : Designed for use
FurniturePic : bitmap
FurnitureLoc : Warehouse 2345
PartsID : PAR004

Is this correct?












Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

Right.

 

[lespaul]

My first piece of advice would be to create the middle table (Furn_parts), that's really the only way to have a many-to-many relationship between items.

As far as your query goes; you have the following tables:
Furniture(FurnitureID, FurnitureName, FurnitureDesc, FurniturePic, FurnitureLoc, PartsID)

Parts(PartID, PartType, PartsDesc, PartsLoc, PartsPic, FurnitureID, ComboID, OptionID)

Combo(ComboID, ??)

Options(OptionID, ??)

SELECT PARTS_DESC, PARTS_ID, PARTS_TYPE, PARTS_IMAGE_LOC, OPTION_ID, COMBO_ID, FURNITURE_ID
FROM tblPARTS
INNER JOIN TblOPTION on tblPARTS.OPTION_ID = TblOPTION.OPTION_ID
INNER JOIN TblFURNITURE on TblPARTS.FURNITURE_ID = TblFURNITURE.FURNITURE_ID
WHERE (((tblPARTS.COMBO_ID)=[Forms]![frmDocView]![cmboChoice]));


And you want to get the PartsDesc, PartsID, partsType, PartsPic, OptionID, ComboID, FurnitureID when a combo id is entered?







Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!