I've tried the SQL in two ways:
SELECT DISTINCT p.ponumber, i.item, p.invoicenumber, p.vendor, p.cnumber, i.cnumber, p.state, p.podate
FROM purchaseorders p JOIN itemsordered i
ON p.cnumber=i.cnumber
Where 0=0
and
SELECT p.ponumber, i.item, p.invoicenumber, p.vendor, p.cnumber, i.cnumber, p.state, p.podate
FROM purchaseorders p JOIN itemsordered i
ON p.cnumber=i.cnumber
Where Exists (
Select * from itemsordered i where p.cnumber=i.cnumber
)
But is still grabs duplicate records from the itremsordered table. There the cnumber field is displayed for each item that is ordered. The purchaseorders table has the cnumber once since it just contains contact info.
How can I just display one record for each cnumber when I do a search of all records for instance?
Robert O.
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 16, 2004 12:59 PM
To: CF-Talk
Subject: RE: SQL query style (WAS: SQL search query)
> While we are on the subject I have noticed recently that more
> and ore people are joining tables using the...
>
> FROM table1 x JOIN table2 y ON x.ID = y.ID
>
> And I am wondering if there is a reason for this. Sure I use
> this syntax for my outer joins but inner joins I still do the
> old fashioned way in the where clause, for example
>
> FROM table1 x, table2 y
> WHERE x.ID = y.ID
>
> This has the added benefit of taking the place of the 'WHERE
> 0=0' line we recently discussed. What are the benefits of
> one form of inner join over the other or is it just today's
> SQL fashion trend?
It's hardly a fashion trend - it's the law (ANSI SQL 92 specification). Most
modern databases support explicit JOINs.
It provides two benefits. Jochem mentioned that it separates filters from
join conditions. As a byproduct of this, it prevents unintentional Cartesian
products or cross joins, which can easily happen when you perform joins in a
WHERE clause.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
