Joins versus relationships

If a database has relationships establshed between all of the tables
via primary and foreign key constraints, why isn't is possible to make
a SELECT statement across multiple tables without using a JOIN?

If the system knows the relationsip schema already why are JOINS
required?

Thanks,
HCHi

It is not always that easy! Not every database is fully normalised and there
can be mutiple relationships or missing ones. Doing the extra work to figure
out the relationship is going to take extra time and resource.

Also, how do you declare the different types of JOIN?

John

"H Cohen" <harris_cohen@.yahoo.com> wrote in message
news:1545331c.0408150629.1ffa0575@.posting.google.c om...
> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC|||"H Cohen" <harris_cohen@.yahoo.com> wrote in message
news:1545331c.0408150629.1ffa0575@.posting.google.c om...
> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC

Well, for a start what type of join would it be - inner, outer, cross? And
what would you join on - you might not want to join on col1 = col2, you
might want to join on col1 < col2, or col1-1 = col2 etc. Or you might want
to join on non-key columns. And you would have to assume that every database
is normalised, there is only one possible relationship between each pair of
tables, and all the relationships are enforced correctly, which is unlikely
to be true all the time.

If you specify what you want explicitly then it's clear to others reading
your code what you intended, and it also makes it easier to handle schema
changes and other code changes without the added confusion of the system
'automagically' doing things for you.

I suspect you're thinking mainly of the simplest possible case - join two
tables with an inner join using an equality comparison. While I suppose you
could introduce some kind of meta-syntax to avoid fully typing out the
primary key column names, that would be a false economy compared to the
potential issues, and of course it wouldn't work at all in some of the cases
I mention above.

Simon|||>> If a database has relationships establshed between all of the
tables via primary and foreign key constraints, why isn't is possible
to make a SELECT statement across multiple tables without using a
JOIN? <<

UNH?? A SELECT statement with two or more tables in the FROM clause
has at least a CROSS JOIN in it, even without a WHERE clause.

>> If the system knows the relationship schema already why are JOINS
required? <<

For the same reason you have to do math to get answers from numbers.
This makes no sense. Are you thinking about an old network database
like IMS or IDMS, or whatever that had pointer chains to navigate
along pre-defined acces paths?|||HC,

I believe Oracle and possibly other database systems implement something like NATURAL JOIN which infers a join condition of equality on
all like-named columns, but SQL Server always requires the join condition to be supplied.

Steve Kass
Drew University

H Cohen wrote:

> If a database has relationships establshed between all of the tables
> via primary and foreign key constraints, why isn't is possible to make
> a SELECT statement across multiple tables without using a JOIN?
> If the system knows the relationsip schema already why are JOINS
> required?
> Thanks,
> HC

JOINing the same table

