Is it possible to make use of database relationships in a join?

When you make a join, it may look something like this:

SELECT Products.ProductName from Products, Categories
WHERE Categories.CategoryName = 'Seafood' AND
Products.CategoryID = Categories.CategoryID

In this query you use 'Products.CategoryID = Categories.CategoryID' to give the relation between the two tables. But if you already added the relationships to your database, this should be known.

My question is, why is it necessary to add the relation in the query when the relationships are already in the database? Is it somehow possible to ommit the relation in the query and make use of the relationships in the database.

Kind regards,
Iwan RotteveelRelationships (also known as Declarative Referential Integrity constraints) are not for DML operations, but rather for enforcement of data integrity that can be affected by DML should there be no DRI on related tables.|||thats a good note...

Just so im clear, DDL as in INSERT, UPDATE, etc

DML as in JOIN etc?|||No,

DDL = Data Definition Language (CREATE, ALTER)

DML = Data Manipulation Language (INSERT, UPDATE, SELECT, DELETE)

Regards,

hmscott

thats a good note...

Just so im clear, DDL as in INSERT, UPDATE, etc

DML as in JOIN etc?|||And lest we forget...

DCL

EDIT: Damn...some lexicon...|||Did you mean "at least" or "...and lest" ?

-PatP

is it a bug ? (relationships in report builder)

Hi friends
am having some weird problem.am using sql server 2005 standard edition.

i've a report model with tables (tab1,tab2,tab3). tab1 and tab2 (actually these are views) relate to same table but each has different columns from same table.
the third table i.e. tab3 is child of tab1.

in my report model project,i set cardinality of this role in tab3 as "one" and in tab1 as "Optionalmany".

when creating a report in reportbuilder.if select columns from either tab1 and/or tab2 i get to see 100 records which is correct.if add any column from tab3 i get to see only 1 record which also correct as i've only 1 row tab3 at the moment.

now ,fun begins , what should happen if i delete column(s) of tab3 from report designer ? i should see all 100 records ,right ? bcoz all my columns coming from either tab1 or tab3 but when i run report i get see only 1 record !!! why ? is it a bug or am i missing something.
Thanks for your help.no one ever faced this situation ?
any ideas on this one much appreciated.|||

The difference between the first report that returned 100 rows and the third report that returned 1 row is the primary entity, which changed when you added a column from tab3, but did not revert when you deleted that column.

The primary entity of the first report is tab1, which means that report is fundamentally about tab1 and the data related to it.

The primary entity of the third report is tab3, which means that report is fundamentally about tab3 and the data related to it. Basically, the third report is a summary report for the data in tab3, grouped by tab1, which explains why there is only one row.

|||Thanks for the reply Bob.
does it mean that even if some one adds a field from tab3 accidentally ,he has to create the whole report from scratch so that he can see data that matches its criteria ?|||Bob
I think ,i kind of achieved what i want. please advise if i go into any problems in future.

what i did was i set cardinality for the roles on parent table side as "optionalone" and child entity side as "Optionalmany". (normally its other way round ,right?)
now my reports work just fine. i mean i get data am expecting and top of it if i remove the field from tab3 still my report worked displaying all records from tab1,tab2.

BTW when i deploy (using BI) i get a warning like below

"The Relation property of the Role 'tab3 detail' refers to the Target end of the Relation 'dd_tab1-dd_tab3', which is not bound to a set of uniquely constrained columns for the Table 'dbo.dd__tab3'. Roles with Cardinality of One or OptionalOne require relations bound to uniquely constrained columns of the table."

is it a problem ?
i checked sql the report builder making (in sql profiler) and they look fine as its placing joins correctly on both parent and child tables.
any suggestions on this much appreciated.
Thank you very much|||

No, you should not swap the cardinality of your report model roles to get different joins. RB relies on this information in many ways to provide a consistent and appropriately constrained query design experience to the user.

You are right that there is currently no way to revert the primary entity other than rebuilding your report. This feature was slated for SQL 2005 at one point, but unfortunately did not make it into this release.