Database

4-Feb-05 15:24

Hello,

Here's a good question for all you SQL gurus here in CP.

I have a Customers table with PK ID_customer and other customer information such as name, birth date, etc.

I have an Addresses table which contains all the information for an address, such as street, exterior number and so on. The primary key is ID_address, and contains ID_customer field for relating it to the customer. This table contains a foreign key ID_city. Each address has a date indicating since when it is valid (one starting 1/1/2000 and another from 1/1/2001 for example.)

My Cities table contains a PK ID_city for each of the cities that are allowed in an address, and another field CityName.

So, I developed a stored procedure to find all the records matching certain criteria (all fields in the Customer table) using IF NOT @param = NULL to check the different criteria (I can search by name or by customer ID, for example) and performing a SELECT query.

My problem is that this SELECT query must return only one record for each customer matching, and that record must contain the CityName for the latest address for that customer (or blank if no address registerd). I can do the JOIN, and with a GROUP BY but it returns one record for each different city each customer has.

So, any ideas?

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Yulianto.4-Feb-05 16:05

Yulianto.

4-Feb-05 16:05

I don`t get it, you mean each customer has many cities?

<italic>Work hard and a bit of luck is the key to success.

You don`t need to be genius, to be rich.

Luis Alonso Ramos4-Feb-05 19:18

4-Feb-05 19:18

Yulianto AKA goodmast3r wrote:
you mean each customer has many cities?

The software is for a door-to-door credit company, so it's necessary to keep a history of all the addresses where the customer has lived.

At one point one customer has one address (his house) but when (and if) he moves, you'll need to save the new address (with a more recent lives-here-since date.) It's not that he'll move to another city, but sometimes big cities are made of a few (or many) counties together, and although it's the same metropolitan area, legally they are different counties.

So, I need to get the county (or city) of the latest address for each customer that meets my search criteria.

An example:

Customers
ID_customer Name
    1       Luis Alonso Ramos
    2       John Smith

Addresses
ID_address ID_customer Street                City     LivesHereSince
    1           1      123 CodeProject St.     1         1/1/2001
    2           1      22  Maunder Ave.        2         1/1/2002
    3           2      2222 5th Ave.           1         1/1/1999

Cities
ID_city   Name
   1      Los Angeles
   2      Beverly Hills

So if I make a search for customers whose name starts with L, I should get:

ID_customer Name              City
    1     Luis Alonso Ramos   Beverly Hills

I hope this is clearer now. So, any ideas of how I can do it?? Confused | :confused:

Thanks!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Mike Dimmick5-Feb-05 1:52

Mike Dimmick

5-Feb-05 1:52

It'd be great if SQL Server had a MAX_OF function, where you could ask for the value of one column where another column had the maximum value in a group. Sadly, it doesn't.

My workaround looks like this:

SELECT C.ID_customer, C.Name, Cities.Name AS City
FROM 
    Customers C
INNER JOIN
    Addresses A ON C.ID_customer = A.ID_customer
INNER JOIN
(
    SELECT ID_customer, MAX(LivesHereSince) AS LastMove
    FROM Addresses
    GROUP BY ID_customer
) A2
    ON A.ID_customer = A2.ID_customer AND A.LivesHereSince = A2.LastMove
INNER JOIN
    Cities ON A.City = Cities.ID_city

I join Addresses onto a subquery of itself. The inner subquery tells me, for each customer, the largest (most recent) value of LivesHereSince. To extract the other fields of that row, I join back onto Addresses using that value as a key.

This technique works quite well as long as you have unique values in your search column. If that doesn't hold you'll get duplicate values. You might consider denormalizing your database slightly and keeping a CurrentAddress column in the Customers table. You can use a trigger to keep that column up-to-date.

Stability. What an interesting concept. -- Chris Maunder

Luis Alonso Ramos5-Feb-05 6:41

5-Feb-05 6:41

I tried it and it works, but it doesn't return records for those customers that don't have yet an address (in the process of being captured, not that it should happen often.)

Mike Dimmick wrote:
This technique works quite well as long as you have unique values in your search column.

That might be a problem. I need to perform searches based on first or last name, and so I should get several results.

Mike Dimmick wrote:
You might consider denormalizing your database slightly and keeping a CurrentAddress column in the Customers table.

This might not be a bad idea at all. I have a Customer C# class with an AddressCollection and when I call Save on the Customer class, it can easily walk the collection and get the latest address.

Mike Dimmick wrote:
You can use a trigger to keep that column up-to-date.

This may be another solution, but I've never used them before. Any pointers?

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Mike Dimmick5-Feb-05 7:32

Mike Dimmick

5-Feb-05 7:32

