Monthly Archives: October 2011

Used car financing

Posted by on October 25, 2011 No comments

For a few days, my colleague and I talks and speak each others opinion as one of my colleague decides to buy a used car and asking us for car financing.

Some people believe that you need to dump a lot of cash for down payment. Well, my colleague did think that way. So he planned  to pay up at least 10k for the car’s downpayment. From his plan, he will apply for personal loans to finance car down payment then use hire purchase loans for the rest of car financial.

Personally, I didn’t agree with the idea since he will end up with loans with more interest. Personal loans charge minimum 10% interest rate. Some banks charged more than that. Then hire purchase loans itself charge higher interest for used car maximum 4% interest rate.

So it’s not worth to have loans with 14% interest rate. I advised him pay only minimum on downpayment and only take hire purchase since it only serve 4% interest rate. That will save a lot of his money from paying more for banks.

Constant value in insert select

Posted by on October 25, 2011 No comments 
INSERT INTO linking_book_category (book_id, category_id)
SELECT book.id, 1 FROM books AS book;

More advance usage involving substring, trimming leading zeros as I try to migrate college’s intake from migration table.

INSERT INTO intakes (no,month,year,active,`status`,createdby,datecreated)
SELECT DISTINCT academicperiod, TRIM(LEADING '0' FROM SUBSTR(academicperiod,6,2)), SUBSTR(academicperiod,1,4), 1, 1, 1, NOW()
FROM _prestige_intake
WHERE academicperiod IN ('2006/01','2006/07','2007/01','2007/07','2008/01','2008/07','2009/01','2009/07','2010/01','2010/07')
ORDER BY academicperiod ASC

Update field with value from another table

Posted by on October 14, 2011 No comments 
UPDATE table_1 AS t1, table_2 AS t2
  SET
    t1.name = t2.title,
    t1.descr = t2.description,
    t1.price = t2.price,
    t1.available = t2.stock
  WHERE t1.productId = t2.sku

Taken from forum while browsing to find for solution. This query will took some time when dealing with thousands record in both table.

Merge rows as one field

Posted by on October 12, 2011 No comments 
GROUP_CONCAT(fieldname ORDER BY fieldname ASC SEPARATOR ',')

Full code usage example:

SELECT email, studentname,
GROUP_CONCAT(matrixno ORDER BY matrixno ASC SEPARATOR ',') AS matno,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Code above used to find duplicates for email.