Re: [Phpgroupware-developers] Palm conduit for addbook

[Scott Courtney]

On Wednesday 17 July 2002 09:04 pm, Brian Johnson wrote:
> Can someone help me with the SQL to match the individuals with the
> companies.  I could do it if it were two separate tables but when they're
> mixed together, I get lost.  It seems that for each company record we need
> to search for any individual records and for each individual record we need
> to search for it's corresponding company record.  What if there are no
> individuals linked to the company?  Is this process slower than if it were
> in two separate tables?

I don't know phpGW itself well enough to give you the specifics for its schema,
but I can give you some generic SQL that might point you in the
right direction.

Let's say you have two tables, defined as follows:

create table company (
        id integer auto_increment,
        lname char(20),
        fname char(20),
        primary key (id),
        key (lname, fname)
);

create table personal (
        id integer auto_increment,
        lname char(20),
        fname char(20),
        primary key (id),
        key (lname, fname)
);

Assuming the combined names are the common value for joining the tables, you
can do the following:

select company.id, company.lname, company.fname
        from company left join personal
        on company.lname = personal.lname and company.fname = personal.fname
        having personal.id is null;

This will give you all the company IDs and names where there is no matching
record in the personal table.

select personal.id, personal.lname, personal.fname
        from company right join personal
        on company.lname = personal.lname and company.fname = personal.fname
        having company.id is null;

This gives you all the personal IDs and names where there is no matching
record in the company table.

Finally, try this:

select company.id lname, company.lname lname, company.fname fname
        from company, personal
        where company.lname = personal.lname
        and company.fname = personal.fname;

This gives you all the records where the fields are in common, i.e., where
both records exist.

In each case, you can add as many additional fields from the left table (that
is, the one named first in the select statement) as you want, to obtain
additional data without needing another query.

I'm not sure what you mean by "both being in one table," but it is possible
to do a reflexive join (i.e., join a table back to itself by aliasing each
instance of the table), and that can be helpful if you need to treat a single
table as if it were two tables.

I know this isn't a complete solution, but I hope it's got you started thinking
in a useful direction.

Scott

-- 
-----------------------+------------------------------------------------------
Scott Courtney         | "I don't mind Microsoft making money. I mind them
address@hidden       | having a bad operating system."    -- Linus Torvalds
http://4th.com/        | ("The Rebel Code," NY Times, 21 February 1999)
                       | PGP Public Key at http://4th.com/keys/courtney.pubkey