Table Join Help Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Table Join Help Needed

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-07 : 11:41:55

Ok, getting a little better at joins, but now need to take it two more steps.

Main table tblEquipments

Fields: ID, EquipmentType, Location, Status

Second Table tblEquipments_Group

Fields: equipmentID, categoryID, subCategoryID
(all are ints)

I joined these two together and got the info back I needed:

SELECT a.ID, a.Description, a.Status, a.Location,
from tblEquipments a
inner join tblEquipments_Group U on u.categoryID = a.EquipmentType
and u.subCategoryID = @type

Now, the a.status returns a number. What the status actually is is in tblStatus.

Fields: statusID (int) , Status (varchar).

So, the statusID matches a.Status.

I need to get the text from tblStatus in the field Status and replace the a.Status (which is a number still) with the text.

The other one, location, is the same situation.

I've been trying to do another join and am at an end on this.

Suggestions?

Thanks!!

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-07 : 13:07:18

Ok, I got it...

SELECT a.ID, a.Description, s.Status, a.Location
from tblEquipments a
inner join tblEquipments_Group U on u.categoryID = a.EquipmentType
inner join tblStatus S on a.status = S.statusID
and u.subCategoryID = @type

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 14:18:13

Move the u.SubCategoryID = @type to either

SELECT		a.ID,
		a.Description,
		s.Status,
		a.Location
from		tblEquipments a
inner join	tblEquipments_Group U on u.categoryID = a.EquipmentType and u.subCategoryID = @type
inner join	tblStatus S on a.status = S.statusID

or make it a WHERE clause for easier reading.

SELECT		a.ID,
		a.Description,
		s.Status,
		a.Location
from		tblEquipments a
inner join	tblEquipments_Group U on u.categoryID = a.EquipmentType
inner join	tblStatus S on a.status = S.statusID
where		u.subCategoryID = @type

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-07 : 17:14:31

Thanks for the input.

The sql is getting even more complicated now.

I need to add another table: tblSubCategories
Fields: ID, Name, Parent

and Parent will be the same as @type.

I need the name in the dataset....

This returns about 1500 rows when I need them uniqu, only about 45

SELECT c.Name, a.ID, a.Description, s.Status, b.Location, a.scheduleBegin, a.scheduleEnd
from tblEquipments a
inner join tblEquipments_Group U on u.categoryID = a.EquipmentType
inner join tblStatus S on a.status = S.statusID
and u.subCategoryID = @type
inner join tblLocation b on a.status = b.locationID
inner join tblSubCategories c on u.subCategoryID = @type
and c.Parent = @type

Suggestions....

Thanks,

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 17:37:53

This line make no sense

inner join	tblSubCategories c on u.subCategoryID = @type and c.Parent = @type

You must bind this table to another table woth at leest one column or all matching records in this column will be put to all and every records for rest of query.

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-08 : 11:09:14

Thanks for the input.

I took a long look at all the table in the database (which I inherited) and realized the info I needed could not be in tblEquipments.

What I needed to do was created a new table, constrain most of the fields I needed in there such as location, status, subcat and add the fields I needed there, like function, end/begin dates for scheduling.

With help here and learning from a few mistakes, I got the sql working perfectly!!!!

SELECT a.ID, c.Name, a.funct, s.Status, b.Location, a.scheduleBegin, a.scheduleEnd
from tblEquipmentSchedule a
inner join tblStatus S on a.statusId = S.statusID
inner join tblLocation b on a.locationId = b.locationID
inner join tblSubCategories c on c.Parent = @type and a.subCategoryId = c.ID

Thanks!!!!!

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 14:34:42

Great work!

Since there are only INNER JOINs, this is an equivalent

SELECT		a.ID,
		c.Name,
		a.Funct,
		s.Status,
		b.Location,
		a.ScheduleBegin,
		a.ScheduleEnd 
FROM		tblEquipmentSchedule a
INNER JOIN	tblStatus s ON s.StatusId = a.StatusID
INNER JOIN	tblLocation b ON b.LocationID = a.LocationID
INNER JOIN	tblSubCategories c ON c.ID = a.subCategoryId
WHERE		c.Parent = @Type

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-08 : 14:43:05

Yes, a lot easier to read.

Thanks,

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources