Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: Query help/Sqlexpress 2005 database

    Hi i have the above tables
    Table 01Customers)
    CustomerId Company NameSeller
    001 George Test Soros
    002 Albert Red Soros
    003 Nick Blue White

    Table 51Sellers)
    CodeSeller NameSeller Tel
    00001 Soros Nick 1234567
    00002 White Maria 2345678

    Table 16Receipts)
    CodeCustomer CustomerDescription CodeRe DeReceipt SellerId
    001 George Test 123 TDA 00001
    002 Albert Red 124 TDA 00001
    003 Nick Blue 125 TDA 00002
    001 George Test 030 APD 00001

    Now the same columns are: CodeCustomer=01.Customer
    CustomerDescription=01.Company
    SellerId=51.CodeSeller

    Question:
    The user make a new record at table 51 -> 00003 Melina Zack 4875847
    Then the user change the at table 01 the seller at customer 003 Nick Blue with new one seller (00003) Melina Zack
    At table 01 everything is ok. But at table 16 at third row the old code of seller 00002 remains and i want to change with new one 00003
    So as i thinking it i think the last row of 51 that user insert have to keep it at memory compare at 01 in what customers exist and then make the changes with the new code seller at 016
    I dont know if this can work with update...Do i need any function to search and compare at table 01????
    Help please...Any other ideas?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Think this is your start situation:
    Code:
    Table_01 (Customers)
    CustomerId	Company		NameSeller
    001		George Test	Soros
    002		Albert Red	Soros
    003		Nick Blue	White
    
    Table_51 (Sellers)
    CodeSeller	NameSeller	Tel
    00001		Soros Nick	1234567
    00002		White Maria	2345678
    
    Table_16 (Receipts)
    CodeCustomer	CustomerDescription	CodeRe	DeReceipt	SellerId
    001		George Test		123	TDA		00001
    002		Albert Red		124	TDA		00001
    003		Nick Blue		125	TDA		00002
    001		George Test		030	APD		00001
    First a few remarks about the data model.
    Naming:
    - Are you sure you want to work with table_01, Table_16, ... ? Is this intentionally to make it virtually impossible to customers to understand your data model? If not, give them meaningful names, like the one you have put between parentheses.
    - A column CustomerId is known under another name in another table, CodeCustomer. Same with SellerId and CodeSeller. Use the same name for columns that hold the same data (domain). A Code is different from an Id, I would use CustomerId and SellerId.
    Lack of history:
    - By changing the Seller in Customers, to Melina Zack, you loose all history of what seller it was before. But that seems to be the purpose.
    Redundant information:
    - There is no need to store the redundant information CustomerDescription in table Receipts, as that information is available in the table Customers.
    - There is no need to store the redundant information SellerId in table Receipts, as that information is (somehow) available in the table Customers. (See my solution.)

    This is the way I would model the data model (if history of sellers is unimportant):
    Code:
    Table Customer
    CustomerId	Name		SellerId
    001		George Test	00001
    002		Albert Red	00001
    003		Nick Blue	00003*
    
    Table Seller
    SellerId	Name		Tel
    00001		Soros Nick	1234567
    00002		White Maria	2345678
    *00003		Melina Zack	4875847
    
    Table Receipt
    CustomerId	CodeRe	DeReceipt
    001		123	TDA
    002		124	TDA
    003		125	TDA
    001		030	APD
    
    
    *: changed data by user
    With this SELECT you can extract all information from the database:
    Code:
    SELECT R.CustomerId, C.Name as CustomerName, R.CodeRe, 
    	R.DeReceipt, S.SellerId, S.Name
    FROM Receipt as R
    	INNER JOIN Customer as C ON
    		R.CustomerId = C.CustomerId
    	INNER JOIN Seller as S On
    		C.SellerId = S.SellerId
    It solves your problem:
    But at table 16 at third row the old code of seller 00002 remains and i want to change with new one 00003
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    Hi
    I didnt make the database...I just use the software program...I know are many wrong things on database...
    About your solution... First all i cant make it work.. it brings me syntax error..Also i dont think will solve my problem.. With this i will take a result with new table. I want to change tables... i think i need a query with update or maybe alter.
    Also i wanna add here that i made wrong
    At table Customer i dont have sellerid but sellername and at table receipt i have sellerid and not sellername
    Last edited by Leite333; 08-07-11 at 03:03.

  4. #4
    Join Date
    Aug 2011
    Posts
    4
    Tables:
    E16_EMP001_38 (Customers)

    Code(Customers) Customers SellerName
    000001 George Test Nick Papadopoulos
    000002 Albert Red Melina Zack

    E16_EMP016_38 (receipts)
    CodeCustomer CodeRe DeReceipt SellerID
    000001 123 TDA 001
    000002 124 TDA 003
    000001 125 TDA 001
    000002 030 APD 003

    E16_EMP051_38 (Sellers)
    SellerId Name Tel
    001 Nick Papadopoulos 1234567
    002 Melina Zack 2345678
    003 White Maria 4875847



    Hi again
    I try to do something like that:

    UPDATE E16_EMP016_38
    SET E16_EMP016_38.CodeSeller = (SELECT E16_EMP016_38.CodeSeller

    FROM E16_EMP016_38
    WHERE E16_Emp001_38.Code = E16_EMP016_38.CodeCustomer)
    WHERE E16_Emp001_38.Code= E16_EMP016_38.CodeCustomer

    But i think i have wrong syntax it says the message i get
    "The multipart identifier E16_EMP016_38.Kwd can not be bound"

    What that means????
    If you look carefull the tables you will see that. At table customers in record 000002 customer Red is with seller Zack but in table
    Receipts at Red is the code of seller White. That is because the administrator(user) because fired seller White put a new seller at customer Red (Zack). Now at table receipts we have to change that and put where code is 003->002. This could be do it manually but this changes are be all the time. So we need something that will make it automatically like a sql query.
    The reason that we want that is because we need to carry all receipts to the new customer that customer have (all the history)
    I hope i am more spesifiec this time. I think we need update and set. I dont want retreive data i want to change data.
    If you look carefull the tables
    Last edited by Leite333; 08-07-11 at 08:30.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I will answer your PM to me on the forum, as your PM question is the same as the last one on the forum..

    I rewrote your UPDATE script to make it more readable. It is meaningless, see the red part.
    Code:
    UPDATE U
    SET U.CodeSeller = U.CodeSeller
    FROM E16_EMP016_38 AS U
    	INNER JOIN E16_Emp001_38 ON
    		U.CodeCustomer = E16_Emp001_38.Code
    It is extremely difficult for me to understand your problem:
    - I shiver from the meaningless table names
    - The data model is plain wrong and not-normalised.
    - The UPDATE script contains fields that are not present in the table examples you gave.
    - My mother's tongue is not English. I have problems understanding the sentences you wrote.

    Don't forget you are the only person who knows your situation. If you can not describe that situation to me in a way that is clear to me, I can not help you.

    About your solution... First all i cant make it work.. it brings me syntax error.
    What error did you get?
    Also i dont think will solve my problem.
    Have you tried it? What makes you think so?
    With this i will take a result with new table. I want to change tables... i think i need a query with update or maybe alter.
    What do you mean by all of this?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Aug 2011
    Posts
    4
    Hi
    I am really sorry. My language is not english too thats why. I try to be as more clear as i can. I didnt built the database. I know there are many wrong things but i cant change it.
    About your new query i try it and it runs with no syntax errors. But dont make changes at table of Receipts.... I mean for example. I have a customer with a seller. I change the seller to the customer with someone that already exist at table Sellers. So now the customer have new seller. At table Customers i can see that in field SellerName is the new seller. But in table with receipts is the code of the old seller. I want the table of receipts get the code of new seller.

    I hope i dont confuse you. I cant be more specifiec. Also in your query i dont understand where you check what changes made at table of Customers so you can get the changes and make correct at table of Receipts.

    The query must check as i think at table of Customers what seller exist at each name and then go to table of Receipts and change the codes with correct one.

    Maybe my problem cant be solve with a simple query. How i can write code???? Do i need a stored procedure????
    Last edited by Leite333; 08-08-11 at 02:06.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I didnt built the database. I know there are many wrong things but i cant change it.
    OK.
    I have a customer with a seller. I change the seller to the customer with someone that already exist at table Sellers. So now the customer have new seller. At table Customers i can see that in field SellerName is the new seller. But in table with receipts is the code of the old seller. I want the table of receipts get the code of new seller.
    The reason why you have these problems is because redundant data is stored. The only place the seller should be stored is in the Customer table. The seller's name is also stored in the Receipts table and should be updated there too. The fact that you have to do that second update (in Receipts) is proof that the data model is bad.

    So: Normally you would get the seller's name from the Customer table. To find the records in Receipts that have to be updated: get the seller's name from the Customer table and compare that to the seller's name in Receipt. If they don't match, you need to update the seller in Receipts with the name of the seller in Customer.

    You have been withholding information: the columns in your examples are not those from your UPDATE script. My UPDATE script is based is yours. So you will have to figure out yourself what else besides the CodeSeller needs to be updated. This script will only update the CodeSeller, not the seller's name and whatever else of redundant data is present in the Receipts table, you will have to add those columns yourself if required.
    Code:
    UPDATE U
    SET U.CodeSeller = Customer.CodeSeller
    FROM E16_EMP016_38 AS U
    	INNER JOIN E16_Emp001_38 as Customer ON
    		U.CodeCustomer = Customer.Code 
    WHERE U.CodeSeller <> Customer.CodeSeller
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •