Utterly Stuck with SQL

Abyss

Abyss

Abyss

Soldato
Joined
18 Oct 2002
Posts
4,548
Location
Nottingham
I am trying to create a database in MS Access, and am completely stuck at one point. It would really be appreciated if anyone could help:

Basically a tenant has multiple "requirements", which are just attributes that they need.

A building has "buildingAttributes", which are attributes that a building has.

For a specific tenant, I want to be able to list the buildingIDs that meet the tenants requirements.

It keeps feeling like i'm 90% of the way there, but it keeps failing to work :(.

(Section of entity relationship diagram relevent to problem)
untitledfr9.jpg



Any help would be really appreciated

Thanks, Andrew.
 
Gaverick said:
HTML, Graphics and Programming would be a better place for this
Click to expand...

Doh! Cheers. Didn't realise there was even such a forum (just checked and my software tab is minimised). Could a mod possibly move it for me please?
 
One wonders what kind of database you're building when tenant has a TenantSize attribute and requirements offers HardOrSoft :eek: :p

Anyway, a simple (and only partially correct) query would be:
Code: 
SELECT 
	bu.BuildingName 
FROM
	Building bu,
	BuildingAttribute at,
	Requirement re
WHERE
	re.TenantID = "your tenant",
	re.AttributeID = at.AttributeID,
	at.BuildingID = bu.BuildingID

This doesn't take into account a building may be returned multiple times from multiple matched requirements or that buildings will be returned when only one of several requirements is met. Been too long since I've done any SQL..
 
I can't help you directly but I did think of a dirty hack. I am kind of surprised that Access is powerful enough to do this in one SQL command even with nesting, but I will await an elegant solution ;)

Any way the hack would be to give every attribute a number, more specifically a prime number then each building has a "score" which is all it's attribute values multiplied together.
Now you work out the score the tenant wants by multiplying each requirement value together and then pick results that this number divides into without remainder.
This is highly dependent on the number of attributes a house could possible have as you might run out of sensible sized primes.

In case you don't quite get what I mean I'll give a small example:
Building1 has attributes: roof, door, window, garage
Roof has a value of 2
Door has a value of 3
Window has a value of 5
Garage has a value of 7
The score of Building1 is 2*3*5*7 = 210
Building2 only has a roof and a garage so has a score of 14 (2*7)

The tenant wants a house with a Window and a rood, i.e. target score of 2*5 = 10.
Now find buildings that have a score that divides by 10. 210 does :) return Building1, 14 doesn't so don't return Building2

Afraid that's the best solution I can offer.
 
One way to think about this is to select the building where all the attributes for the tenant match the requirement and you don't have any that don't match.

Try something like this:

Code: 
select distinct(b.buildingid)
from building b, requirements r
where b.attributeid = r.attributeid
and r.tenantid = :mytenantid
and 1 not in (select 1 from requirements r2
                  where r2.tenantid = r.tenantid
                  and r2.attributeid not in
                        (select b2.attributeid from building b2
                         where b2.buildingid = b.buildingid));
The first part of the select handles the attributes that do match whilst the sub-select looks for attributes for the tenant that aren't met by the building.

A bit contrived but I think that would work.

Jim
 
Thanks for the help everyone.

The last one seems the most promising, but I can't get it to work.

I assume you meant

from buildingAttribute b, requirements r,
as there is no such thing as b.attributeid.

Even changing that doesn't get it working though :(
 
This is what I have so far:

Code: 
SELECT DISTINCT (b.buildingid)
FROM building b, buildingAttribute ba, requirements r
WHERE ba.AttributeID = r.AttributeID
AND r.TenantID = [Enter TenantID]
AND 1 NOT IN 
(select 1 from requirements r2, building b2
                  where r2.tenantid = r.tenantid
                  and r2.attributeid NOT IN
                        (select ba2.AttributeID from buildingattribute ba2
                         where b2.buildingid = b.buildingid));

Doesn't work though :(
 
Ah, there were a few mistakes in the first query I posted. Here's one that I've tested....

Code: 
select distinct(b.buildingid)
from buildingattribute b, requirements r
where b.attributeid = r.attributeid
and r.tenantid=[mytenant]
and 1 not in (select 1 from requirements r2
	    where r2.tenantid = r.tenantid
                    and r2.attributeid not in
		(select b2.attributeid from buildingattribute b2
		 where b2.buildingid = b.buildingid));

This selects the distinct buildingid from the buildingattribute table for all buildings that meet a tenants requirements. Buildings that exceed a tenants requirements will also be selected.

To get the names of buildings you'll need to join the first query to the building table, something like:

Code: 
select distinct(b.buildingid), bi.buildingname
from buildingattribute b, building bi, requirements r
where b.attributeid = r.attributeid
and r.tenantid=[mytenant]
and bi.buildingid = b.buildingid .... (rest of query the same)

This was tested on an Ingres database (I'm not that speedy on Access) so there may be some differences in the SQL syntax etc. However, they shouldn't be much as it's pretty standard SQL.

Hope that helps,
Jim
 
Thats brilliant JIMA, cheers! The above code was pretty much spot on bar a few modifications to get it to work how I wanted it to. I can finally get onto implementing the rest of the system!

Thanks again!

Also thanks to everyone else who provided ideas and offers to help solve the problem.

Andrew.
 
You must log in or register to reply here.
Back
Top Bottom