This is probably something simple I'm missing, but here it is anyway.
I have a table called "Employee". Primary key is "pk_EmployeeID".
There is a foreign key field called "fk_SupervisorID" which relates to
pk_EmployeeID. The object is to pull an employee's supervisor from the
same table.
I can't seem to get past a basic SELECT statement to run more complex
queries. Here's what I'm trying:
SELECT Employee.LastName AS EmpLastName, Sup.LastName AS SupLastName
FROM Employee
' JOIN Employee AS Sup ON Sup.fk_SupervisorID =
Employee.pk_EmployeeID
I've tried inner joins, outer joins, left, right... you name it. The
results I get are always putting the "Employee's" last name in the
Supervisor's (SupLastName) column.
A LEFT JOIN duplicates the supervisors giving me more records than is
actually in the table (which I thought would occur for a RIGHT JOIN)
and a RIGHT JOIN gives me the correct record count, still with botched
name fields. INNER JOIN also botches the name fields but does what it
is supposed to by not including the few records that don't have a
supervisor.
Any ideas?
Thanks in advance!!--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe you should have something like this:
CREATE TABLE Employees (
EmployeeID integer not null primary key ,
Name varchar(20) not null ,
- -- ... other columns ...
SupervisorID integer references Employees (EmployeeID)
)
SELECT E.LastName AS EmpLastName, S.LastName AS SupLastName
FROM Employees As E INNER JOIN Employees AS S
ON E.SupervisorID = S.EmployeeID
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQgfl54echKqOuFEgEQJwFwCggJAWohcqvWQK
QWUNBHdVeliRvDUAoIod
3gHlfBy2yj0p8/J4KRWPjffP
=TMY3
--END PGP SIGNATURE--
Wally wrote:
> This is probably something simple I'm missing, but here it is anyway.
> I have a table called "Employee". Primary key is "pk_EmployeeID".
> There is a foreign key field called "fk_SupervisorID" which relates to
> pk_EmployeeID. The object is to pull an employee's supervisor from the
> same table.
> I can't seem to get past a basic SELECT statement to run more complex
> queries. Here's what I'm trying:
> SELECT Employee.LastName AS EmpLastName, Sup.LastName AS SupLastName
> FROM Employee
> ' JOIN Employee AS Sup ON Sup.fk_SupervisorID =
> Employee.pk_EmployeeID
> I've tried inner joins, outer joins, left, right... you name it. The
> results I get are always putting the "Employee's" last name in the
> Supervisor's (SupLastName) column.
> A LEFT JOIN duplicates the supervisors giving me more records than is
> actually in the table (which I thought would occur for a RIGHT JOIN)
> and a RIGHT JOIN gives me the correct record count, still with botched
> name fields. INNER JOIN also botches the name fields but does what it
> is supposed to by not including the few records that don't have a
> supervisor.|||Are you saying you have a primary key that is been referenced as a
foreignkey in the same table? WHYyyyy' Anyways, to solve your problem
for now, here is the query.....
select E.LastName as EmpLastName,
(Select LastName as SupLastName from Employees Where employeeID =
E.SupervisorID)
from Employee E|||Query Builder wrote:
> Are you saying you have a primary key that is been referenced as a
> foreignkey in the same table? WHYyyyy' Anyways, to solve your
problem
> for now, here is the query.....
No... there is no actual reference between the fields. As far as SQL
Server is concerned, pk_SupervisorID is an indexed primary key.....
and fk_SupervisorID is just another unreferenced column with data in
it. The relation of the two fields only occurs in reports that the
front-end calls for.

> select E.LastName as EmpLastName,
> (Select LastName as SupLastName from Employees
> Where employeeID = E.SupervisorID)
> from Employee E
FYI: Your SELECT statement worked fine, but for some odd reason it
didn't label the SupLastName field.
Thanks!|||> SELECT E.LastName AS EmpLastName, S.LastName
> AS SupLastName
> FROM Employees As E INNER JOIN Employees AS S
> ON E.SupervisorID = S.EmployeeID
That worked too. Thanks!
And as I originally said... it was something small that I was missing.
:-|
Oh well... Thanks again!

Joining different data types

I have a table, I'll call contacts, where the primary key is called id and
it's an integer field. I have another table, I'll call tempcontacts, and
it's primary key is id but it's a guid for many reasons I won't go into now.
These 2 tables are never combined in the same query.
Table 3 is a ClientDetails table, with the ClientID a varchar to accommodate
either a guid or an integer. This works okay until I try to do a query with
joins, I can't join either table's id with the ClientDetails
ClientID/varchar field. Can I use convert or cast in my query? Or how can
I weed out, for instance, only the numeric ClientID's, then join it.
Thanks for your help, I'm really not sure what to do about this.You can do both, but converting the joined columns will lead into
performace issues.
HTH, jens Suessmeyer.|||Uniqueidentifier has a higher data-type precedence that varchar, so when
comparing the varchar field to the uniqueidentifier field, SQL tries to
implicitly convert the varchar field to uniqueidentifier. You get the error
when it tries to convert one of the ClientID values that represents an
integer into uniqueidentifier.
Try
SELECT
....
FROM Contacts
INNER JOIN ClientDetails ON Contacts.id =
CASE WHEN ISNUMERIC(ClientDetails.ClientID) = 1 THEN ClientDetails.ClientID
ELSE -10 END
--The key thing here is that the CASE expression returns the value of
ClientDetails.ClientID to -10 whenever ClientID is not numeric (in other
words, whenver it is a uniqueidentifier). I chose -10 because I'm guessing
there are no negative values of ID in your contacts table. If there are,
adjust accordingly.
For the other table.
SELECT
...
FROM tempcontacts
INNER JOIN ClientDetails ON tempcontacts.id =
CASE WHEN ISNUMERIC(ClientDetails.ClientID) = 0 THEN ClientDetails.ClientID
ELSE NEWID() END
Again, the logic here is that for any integer values of
ClientDetails.ClientID, the value for the uniqueidentifier will be set to a
value returned by NEWID(), which (hopefully) will not match anything in
tempcontacts.id.
dew" wrote:

