![]() The extra complexity for creating/updating a customer might be fearsome, but it can easily be automated with triggers.įinally, if you're using an ORM, this can be really easy to manage. UPDATE customer SET current_data_id = LAST_INSERT_ID() WHERE id = up INSERT INTO customer_data (customer_id, title, forename, surname) 'Mr', 'John', 'Smith') UPDATE customer SET current_data_id = LAST_INSERT_ID() WHERE id = 2 Ĭreating a customer is just a matter of inserting the customer entry, then running the same statements: INSERT INTO customer () VALUES () INSERT INTO customer_data (customer_id, title, forename, surname) VALUES(2, 'Mr', 'John', 'Smith') Whenever you want to update a customer, you insert a new record in the customer_data table, and update the customer record. The drawback is the extra complexity when creating or updating a customer. INNER JOIN customer_data d on d.id = c.current_data_id Querying is as easy and fast as it can be: SELECT c.*, d.title, d.forename, d.surname Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Ĭurrent_data_id INT UNSIGNED NULL DEFAULT NULL ![]() If you can, however, modify your schema, I would advise to add a field in your customer table that holds the id of the latest customer_data record for this customer: CREATE TABLE customer ( If you cannot modify your original database schema, then a lot of good answers have been provided and solve the problem just fine. I'm adding it here for completeness sake. I know this question is old, but it's got a lot of attention over the years and I think it's missing a concept which may help someone in a similar case. JOIN customer_data cd ON (cd.id = c_max.max_id) Result (query without the LIMIT and WHERE): SELECT CONCAT(title, ' ', forename, ' ', surname) AS name ![]() ![]() INSERT INTO customer_data VALUES (5, 3, 'Dr', 'Jack', 'Black') INSERT INTO customer_data VALUES (4, 2, 'Miss', 'Jane', 'Green') INSERT INTO customer_data VALUES (3, 2, 'Mr', 'Jane', 'Green') INSERT INTO customer_data VALUES (2, 1, 'Mr', 'Bob', 'Smith') INSERT INTO customer_data VALUES (1, 1, 'Mr', 'Bobby', 'Smith') Test case: CREATE TABLE customer (customer_id int) Note that a JOIN is just a synonym for INNER JOIN. WHERE CONCAT(title, ' ', forename, ' ', surname) LIKE '%Smith%' ) c_max ON (c_max.customer_id = c.customer_id) You may want to try the following: SELECT CONCAT(title, ' ', forename, ' ', surname) AS name I actually have no clue what the difference is between the different JOINs. (I appreciate that INNER JOIN might be the wrong type of JOIN to use. INNER JOIN customer_data d on c.customer_id=d.customer_idĪdditionaly, am I right in thinking I can use CONCAT with LIKE in this way? The query at the minute is like this- SELECT *, CONCAT(title,' ',forename,' ',surname) AS name How can I do this with MySQL? I think I'm wanting to put a DISTINCT in there somewhere. It gets a little more complicated in that the query is paginated, so has a limit and an offset. In order to display the customer information in a table, the two tables need to be joined, however only the most recent row from customer_data should be joined to the customer table. when there's a change made a new row is inserted. There is an additional table customer_data that stores a historical record of the changes made to the customer, i.e. I have a table customer that stores a customer_id, email and reference.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |