September 12, 2017 at 3:21 am
Hi,
I've 2 entities, opportunities and properties, with certain fields and that can have features.
I'd like to get:
1. All properties for an opportunity
2. All opportunities for a property (probably just change the WHERE clause from 1)
3. All opportunities / properties
The basic structure is this:
/* just going to add type and rooms */
CREATE TABLE opportunities (id INT, idType TINYINT, rooms TINYINT)
CREATE TABLE opportunitiesFeatures (idOpportunity INT, idFeature INT)
CREATE TABLE properties (id INT, idType TINYINT, rooms TINYINT)
CREATE TABLE propertiesFeatures (idProperty INT, idFeature INT)
INSERT INTO opportunities VALUES (1, 1, 3), (2, 1, 2)
INSERT INTO opportunitiesFeatures VALUES (1, 1), (1,2), (2,1), (2,3)
INSERT INTO properties VALUES (1, 1, 3), (2, 1, 2), (3, 1, 3)
INSERT INTO propertiesFeatures VALUES (1, 1), (2,1), (2, 2), (2, 3)
/* BASIC MATCH WITHOUT FEATURES */
SELECT t0.id idOpportunity, t1.id idProperty
FROM opportunities t0
CROSS JOIN properties t1
WHERE t0.idType = t1.idType AND t0.rooms = t1.rooms
/* WITH FEATURES
1. PROPERTIES FOR OPPORTUNITIES (all properties that have required features from opportunities)
- property 1 won't match opportunity 1 because this requires features 1,2 and the property only has features 1
- property 1 won't match opportunity 2 since the rooms are different
- property 2 won't match opportunity 1 since the rooms are different
- property 2 matches opportunity 2 because the opportunity requires features 1,3 and the property has features 1,2,3
- property 3 wont match any opportunity since it has no features
2. OPPORTUNITIES FOR PROPERTIES (all opportunities that have required features from properties)
- opportunity 1 matches property 1 since the property requires features 1 and opportunity has 1,2
- opportunity 1 won't match property 2 since the rooms are different
- opportunity 1 matches property 3 since the property has no features
- opportunity 2 won't match property 1 since the rooms are different
- opportunity 2 won't match property 2 since the property requires features 1,2,3 and opportunity has 1,3
- opportunity 3 won't match property 3 since the rooms are different
3. ALL OPPORTUNITES VS PROPERTIES
I think this is an INTERSECT of 1 and 2.
*/
Thanks,
Pedro
September 12, 2017 at 4:00 am
I might be missing something, but i can't see any kind of relationship between properties and opportunities. How do you know a property is applicable to a opportunity (and vice versa) or not?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 12, 2017 at 4:04 am
They are different entities that only relate through idType, rooms and the features lists.
Imagine you go to a real-estate agent and you want an apartment (idType) with 2 rooms (rooms) with elevator (feature), garage (feature), oven (feature), ....
That's an opportunity. Then he has his properties and will try to match them to your opportunity.
September 12, 2017 at 4:12 am
PiMané - Tuesday, September 12, 2017 4:04 AMThey are different entities that only relate through idType, rooms and the features lists.
Imagine you go to a real-estate agent and you want an apartment (idType) with 2 rooms (rooms) with elevator (feature), garage (feature), oven (feature), ....
That's an opportunity. Then he has his properties and will try to match them to your opportunity.
Sorry, but I still don't really understand. Are you saying that idType in opportunity joins to idType in properties? If so, the syntax is just a simple JOIN: JOIN opportunities o ON p.TypeID = o.TypeID;
If not, can you provide your expected output?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 12, 2017 at 4:27 am
Thom A - Tuesday, September 12, 2017 4:12 AMPiMané - Tuesday, September 12, 2017 4:04 AMThey are different entities that only relate through idType, rooms and the features lists.
Imagine you go to a real-estate agent and you want an apartment (idType) with 2 rooms (rooms) with elevator (feature), garage (feature), oven (feature), ....
That's an opportunity. Then he has his properties and will try to match them to your opportunity.Sorry, but I still don't really understand. Are you saying that idType in opportunity joins to idType in properties? If so, the syntax is just a simple JOIN: JOIN opportunities o ON p.TypeID = o.TypeID;
If not, can you provide your expected output?
I already did in the comments I place in the SQL section of the tables.
The simple part is the JOIN between idType and rooms... Now I want to "join" features also.
If I have an opportunity with feature 1 and 2 and a property if feature 2 the opportunity will be ok for the property since the property only requires feature 1 and the opportunity has 1 and 2, but the property wont be ok for the opportunity since the opportunity requires 1 and 2 and property only has 1.
September 12, 2017 at 4:43 am
PiMané - Tuesday, September 12, 2017 4:27 AMI already did in the comments I place in the SQL section of the tables.
The comments are a descriptive list of what you want to achieve, not an expected output. Could you provide the expected output please?
It could be, as well, that everything you have here are numbers and you're describing them as features. 1 and 2 aren't features; a fountain or a garage is a feature. At least for me, that makes it difficult to visualise, as the number don't appear to actually represent anything, they're all a selection of ID's.
As I said before, expected data will be extremely helpful here. If you have representative data, that would also help greatly, rather than just giving numbers as names. Also, could you describe the relationships between the tables, or add keys to them, so we know how each table relates to another, as it isn't inherently clear right now. For example, does propertiesFeatures.idFeature have a many to one relationship to opportunitiesFeatures.idFeature or is it the other way round, or is idFeature actually a foreign key to another table we're missing? If so, where is that table and can you provide some sample data to go with it? What about idType? Which way does the many to one relationship go, or is this another foreign key we might be missing?
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 12, 2017 at 4:54 am
To give an example, to below gives some sample data, with representative names. It also gives details of the relationsips (via a foreign key). Then i go on to give a brief describe of what I want and a sample SELECT statement that gives that result. you could also, however, give it in a result set structure, like is included below it:CREATE TABLE Customer
(CustomerID int IDENTITY(1,1) PRIMARY KEY,
CustomerName varchar(10));
CREATE TABLE CustomerOrder
(OrderID int IDENTITY(1,1) PRIMARY KEY,
CustomerID int,
OrderValue decimal(8,2));
ALTER TABLE CustomerOrder WITH CHECK ADD CONSTRAINT Customer_Order_FK FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID);
GO
INSERT INTO Customer (CustomerName)
VALUES ('Steve'),('John'),('Jane');
INSERT INTO CustomerOrder (CustomerID, OrderValue)
VALUES (1, 100),
(1, 150),
(3, 500);
GO
--I want a sum of the customers order values, but also include customers that did not place Orders, so like this:
--Expected Output Dataset
SELECT 'John' AS Customer, 250 AS TotalValue UNION
SELECT 'Steve' AS Customer, 0 AS TotalValue UNION
SELECT 'Jane' AS Customer, 500 AS TotalValue
--Expected output, resukt set
/*
Customer TotalValue
-------- -----------
Jane 500
John 250
Steve 0
*/
--Clean up
GO
DROP TABLE CustomerOrder;
DROP TABLE Customer;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 12, 2017 at 7:17 am
I'm not sure that you really need to look at it from both points of view, as once you start matching properties to opportunities, the perspective doesn't seem to me to be any different, but then, I can't know for sure. However, I suspect this will do what you're looking for:/* just going to add type and rooms */
CREATE TABLE #opportunities (id INT, idType TINYINT, rooms TINYINT)
CREATE TABLE #opportunitiesFeatures (idOpportunity INT, idFeature INT)
CREATE TABLE #properties (id INT, idType TINYINT, rooms TINYINT)
CREATE TABLE #propertiesFeatures (idProperty INT, idFeature INT)
INSERT INTO #opportunities VALUES (1, 1, 3), (2, 1, 2)
INSERT INTO #opportunitiesFeatures VALUES (1, 1), (1,2), (2,1), (2,3)
INSERT INTO #properties VALUES (1, 1, 3), (2, 1, 2), (3, 1, 3)
INSERT INTO #propertiesFeatures VALUES (1, 1), (2,1), (2, 2), (2, 3)
/* BASIC MATCH WITHOUT FEATURES */
SELECT O.id AS idOpportunity, P.id AS idProperty
FROM #opportunities AS O
INNER JOIN #properties AS P
ON O.idType = P.idType
AND O.rooms = P.rooms
CROSS APPLY
(
SELECT COUNT(*) AS ROW_COUNT
FROM (
SELECT OFT.idFeature
FROM #opportunitiesFeatures AS OFT
WHERE OFT.idOpportunity = O.id
EXCEPT
SELECT PF.idFeature
FROM #propertiesFeatures AS PF
WHERE PF.idProperty = P.id
) AS X
) AS R
WHERE R.ROW_COUNT = 0;
DROP TABLE #opportunities;
DROP TABLE #opportunitiesFeatures;
DROP TABLE #properties;
DROP TABLE #propertiesFeatures;
I have to wonder, however, if you might be too strict on the rooms equality. If a property has more rooms than a client wants, that might not be a deal breaker if the price is in the right range. If that's the case, a <= operator might be called for. The results from my query return just 1 record, identifying Opportunity id = 2 and Property id = 2. Would that be a correct result? It would appear to be, based on your description within your SQL portion of your post... Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 12, 2017 at 8:22 am
Thanks...
I know about the rooms.. probably will be making rooms between -1 and +1.
About the features I was trying to match the properties with opportunities features and the join count had to be greater or equal than the opportunity count (this to get the properties that match opportunities).
Pedro
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply