Advanced Query Help

[TamedTech]

Hello Guys,

I'm guessing this what you would call an 'advanced' query, although with my knowledge as it is, most things seem pretty advanced lol.

I'm hoping you'll be able to help, I'll try and explain to the best of my abilities what i'm trying to achieve in the hope you'll be able to help. I've also copied in my current efforts (which are shocking) so you can see how poor i am with this kind of thing.

In my database I have the following tables.

member
tblUnits
tblUnitsOwners
tblGroups

Now the concept behind this is that I have a list of members (in the members table), each member is able to own many units, and a unit can be owned by many members. A unit can also be be put into a group, a group can store many units, but a unit may only belong to one group. A member may also own many other members, but a member can only be owned by one member.

So i've constructed the database as best I can, and i'm hoping it will meet my requirements, but if it doesn't then i'm more than happy to change things.

The 'member' table consists of three fields (ID, Name, Owner_id).

The 'tblUnits' table contains all the information on the units, fields are (ID, UnitName, GroupID)

The 'tblUnitsOwners' table contains link data between members and the units, fields are (ID, MemberID, UnitID)

The 'tblGroups' table contains information on the groups, fields are (ID, GroupName, GroupDescription)

Now the desired result is a list of units that are owned by particular user, such as user '1', that can be organised into thier 'groups' and with each box I want a list of other users that own that box, I've tried to illustrate this below.

Group 1 Title
-------------
Unit 1 - User 2
- User 4
- User 5
Unit 3 - User 2
Unit 4 - User 2
Unit 7 - User 5
- User 4

Group 2 Title
-------------
Unit 2 - User 4
Unit 5 - User 8
- User 9

Anyway, i'm mildy proficiant with ColdFusion so grouping the output isn't an issue, its just this query that has got me stumped, and i thought it a little heavy for the CF forum.

Below is a copy of what i've managed to cobble together so far, although i'm pretty convinced its next to useless.

				SELECT	tblUnitsOwners.id,
						tblUnitsOwners.unitID,
						tblUnitsOwners.ownerID,
						tblUnits.box_id,
						tblUnits.unit_name,
						tblUnits.group_id,
						tblGroups.id,
						tblGroups.group_title,
						tblGroups.group_description,
				FROM	tblUnitsOwners
				LEFT JOIN tblUnits
				ON		tblUnitsOwners.unitID = tblUnits.box_id
				LEFT JOIN tblGroups
				ON		tblUnits.group_id = tblGroups.id
				WHERE	tblUnitsOwners.ownerID = #arguments.aOwnerID#
				ORDER BY	tblGroups.id, tblUnits.box_id, tblUnitsOwners.id

You'll notice that the field names are a little different to the ones i've given above, but dont fuss to much, i'm looking for a more general solution as to how my JOINS should be working, I have enough knowledge to be able to change the field names later.

I know its a big ask guys, but i'm pretty sure that if you can give me a little help then i'll be able to learn alot from the workings of the query.

Many Thanks,

Rob

 

[TamedTech]

Argh .. no advice?

Come on chaps, throw the dog a bone, i'm not looking for anyone to write the query for me, just hand me a few pointers on which types of joins i should be using to link each of the tables, just somthing to kick start me out of the rut i'm stuck in.

Thanks,

Rob

 

[TamedTech]

OK Guys,

I've made a little progress with this, although i'm not quite getting the result i need from the query.

Here is my revised query:

				SELECT	tblUnitsOwners.id,
						tblUnitsOwners.unitID,
						tblUnitsOwners.ownerID,
						tblUnits.box_id,
						tblUnits.unit_name,
						tblUnits.group_id,
						tblGroups.id,
						tblGroups.group_title,
						tblGroups.group_description,
						member.ID,
						member.firstName,
						member.lastName
				FROM tblGroups 
				LEFT JOIN tblUnits ON tblGroups.id = tblUnits.group_id 
				INNER JOIN tblUnitsOwners ON tblUnits.box_id = tblUnitsOwners.UnitID 
				INNER JOIN member ON tblUnitsOwners.OwnerID = member.ID
				ORDER BY	tblGroups.id, tblUnits.box_id, tblUnitsOwners.id

This works well, as i can group the output and get a list of all the boxes, organised into thier groups and then a list of other owners below each box.

Now the bugs in this code are that i cant specify a member that is viewing the data and have it filter the content slightly.

For instants if i am viewing the list of boxes, i want to see a list of the boxes i own, not all of the boxes in the database, then a list of users under each box excluding my own name as i know i own the box.

So if I own units 1, 2, 3 & 4 and bob owns boxes 2 & 4, my list would look like this.

1
2
Bob
3
4
Bob

And bob's list would look like this.

2
Rob
4
Rob

This way bob cannot see boxes 1 and 3 as he does not own them and they are of no concern to him. I would guess that this is done using a WHERE clause, but i've tried a couple of solutions that filter my member ID from the member table but you then no longer get a list of other users that own a unit.

Any advice?

Thanks,

Rob