![]() ![]() 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. something like UPDATE Table SET SpecialEventCount=COUNT(m.EventType).īut I am confused what to put where and have failed with numerous different guesses.I have a table customer that stores a customer_id, email and reference. ![]() How do I have to modify the select query to become an update query? I.e. Index1 and Index2 together are unique in Table t and they are used to identify events in MEvent. This number could be 0 or any positive number (hence the left join). The update query I am trying to write should set this SpecialEventCount to that count, i.e. It only does this for case where t.SpecialEventCount is NULL. It creates a list of triplets Index1,Index2,EventCounts. The following query is working: SELECT t.Index1, t.Index2, COUNT( m.EventType ) I am trying to create an update query and making little progress in getting the right syntax. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |