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

Join tables Count problems

Help,

I have 2 tables one called ProjectHeader and the other called ProjectSuppliers. There is a one to many relationships. I’m trying to get a count of the total number of projects for a certain supplier where the Date Entered is less then 5/1/2206 with the Bid Date greater than 3/31/2206.

When I run the query below the count comes back wrong. I know it’s because the ProjectSupplier table can have multiply entries of the supplier under the same Projectid.

For example, the query below gives me a count of 206 projects for Miller in Tampa.

Where he really only has 169 projects entered.

I know you can run two queries to get the results.

But because the data is being display on a Web page, I really would like the one SQL statement to handle this.

ProjectHeader Table	ProjectSuppliers Table
ProjectID	ProjectID
Bid Date	SupplierName
	Date Entered
	District
	Modifed By

SELECT Count(ProjectHeader.ProjectID) AS CountOfProjectID, ProjectHeader.District, ProjectSuppliers.[Modifed By]

FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID = ProjectHeader.ProjectID

WHERE (((ProjectSuppliers.[Bid Date])>#3/31/2006#) AND ((ProjectSuppliers.SupplierName)='Some Supplier') AND ((ProjectSuppliers.[Date Entered])<#5/1/2006#))

GROUP BY ProjectHeader.District, ProjectSuppliers.[Modifed By]

HAVING (((Count(ProjectHeader.ProjectID))>0))

ORDER BY Count(ProjectHeader.ProjectID) DESC , ProjectHeader.District, ProjectSuppliers.[Modifed By];

Hi,

I tried to simplify the syntax you have used in the script

And I end up with the below one (I changed column names for simplicity)

SELECT
Count(PH.ProjectID) AS CountOfProjectID,
PH.District,
PS.ModifedBy
FROM ProjectSuppliers PS
INNER JOIN ProjectHeader PH ON PS.ProjectID = PH.ProjectID
WHERE
PS.BidDate > #3/31/2006#
AND PS.SupplierName ='Some Supplier'
AND PS.DateEntered < #5/1/2006#
GROUP BY PH.District, PS.ModifedBy
HAVING Count(PH.ProjectID) > 0
ORDER BY Count(PH.ProjectID) DESC, PH.District, PS.ModifedBy

The first problem I see is that there is not a "Bid Date" column in Project Suppliers, so this query will not work.

Am I missing something?

|||

I believe all you have to do is add the distinct keyword to make what you want to work.

Select count(distinct ProjectHeader.ProjectID)AS CountOfProjectID, ProjectHeader.District, ProjectSuppliers.[Modifed By]
FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID = ProjectHeader.ProjectID
WHERE (((ProjectSuppliers.[Bid Date])>#3/31/2006#) AND ((ProjectSuppliers.SupplierName)='Some Supplier') AND ((ProjectSuppliers.[Date Entered])<#5/1/2006#))
GROUP BY ProjectHeader.District, ProjectSuppliers.[Modifed By]
HAVING (((Count(ProjectHeader.ProjectID))>0))
ORDER BY Count(distinct ProjectHeader.ProjectID) DESC , ProjectHeader.District, ProjectSuppliers.[Modifed By];

I haven't bothered checking if my assumption is right...but I think this should solve your problem of duplicate bids being counted twice.

|||I think he juxtaposed biddate and dateentered on the table. In that case he'd need to switch it in the query as well.|||

Try this query.

SELECT Count(ProjectSuppliers.SupplierName) AS CountOfProjectID, ProjectSuppliers.District, ProjectSuppliers.ModifiedBy
FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID=ProjectHeader.ProjectID
WHERE (((ProjectHeader.BidDate)>#3/31/2006#) And ((ProjectSuppliers.SupplierName)='Some Supplier') And ((ProjectSuppliers.DateEntered)<#5/1/2006#))
GROUP BY ProjectSuppliers.District, ProjectSuppliers.ModifiedBy
HAVING (((Count(ProjectSuppliers.SupplierName))>0));

I used these tables and filled with some dummy data.

ProjectHeader ProjectID BidDate 1 3/30/2006 10 4/1/2006 2 3/29/2006 3 2/25/2006 4 5/5/2006 5 4/15/2006 6 4/8/2006 7 5/1/2006 8 4/10/2006 9 5/2/2006

ProjectSuppliers ProjectID SupplierName DateEntered District ModifiedBy 4 Other Supplier 4/25/2009 Miami 4/20/2006 3 Other Supplier 4/27/2006 Tampa 4/29/2006 2 Other Supplier 4/29/2006 Miami 4/30/2006 1 Other Supplier 5/11/2006 Tamp 4/16/2006 9 Other Supplier 4/19/2006 Miami 4/15/2006 8 Some Supplier 4/19/2006 Tampa 4/15/2006 7 Some Supplier 4/18/2006 Tampa 4/8/2006 6 Some Supplier 4/20/2006 Tampa 4/8/2006 5 Some Supplier 4/20/2006 Tampa 4/11/2006 4 Some Supplier 4/25/2006 Tampa 4/20/2006 3 Some Supplier 4/27/2006 Tampa 4/29/2006 2 Some Supplier 4/29/2006 Tampa 4/30/2006 1 Some Supplier 5/11/2006 Tampa 4/16/2006

Please let me know if it worked.

-Ujj

|||

Try this query.

SELECT Count(ProjectSuppliers.SupplierName) AS CountOfProjectID, ProjectSuppliers.District, ProjectSuppliers.ModifiedBy
FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID=ProjectHeader.ProjectID
WHERE (((ProjectHeader.BidDate)>#3/31/2006#) And ((ProjectSuppliers.SupplierName)='Some Supplier') And ((ProjectSuppliers.DateEntered)<#5/1/2006#))
GROUP BY ProjectSuppliers.District, ProjectSuppliers.ModifiedBy
HAVING (((Count(ProjectSuppliers.SupplierName))>0));

I used these tables and filled with some dummy data.

ProjectHeader

ProjectID	BidDate
1	3/30/2006
10	4/1/2006
2	3/29/2006
3	2/25/2006
4	5/5/2006
5	4/15/2006
6	4/8/2006
7	5/1/2006
8	4/10/2006
9	5/2/2006

ProjectSuppliers

ProjectID	SupplierName	DateEntered	District	ModifiedBy
4	Other Supplier	4/25/2009	Miami	4/20/2006
3	Other Supplier	4/27/2006	Tampa	4/29/2006
2	Other Supplier	4/29/2006	Miami	4/30/2006
1	Other Supplier	5/11/2006	Tamp	4/16/2006
9	Other Supplier	4/19/2006	Miami	4/15/2006
8	Some Supplier	4/19/2006	Tampa	4/15/2006
7	Some Supplier	4/18/2006	Tampa	4/8/2006
6	Some Supplier	4/20/2006	Tampa	4/8/2006
5	Some Supplier	4/20/2006	Tampa	4/11/2006
4	Some Supplier	4/25/2006	Tampa	4/20/2006
3	Some Supplier	4/27/2006	Tampa	4/29/2006
2	Some Supplier	4/29/2006	Tampa	4/30/2006
1	Some Supplier	5/11/2006	Tampa	4/16/2006

This is the result I got.

CountOfProjectID	District	ModifiedBy
1	Tampa	4/11/2006
1	Tampa	4/15/2006
1	Tampa	4/20/2006
2	Tampa	4/8/2006

Please let me know if it worked.

-Ujjwal Kaji Shrestha