Luis Alonso Ramos wrote:
I tried it and it works, but it doesn't return records for those customers that don't have yet an address (in the process of being captured, not that it should happen often.)

Well, you could change the first INNER JOIN to a LEFT JOIN. You'll then get a NULL City back for anything you've not yet added the address to. You'll probably need to turn the whole Address<->Address<->City join into a subquery so that the INNER JOINs there don't eliminate rows from the Customers table. Example:

SELECT C.ID_customer, C.Name, AC.City
FROM
    Customers C
LEFT JOIN
(
    SELECT
        A.ID_customer, Cities.Name AS City
    FROM     
        Addresses A
    INNER JOIN
    (
        SELECT ID_customer, MAX(LivesHereSince) AS LastMove
        FROM Addresses
        GROUP BY ID_customer
    ) A2
        ON A.ID_customer = A2.ID_customer AND A.LivesHereSince = A2.LastMove
    INNER JOIN
        Cities ON A.City = Cities.ID_city
) AC ON C.ID_customer = AC.ID_customer

Luis Alonso Ramos wrote:
That might be a problem. I need to perform searches based on first or last name, and so I should get several results.

When I said 'search column', I meant duplicates in the column you're using to find a single row in the historical table (in this case, LivesHereSince in the Addresses table). If you have two rows where ID_customer = 1 and LivesHereSince = 1/1/2002, you'll get two rows from the Addresses<->Addresses join, because they both match LastMoved. I seem to recall finding some disambiguating mechanism last time I needed to do this - I think I added another condition that selected the maximum ID. Unfortunately this ends up adding another join to the query. My problem was trying to extract the most recent event from a transaction log where things actually could happen within the timer period and so were recorded with the same timestamp - however, you couldn't guarantee that the IDs were necessarily in the right order.

As for the trigger, you probably want to add something like:

CREATE  TRIGGER trigUpdateCustomersCurrentAddress
ON Addresses
AFTER INSERT AS
    UPDATE Customers
    SET CurrentAddress = inserted.ID_address
    FROM inserted
    WHERE Customers.ID_customer = inserted.ID_customer

inserted is a virtual table that exists in the trigger. It contains the new versions of rows inserted or updated (you can create a trigger FOR UPDATE too - you can even use the same trigger for both insert and update). The trigger executes in the context of the statement that performs the update. You might ask, why use this form of UPDATE? Remember that you can perform batch updates from client code and that you can use an INSERT/SELECT statement to insert multiple rows in one operation, so you might actually be operating on multiple rows inside the trigger. If you're writing a trigger FOR UPDATE or FOR DELETE, the deleted table contains the old versions of rows that were updated and the rows that were deleted.

That's the simplistic version. If you might need to insert a customer's old addresses at a later time, you'll need to check what's already in the addresses table:

CREATE TRIGGER trigUpdateCustomersCurrentAddress
ON Addresses
AFTER INSERT AS
    UPDATE Customers
    SET CurrentAddress = inserted.ID_address
    FROM inserted
    INNER JOIN Addresses
        ON inserted.ID_customer = Addresses.ID_Customer AND
           inserted.LivesHereSince > Addresses.LivesHereSince
    WHERE Customers.ID_customer = inserted.ID_customer

It's surprising how few people actually 'get' the set-based concepts of SQL and its ability to work on multiple rows simultaneously, and fall back on cursors way too early. INSERT/SELECT and UPDATE FROM are extremely powerful tools.

I can't recall how exactly I first found out about the power of triggers. It was probably somewhere in Inside SQL Server 2000 by Kalen Delaney.

Once you've created the trigger, you'll need to update the existing rows:

UPDATE Customers
SET CurrentAddress = A.ID_address
FROM
    Addresses A
INNER JOIN
(
    SELECT ID_customer, MAX(LivesHereSince) AS LastMove
    FROM Addresses
    GROUP BY ID_customer
) A2
    ON A.ID_customer = A2.ID_customer AND A.LivesHereSince = A2.LastMove    
WHERE
    Customers.ID_customer = A.ID_customer

Stability. What an interesting concept. -- Chris Maunder

Luis Alonso Ramos5-Feb-05 8:13

i can't to insert a new row

5-Feb-05 8:13

Wow! that was a long reply, thank you very much!! Big Grin | :-D

I had already solved my problem with what you suggested in your last post about denormalizing my table. I developed a simple SP that would update the customer record with the ID_address of the latest address after all addresses had been inserted/updated. The only problem was that I had to call that SP at the end of every customer save operation.

I've copied and tested your query now, and after adding some WHERE clauses, I think it works beautifully. So I've just updated my search stored procedure, and removed the ID_lastaddress field from my Customers table. All I can say is THANKS!