> I have a table, I'll call contacts, where the primary key is called id and
> it's an integer field. I have another table, I'll call tempcontacts, and
> it's primary key is id but it's a guid for many reasons I won't go into no
w.
> These 2 tables are never combined in the same query.
> Table 3 is a ClientDetails table, with the ClientID a varchar to accommoda
te
> either a guid or an integer. This works okay until I try to do a query wi
th
> joins, I can't join either table's id with the ClientDetails
> ClientID/varchar field. Can I use convert or cast in my query? Or how ca
n
> I weed out, for instance, only the numeric ClientID's, then join it.
> Thanks for your help, I'm really not sure what to do about this.
>
>

joining a table to a user-defined function?

Suppose I have a SQL Server table named 'gadget'. 'gadget' has an integer field named 'gadget_key', which is the primary key of the table.

Now suppose I have a user-defined function named 'udf_gadget_values'. This function takes as its input parameter an integer variable named '@.nGadgetKey'. This function returns a table which will always contain exactly one record. This one record has one field named 'nGadgetKey', which contains the same value that was passed to the function in parameter '@.nGadgetKey'

I would like to join the table 'gadget' with the table that function 'udf_gadget_values' returns kind of like this:

SELECT TOP 10 *
FROM gadget, udf_gadget_values(gadget.gadget_key)

The purpose of this query is to get the top 10 records from 'gadget', as well as the values associated with each record, as returned by the function.

The real issue is this: how do I pass gadget.gadget_key to the function as an input parameter? Or if this will not work, is there an alternative?

Hi,

This syntax is neither supported in Yukon nor Shiloh. I believe the problem is that the output rowset cannot be materialized until the function is evaluated, and yet, the function cannot be evaluated until the output rowset is materialized.

In Yukon we've introduced a new relational operator called CROSS APPLY that you could use in scenarios like this. The LHS of CROSS APPLY is a table source and the RHS is a table-valued function. The formal input parameters of the function can be bound to actual column values materialized in the LHS rowset. In other words, for each row of the LHS, evaluate the function on the RHS and JOIN the results to the LHS, resulting in >=1 row in the ultimate output rowset. In essense, it solves the problem described above by assigning a formal and well-defined evaluation strategy to the LHS and RHS of the CROSS APPLY.

It would look like this:

select * from gadget cross apply udf_gadget_values(gadget_key)

Moreover, in Yukon, we've changed the parser to allow function input parameters to bind to correlated subqueries in FROM clause and in the projection list. The examples below illustrate:

-- Yukon : works
-- Shiloh: !works
select * from gadget where exists
(select * from udf_gadget_values(gadget.gadget_key))

-- Yukon : works
-- Shiloh: !works
select *
, (select gadget_desc
from dbo.udf_gadget_values(gadget.gadget_key))
as function_value
from gadget

Regards,
Clifford Dibble
Program Manager, SQL Server

Joining 5 tables

Hi,

I have to join five tables in sql server using primary and foreign keys .I have joine all the tables

but problem was duplicate rows were repeating.Could any body help me to resolve this problem.

Thanks in advance.

Regards,

Raja.

Could you post the query and the table relationships? No one will be able to help you without that.

|||

1.Feature Column NameDatatypeSizeConstraints1FeatureIdUniqueIdentifiernot null, primary key2FeatureNamevarchar255not null 2.Products 1ProductIdUniqueIdentifiernot null,primary key2ProductNamevarchar255not null 3.Feature_Product 1FeaturIdUniqueIdentifiernot null,foreign key Feature2ProductIdUniqueIdentifiernot null,foreign key Products 4.Customer 1CustomerIdUniqueIdentifiernot null,primary key2CustomerNamevarchar255not null3Emailidvarchar255not null4Mobilevarchar2555Faxvarchar2556Telephonevarchar2557Addressvarchar5008TypeIdintidentityforeign key customertype 5.Customer_Product 1CustomerIdUniqueIdentifiernot null,foreign key Customer2ProductIdUniqueIdentifiernot null,foreign key Products 6.License 1LicenseIdUniqueIdentifiernot null,primary key2Typevarchar255not null3Release_datedatetimenot null4License_Versionvarchar255not null5StartDatedatetime6EndDatedatetime7HostId1varchar2558HostId2varchar2559HostId3varchar25510HostName1varchar25511HostName2varchar25512HostName3varchar25513IPAddress1varchar25514IPAddress2varchar25515IPAddress3varchar25516No_Licensesint17CustomerIdUniqueIdentifiernot null,foreign key Customer18MailTovarchar255 7.License_Product 1LicenseIdUniqueIdentifiernot null,foreign key License2ProductIdUniqueIdentifiernot null,foreign key Products

