Is this okay?
select * from contacts
Id ContactName
----------- --------------------------------------------------
1 Contact1
2 Contact2
3 Contact3
4 Contact4
select * from contactattributes
ContactId AttributeId
----------- --------------------------------------------------
1 Attrib1
2 Attrib2
3 Attrib3
4 Attrib1
4 Attrib2
select distinct c.*
from Contacts c inner join ContactAttributes ca on c.[id] = ca.[ContactId]
where ca.[AttributeId] in ('Attrib1', 'Attrib2')
order by [ContactName]
Id ContactName
----------- --------------------------------------------------
1 Contact1
2 Contact2
4 Contact4
-----Original Message-----
From: Roman Antchik [mailto:[EMAIL PROTECTED]
Sent: Friday, October 08, 2004 1:21 AM
To: [EMAIL PROTECTED]
Subject: Re: [ADVANCED-DOTNET] Abstracting SQL
>> Don't forget you can also put complicated and important
>> tables in memory
>> on middle tiers this allows you to scale to many users and
>> makes the DB
>> performance irrelevant. These middle tiers also allows your
>> OO design
>> to capture business logic that requires sorting , joins , groupings ,
>> exclusions etc and allows you to have much simpler SQL . ( ie just
>> simple selects and updates). In this case your SQL grammar
>> is so simple
>> it will be DB independent.
Thanks Ben. Unfortunately that is not an option:
1. Sheer volume of data.
2. Straightforward O/R mapper is not hard to implement - this is where we
stand now. It is the more complex stuff, like subqueries that got us
stuck.
Regardless, there is no support for subqueries in .NET framework
DataTable / DataSet classes anyway.
For example the only way to execute the query below - without subqueries -
would be to have two queries and a union.
Extracting all contacts that either have Attribute 1 or Attribute 2
(assuming there are three tables - Contacts, Attributes &
ContactAttributes):
SELECT * FROM Contacts
WHERE EXISTS
(
SELECT * FROM ContactAttributes
WHERE
Contact.Id = ContactAttributes.ContactId
AND
ContactAttributes.AttributeId = <Some Value>
)
OR EXISTS
(
SELECT * FROM ContactAttributes
WHERE
Contact.Id = ContactAttributes.ContactId
AND
ContactAttributes.AttributeId = <Some Other Value>
)
Thanks,
Roman.
===================================
This list is hosted by DevelopMentor� http://www.develop.com
Some .NET courses you may be interested in:
Essential .NET: building applications and components with CSharp
August 30 - September 3, in Los Angeles
http://www.develop.com/courses/edotnet
View archives and manage your subscription(s) at http://discuss.develop.com
===================================
This list is hosted by DevelopMentor� http://www.develop.com
Some .NET courses you may be interested in:
Essential .NET: building applications and components with CSharp
August 30 - September 3, in Los Angeles
http://www.develop.com/courses/edotnet
View archives and manage your subscription(s) at http://discuss.develop.com