That thing about unique search column, I don't think it'll be a problem. Even if somehow the customer could move twice in the same day, the address is stored in a datetime, and using DateTime objects in C# makes the column contain a date and a time, so they'll always be different.

So, I won't be needing triggers for this particular use anymore, but it sure will be useful to know about them for a later need. I've never had any formal SQL training (not even read a book). I only recently learned about stored procedures, and mainly design all my queries (JOINs and all that) in Access. But know I look back to some other projects I developed earlier and I see how much code I could have replaced with a little stored procedure. I guess it happens to everyone, once you learn a new thing you see a thousand places where that could have been useful in the past.

Well, I better keep on working that I still have plenty of things to do today (and it's Saturday noon for me.) Again, thank you very much for your help!

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Sasuko4-Feb-05 6:38

4-Feb-05 6:38

I'm tring to add a row at my database .mdb but i get the follow error: "Sintax error in the INSERT INTO instruction" why? what do i wrong?
this is the schema of my database:
Nome: text 15
Domanda: text 20
Risposta: text 15
Email: text 20

this is the code:

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();

conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= myDatabase.mdb;";
try
{
conn.Open();

OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM myTable", conn);
OleDbCommandBuilder myCmd = new OleDbCommandBuilder(myAdapter);
DataSet myDataSet = new DataSet("myTable");
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
myAdapter.Fill(myDataSet, "myTable");
DataRow myRow = myDataSet.Tables["myTable"].NewRow();
myRow["Nome"] = mNuovoUtente.mName;
myRow["Domanda"] = mNuovoUtente.mQuestion;
myRow["Risposta"] = mNuovoUtente.mAnswer;
myRow["Email"] = mNuovoUtente.mEmail;
myDataSet.Tables["TabellaUtente"].Rows.Add(myRow);
myAdapter.Update(myDataSet, "myTable");
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}

tojamismis4-Feb-05 6:52

4-Feb-05 6:52

What is the syntax for your myAdapter.UpdateCommand and myAdapter.InsertCommand?

Torin Blair

'In the immortal words of Socrates - "I drank what?".'

Sasuko4-Feb-05 11:00

4-Feb-05 11:00

Sorry i don't understand what you mean, please can you explain me?
Thanks.

tojamismis4-Feb-05 11:23

4-Feb-05 11:23

In order for you to be able to update or insert from your dataadapter, you need to specify an insert SQL statement in the OleDbDataAdapter.InsertCommand, and an update SQL statement in the OleDbDataAdapter.UpdateCommand. If you used the wizard to create your adapter, you probably already have these statements.

In debug, or by writing out to a label on the screen, you should output both of these properties and see if their SQL Syntax is correct. This will allow you see the sql commands you are using with the database.

These strings are probably also listed in the WinForms or WebForms generated code if you are using Visual Studio .NET

Torin Blair

'In the immortal words of Socrates - "I drank what?".'

Sasuko4-Feb-05 11:30

4-Feb-05 11:30

I'm a newbie can you tell me how run the wizard to create the adapter?

tojamismis4-Feb-05 14:11

4-Feb-05 14:11

If you drag a data adapter to a form, then it will open a wizard for you.

No worries you can do this the manual way.

<br />
OleDbCommand cmdUpdate = new OleDbCommand(SQLCOMMAND);<br />
OleDbCommand cmdInsert = new OleDbCommand(SQLCOMMAND);<br />
<br />
myAdapter.UpdateCommand = cmdUpdate;<br />
myAdapter.InsertCommand = cmdInsert;<br />
<br />

And that's how it's done.

For a more detailed explanation you can look at the .NET sdk documentation or this example from csharpcorner.

http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=103[^]

Torin Blair

'In the immortal words of Socrates - "I drank what?".'

Sasuko4-Feb-05 22:29

4-Feb-05 22:29

Hi, tojamismis.
Thanks for the answer...
Another user has wrote me this:

the first problem is that your table doesn't show a primary key. Without it, .Update() won't work. Second, you, apparently, don't have an Update command associated with the DataAdapter. Third, if the Primary Key information is missing, you set the .MissingSchemaAction, but never called .FillSchema before you called .Fill

In your opinion what other things have i miss?

Sasuko4-Feb-05 23:58

4-Feb-05 23:58

Can add you directly the missing rows of code, i'm not success. Frown | :(

tojamismis5-Feb-05 4:49

5-Feb-05 4:49

I don't know the structure of your tables so I can't write any of the sql statements.

Torin Blair

'In the immortal words of Socrates - "I drank what?".'

Sasuko5-Feb-05 12:22

5-Feb-05 12:22

It is compose just by a table "myTable" with:
Name: text 20 primarykey
Surname: text 20
Image: OLEOBJ Byte[]

tojamismis5-Feb-05 4:47