Given the three tables (Type, Value, and Links), here is another (crazy
looking) way to structure the query for the Person table.
SELECT P.*
FROM Person P
WHERE
(
EXISTS
(
SELECT null
FROM StringLinks L
JOIN StringValue SV ON L.ID = SV.ID AND SV.refTypeID = 1
WHERE P.ID = L.parentID AND SV.f_val LIKE 'foo'
)
OR
(
NOT EXISTS
(
SELECT null
FROM StringLinks L
JOIN StringValue SV2 ON L.ID = SV.ID AND SV.refTypeID = 1
WHERE P.ID = L.parentID AND SV.f_val LIKE 'foo'
)
AND
EXISTS
(
SELECT null
FROM StringType ST
WHERE ST.ID = 1 AND ST.defaultVal LIKE 'foo'
)
)
)
AND
(
EXISTS
(
SELECT null
FROM StringLinks L
JOIN StringValue SV ON L.ID = SV.ID AND SV.refTypeID = 1
WHERE P.ID = L.parentID AND SV.f_val LIKE 'bar'
)
OR
(
NOT EXISTS
(
SELECT null
FROM StringLinks L
JOIN StringValue SV2 ON L.ID = SV.ID AND SV.refTypeID = 1
WHERE P.ID = L.parentID AND SV.f_val LIKE 'bar'
)
AND
EXISTS
(
SELECT null
FROM StringType ST
WHERE ST.ID = 1 AND ST.defaultVal LIKE 'bar'
)
)
)
-----Original Message-----
From: Robert DiFalco
Sent: Friday, August 18, 2006 12:37 PM
To: Robert DiFalco; '[email protected]'
Subject: RE: More query help for user-defined values
No takers?
-----Original Message-----
From: Robert DiFalco
Sent: Thursday, August 17, 2006 3:03 PM
To: Robert DiFalco; [email protected]
Subject: RE: More query help for user-defined values
One simple solution is to denormalize a little and take the refTypeID
column out of StringValue and add it to the Links table. Then I could
have:
SELECT P.*
FROM Person P
LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1
LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID
LEFT JOIN StringType ST1 ON ST1.ID = 1
LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2
LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID
LEFT JOIN StringType ST2 ON ST2.ID = 2
WHERE ( SV1.f_val LIKE 'foo' OR
( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
AND
( SV2.f_val LIKE 'bar' OR
( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
-----Original Message-----
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 17, 2006 8:43 AM
To: [email protected]
Subject: More query help for user-defined values
To support user defined properties I have the following tables:
TABLE StringType
LONG ID PKEY
VARCHAR name
...
VARCHAR defaultValue
TABLE StringValue
LONG parentID
LONG typeID
VARCHAR val
Assume the correct indices are in place. Different string value types
can be defined by giving them a new row in StringType. The
StringValue.parentID can be a row from any table that has a LONG
identity column. If a row in the parent table does not have a
corresponding row in StringValue, we want to assume an implicit default
value.
So to return all the Person rows that have either an explicit
StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I
can just execute the following query.
SELECT P.*
FROM Person P
LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID
= 1
LEFT JOIN StringType ST1 ON ST1.ID = 1
LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID
= 2
LEFT JOIN StringType ST2 ON ST2.ID = 2
WHERE ( SV1.f_val LIKE 'foo' OR
( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
AND
( SV2.f_val LIKE 'bar' OR
( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
The above seems to work fine. I can also get rid of the constant joins
on the type table and just do a subquery for the default value, either
approach works.
Now I want to change things to introduce a ValueLink table so that if
100 rows have the same string value that I don't have to write the
string 100 times. So my table structure changes like this:
TABLE StringType
LONG ID PKEY
VARCHAR name
...
VARCHAR defaultValue
TABLE StringValue
LONG ID PKEY // the value unique ID
LONG typeID
VARCHAR val
TABLE StringLinks
LONG parentID PKEY
LONG valueID PKEY
My naive approach to the query was this:
SELECT P.*
FROM Person P
LEFT JOIN StringLinks L ON P.ID = L.parentID
LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1
LEFT JOIN StringType ST1 ON ST1.ID = 1
LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2
LEFT JOIN StringType ST2 ON ST2.ID = 2
WHERE ( SV1.f_val LIKE 'foo' OR
( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
AND
( SV2.f_val LIKE 'bar' OR
( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
As you can probably tell this wont work because the two values will now
show up in two different rows so the AND of the search terms will always
fail.
Can anyone think of a clean way to do this or do I have to get rid of
the joins altogether and do a subquery for each StringValue search term?
TIA,
R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