select ProductName,FeatureName,LicenseType, REPLACE(CONVERT(VARCHAR(11), ReleaseDate, 106), ' ', '-') AS [ReleaseDate],LicenseVersion,REPLACE(CONVERT(VARCHAR(11), StartDate, 106), ' ', '-') AS [StartDate],REPLACE(CONVERT(VARCHAR(11), EndDate, 106), ' ', '-') AS [EndDate],HostID1,HostID2,HostID3,HostName1,HostName2,HostName3,ipaddress1,IPAddress2,IPAddress3,No_Licenses,MailTo,CustomerName,EmailID,Mobile,Fax,Telephone,Address from products p,feature f,feature_product fp,license_product lp, Customer_product cp, license l,customers c where p.productid = fp.productid
and f.featureid = fp.featureid
and l.licenseid = lp.licenseid
and c.customerid = l.customerid
and p.productid = cp.productid
and p.productid = lp.productid
and c.customerid = cp.customerid

Hi Prasanth,

These are the tables and relationship for my task and I worked with that code but duplicate rows are generated

could you help me from this iisue.

ThankYou,

Rajasekhar.

|||

1.Feature Column NameDatatypeSizeConstraints1FeatureIdUniqueIdentifiernot null, primary key2FeatureNamevarchar255not null 2.Products 1ProductIdUniqueIdentifiernot null,primary key2ProductNamevarchar255not null 3.Feature_Product 1FeaturIdUniqueIdentifiernot null,foreign key Feature2ProductIdUniqueIdentifiernot null,foreign key Products 4.Customer 1CustomerIdUniqueIdentifiernot null,primary key2CustomerNamevarchar255not null3Emailidvarchar255not null4Mobilevarchar2555Faxvarchar2556Telephonevarchar2557Addressvarchar5008TypeIdintidentityforeign key customertype 5.Customer_Product 1CustomerIdUniqueIdentifiernot null,foreign key Customer2ProductIdUniqueIdentifiernot null,foreign key Products 6.License 1LicenseIdUniqueIdentifiernot null,primary key2Typevarchar255not null3Release_datedatetimenot null4License_Versionvarchar255not null5StartDatedatetime6EndDatedatetime7HostId1varchar2558HostId2varchar2559HostId3varchar25510HostName1varchar25511HostName2varchar25512HostName3varchar25513IPAddress1varchar25514IPAddress2varchar25515IPAddress3varchar25516No_Licensesint17CustomerIdUniqueIdentifiernot null,foreign key Customer18MailTovarchar255 7.License_Product 1LicenseIdUniqueIdentifiernot null,foreign key License2ProductIdUniqueIdentifiernot null,foreign key Products

select ProductName,FeatureName,LicenseType, REPLACE(CONVERT(VARCHAR(11), ReleaseDate, 106), ' ', '-') AS [ReleaseDate],LicenseVersion,REPLACE(CONVERT(VARCHAR(11), StartDate, 106), ' ', '-') AS [StartDate],REPLACE(CONVERT(VARCHAR(11), EndDate, 106), ' ', '-') AS [EndDate],HostID1,HostID2,HostID3,HostName1,HostName2,HostName3,ipaddress1,IPAddress2,IPAddress3,No_Licenses,MailTo,CustomerName,EmailID,Mobile,Fax,Telephone,Address from products p,feature f,feature_product fp,license_product lp, Customer_product cp, license l,customers c where p.productid = fp.productid
and f.featureid = fp.featureid
and l.licenseid = lp.licenseid
and c.customerid = l.customerid
and p.productid = cp.productid
and p.productid = lp.productid
and c.customerid = cp.customerid

Hi Prasanth,

These are the tables and relationship for my task and I worked with that code but duplicate rows are generated

could you help me from this iisue.

ThankYou,

Rajasekhar.