Compare and Append 1

[orreymalcolm]

Hey guys! I found your site, and thank goodness I did, I'm actually stumped on this. Hopefully one of you can help.

I have 2 tables

Contract table (we'll call Con)
Complete Member list with contracts table (we'll call Mem)

Con has 5 fields, Agreement number, Agreement name, Date accepted, Date expiry and a field that is just boolean about something else.

Mem has 7 fields, Member number, agreement number, whether they are commited or not (A or B), and 4 others which are unimportant

They have Agreement number in common and is the relationship.

I wish to compare the Con table with the Mem table, BUT i wish it to compare by member number (as in perform the entire search for every different member number), so that it doesnt check the whole database, just reset the search for each member that way every member has the contract listed in that database, even if they are not committed to it. I hope that makes sense.

After that, I wish to append any contract that is not in their listing to their listing so they have every contract for every member.

Thanks alot

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rac2]

To clarify-

A contract is represented by one row in the Contracts table and it is identified by the Agreement number.

A member is associated with many contracts. Each contract a member is associated with is represented by a row in the Mem table. Different members will be associated with different contracts. If a member is not associated with a contract there will not be a row for that combination of Member number and Agreement number in the Mem table.

If a member is associated with a contract then they may or may not be committed to the contract. This is represented by A or B in the Committed column in the Mem table.


Now the hard part

... compare the Con table with the Mem table, BUT i wish it to compare by member number (as in perform the entire search for every different member number), so that it doesnt check the whole database, just reset the search for each member that way every member has the contract listed in that database ...

I confess that does not make sense to me.


Here are some things we could do with these two tables.

List the member numbers and agreement numbers sorted by member number so that all of the contracts a member is associated with appear together, one after the other.

SELECT [Member Number], [Agreement Number]
FROM Mem
ORDER BY [Member Number]

List all of the members whether or not they have any associated contracts. One row for each member.

SELECT [Member Number]
FROM Mem
GROUP BY [Member Number]
ORDER BY [Member Number]

This will not show any member who is not associated with at least one contract.

One must think that you have another table for the members themselves, each member with one row, and nothing to do with the contracts. One might name that table AllMembers.

A Query to list all members, even those with no contract assoications, and show any contracts they are associated with

SELECT a.[Member Number], b.[Agreement Number]
FROM AllMembers a
LEFT OUTER JOIN Mem b
    ON b.[Member Number] = a.[Member Number]
ORDER BY a.[Member Number]

A list of all possible member contract assoications whether or not these are in the Mem table

SELECT a.[Member Number], c.[Agreement Number]
FROM AllMembers, Contracts c
ORDER BY a.[Member Number], c.[Agreement Number]

Note this does not use the association table we are calling Mem. Also this could be a long list because every contract will be listed for every member, so for M members and N contracts, there will be M x N rows.

Final note. The whole thing, the tables, the queries are one database. A database contains tables.

 

[orreymalcolm]

Thanks a lot for your reply rac2,

Let me clarify my statement for you.

I wish to have all 2011 contracts listed for each member, whether or not they are associated with all of them or not.

Since there are a total of 2011 contracts available to everyone, and a total of 657 members, the Mem table should have 1,321,227 rows total once I am finished.

Even if the member is committed to the contract or not, it should still be added there under their member number. This way I have a listing of every member and every contract for those members in the one table.

Since I want to make it available for them to see what they are not committed to so that they may choose to commit to it at a later time.

Hopefully this clears up what I meant.

Thanks again for your help!

 

[PHV]

Have a look at LEFT JOIN and UNION.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886