SQL Statment Help Needed

SQL Statment Help Needed

Post by UXVlcnkgSG » Sun, 11 May 2008 00:11:01


I have 22011 records in table "tblProcChargCounty " when I create a query
between this table and tblFacility table, I have 22011 records, but I try to
add "tblEquipType" table I get bigger number. Now table tblEquipType &
tblProcChargCounty have one-to-Many relationship with tbleFacility table.

I need help with this please

SELECT CODE, OpenFacility, FACILITY, FacilityType, COUNTY
FROM tblFacility
UNION All
Select Year, BeginningDate, EndingDate, County AS tblProcChargCounty_County,
PETProc
FROM tblProcChargCounty

SELECT Code, EquipType, [Replacement/Upgrade], [Active?], DateAquired,
[Owned/Leased], [Fixed/Mobile], [Shared?]
FROM tblEquipType;
 
 
 

SQL Statment Help Needed

Post by S0FSTCBERV » Sun, 11 May 2008 00:36:03

It appears that tblProcChargCounty joins tblFacility on COUNTY but I do not
see a common field to join tblEquipType. How is it related?

--
KARL DEWEY
Build a little - Test a little

 
 
 

SQL Statment Help Needed

Post by UXVlcnkgSG » Sun, 11 May 2008 03:14:03

All tables are join to tblfacility on Code, even tblEquipType.
 
 
 

SQL Statment Help Needed

Post by S0FSTCBERV » Sun, 11 May 2008 04:51:01

Use a LEFT JOIN on CODE.
SELECT tblEquipType.Code, tblEquipType.EquipType,
tblEquipType.[Replacement/Upgrade], tblEquipType.[Active?],
tblEquipType.DateAquired, tblEquipType.[Owned/Leased],
tblEquipType.[Fixed/Mobile], tblEquipType.[Shared?],
tblFacility.OpenFacility, tblFacility.FACILITY, tblFacility.FacilityType,
tblFacility.COUNTY, tblProcChargCounty.Year,
tblProcChargCounty.BeginningDate, tblProcChargCounty.EndingDate,
tblProcChargCounty.County AS tblProcChargCounty_County,
tblProcChargCounty.PETProc
FROM (tblEquipType LEFT JOIN tblFacility ON tblEquipType.Code =
tblFacility.CODE) LEFT JOIN tblProcChargCounty ON tblEquipType.Code =
tblProcChargCounty.CODE;

--
KARL DEWEY
Build a little - Test a little