JOIN operators

[jt_advance]

I am querying 2 datasets at once, my first data set is a customers table and
the second is a list of contacts for each customer record. my problem is that
when i search this combined dataset, the results only show those customers who
also have a linked record in the contacts table.

Not all customers have contacts listed and have no linked records at all in
the contacts table, so when querying my dataset if i want to search by customer
name alone I effectivley can only search those customers with contacts.

Any help would be greatly appreciated as this is clowing down my development
time considerably.

[Jon Spivey]

Hi,

SELECT * FROM
Customers C LEFT OUTER JOIN Contacts Co ON C.CustomerID = Co.CustomerID

Left outer join pulls all records from customers and shows contacts only
when they exist.

Jon



"jt_advance" <webforumsuser@macromedia.com> wrote in message
news:d2ec70$qd2$1@forums.macromedia.com...

>I am querying 2 datasets at once, my first data set is a customers table
>and
> the second is a list of contacts for each customer record. my problem is
> that
> when i search this combined dataset, the results only show those customers
> who
> also have a linked record in the contacts table.
>
> Not all customers have contacts listed and have no linked records at all
> in
> the contacts table, so when querying my dataset if i want to search by
> customer
> name alone I effectivley can only search those customers with contacts.
>
> Any help would be greatly appreciated as this is clowing down my
> development
> time considerably.
>

[jt_advance]

hi thanks for your quick response.

I've tried this query in an SQL database and it works fine, however when i
write it into my dreamweaver code the results appera as before where only
customers with contacts are displayed.

would there be something else in my code i needed to change?

[jt_advance]

here is the code i am using for this query




SELECT Account, Customer, Name AS ConName FROM Customers LEFT OUTER JOIN
Customers_Contacts ON Customers.ID = Customers_Contacts.CustomerID WHERE Name
LIKE ?

[Lionstone]

If Name is from the Contacts table, then your outer join doesn't do any
good. It will keep the customers with no contacts initially, but they'll be
discarded by the where clause because the contact name doesn't match your
filter.

--
-------------------------------------------------------------------
You must SHOW YOURSELF to e-mail me. No cowards allowed!




"jt_advance" <webforumsuser@macromedia.com> wrote in message
news:d2ei5d$66r$1@forums.macromedia.com...

> here is the code i am using for this query
>
>
>
>
> SELECT Account, Customer, Name AS ConName FROM Customers LEFT OUTER JOIN
> Customers_Contacts ON Customers.ID = Customers_Contacts.CustomerID WHERE

Name

> LIKE ?
>