SQL Query Help Required

I

initroot

Senior Member
Joined
Jul 30, 2011
Messages
898
Reaction score
45
Location
Cape Town
Hope someone can help struggling to figure this one out.

I have a normal table.

Entities
- EntityID
- EntityName
- GroupType

EntityConnections
- ConnectionID
- GroupID
- EntityID

So what happens is you add a new entity into the Enitites table.
Some entities does have sub entities and therefor the main entity will also be add as GroupType = True.
So to populate in Entities table I might have the following:

Entity ID EntityName GroupType
001 PeterBoltman True
002 PeterBoltmanInc False
006 LoneyBoltmanPTY False
003 CyphonCat True
004 CyphonCatInc False


As you can see the other actually belong to the main entity.
This is somehow connected with the connections table but I haven't figured out where the GroupID is coming from.
I know that 001 is the main entity for 002 and 006.
I want to write simple query that only shows me the entities not GroupType=True, but also on the side their main entities.

EntityName GroupName
PeterBoltmanInc PeterBoltman
LoneyBoltmanPTY PeterBoltman


Any help would be greatly appreciated, I'm unable to fully understand the whole layout of the database due to the huge size of it.
 
Be honest, is this a homework/assignment?

You say that you don't know where groupID comes from. By the looks of it it is most likely an Entities.EntityID where groupType is true.

That connections table most like links the entities like so:

GroupID | EntityID
001 | 002
001 | 006
 
Last edited:
Hamster said:
Yeah....let's do their homework for them
Click to expand...


This isn't homework. Im connecting
to Caseware time database. I have very limited knowledge on the database structure and their support wasn't of much help. The example is simplified.
Okay i know the connections table connects it. Can I assume since I cant find any group table that the groupID is created every time connection is made? The groupID is entered as FK so I suspected it to come from another table somewhere.
 
Fransh said:
This isn't homework. Im connecting


to Caseware time database. I have very limited knowledge on the database structure and their support wasn't of much help. The example is simplified.


Okay i know the connections table connects it. Can I assume since I cant find any group table that the groupID is created every time connection is made? The groupID is entered as FK so I suspected it to come from another table somewhere.
Click to expand...

Fair enough.

i think it is saver to assume that a ConnectionID is created for every connection. The GroupType column seems to indicate that an Entity is a group so therefor my guess is that GroupID is just another EntityID.

Or am I misunderstanding you?
 
Hamster said:
Fair enough.

i think it is saver to assume that a ConnectionID is created for every connection. The GroupType column seems to indicate that an Entity is a group so therefor my guess is that GroupID is just another EntityID.

Or am I misunderstanding you?
Click to expand...
Your understanding are correct. Ive had a look again and indeed the groupID is replicated for each connection. So its created each time for every connection. Dunno why I didnt see this sooner. Must have been looking at my own eyelids. Ive been creating query that extracts the data for all the billed but thats already extremely long. Ill test the "solution" on seperate table before i combine them. Thanks you guys.
 
Nerfherder said:
Select * from entities e join entityconnection en on e.EntityID= en.EntityID where GroupType!=True
Click to expand...

I get no results when I try this.
SELECT dbo.Entities.EntityId, dbo.Entities.EntityNo, dbo.Entities.ShortName, dbo.Entities.Name, dbo.Entities.GroupType
FROM dbo.Entities INNER JOIN
dbo.EntityConnections ON dbo.Entities.EntityId = dbo.EntityConnections.EntityId
WHERE (dbo.Entities.GroupType = 0)

Where grouptype = false returns results.
 
I'm not in front of a PC but try something like this:

select e.EntityNane, g.EntityName
from Entity e
inner join EntityConnection c on c.EntityID = e.EntityID
inner join Entity g on c.GroupID = g.EntityID
where e.GroupType = 0

...or something like that
 
mmm.
My entities table doesn't have a group type. SO i cant link the c.GroupID = g.EntityID

The entity ID is also different from the groupID's assigned.
 
is there some secret meeting of DB designers where everyone gets together and agrees to come up with bad table and field names :)
 
I'm assuming this is a sql server db? Didn't the designers bother to add relationships between the tables? You know.. To enforce a little thing called referential integrity?
 
Fransh said:
mmm.
My entities table doesn't have a group type. SO i cant link the c.GroupID = g.EntityID

The entity ID is also different from the groupID's assigned.
Click to expand...

I suspect there is another table for groups.
 
Fransh said:
.
Entities
- EntityID
- EntityName
- GroupType

EntityConnections
- ConnectionID
- GroupID
- EntityID
Click to expand...

Fransh said:
mmm.
My entities table doesn't have a group type. SO i cant link the c.GroupID = g.EntityID

The entity ID is also different from the groupID's assigned.
Click to expand...

You make me so sad. Can you not post a pic of the ERD?
 
Fransh said:
My apologies I meant groupID.
Will do so when I get home tonight.
Click to expand...
c.GroupID is the groupID in the connection table, g.EntityID is the entityID in the second reference to the Entity table

Entity e: dbo.Entity.EntityID -> dbo.EntityConnection.EntityID
Entity g: dbo.Entity.EntityID -> dbo.EntityConnection.GroupID
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X