Shantibhushan Naik

Tag: sql

  • Maintaining relationship – common friends

    I was posed a very trivial question from one of my friends recently. It picked my interest, since I could not readily solve it, and I had the inkling that there is a better, simple solution for this problem.

    The problem was, let’s say I have a table of users as below
    User's table data

    And these users have friends as below

    User's friends data or more easier to read as User's friends data

    Now, the trivial task was find common friends for users 1, and 5, hence naturally the answer should be

    User 1 : Woody’s friends, and User 5 : Andy’s friends  are

    Woody's friends and Andy's friends

    So, the question I had was what could be the best SQL query that would be return me this result?

    Woody and Andy's common friends

    I thought of 2 different SQL queries

    SELECT f.fid, u.name 'Woody and Andy\'s common friends' FROM
    (SELECT f.`fid` FROM friends f
    WHERE f.`uid` IN (1,5)
    GROUP BY f.`fid` HAVING COUNT(f.`fid`) > 1) f INNER JOIN user u ON (u.uid=f.fid)

    -or- another one was

    SELECT f.fid, u.name 'Woody and Andy\'s common friends' FROM
    (SELECT a.fid FROM
    (SELECT * FROM friends f
    WHERE f.`uid`=1) a INNER JOIN
    (SELECT * FROM friends f
    WHERE f.`uid`=5) b ON (a.fid=b.fid)) f INNER JOIN user u ON (u.uid=f.fid)

    Both seem to work, but is there any elegant way of getting common friends in a much simpler SQL query? At this moment, I logically find first SQL query more intuitive since all it does is pickup friends who appear more than once when users are 1 and 5. The second SQL query seems plain brute force approach.

  • SQL JOINS quick tutorial

    Table T1, Table T2

    INNER JOIN returns only matching rows from T1, T2

    LEFT OUTER JOIN returns (all T1 rows) X (matching T1 rows in T2)

    RIGHT OUTER JOIN returns (all T2 rows) X (matching T2 rows in T1)

    UNION returns only unique rows from T1, and T2.

    UNION ALL returns T1+T2 rows

  • Wikimedia Downloads

    Wikimedia Downloads

    The following kinds of downloads are available:

    Database backup dumps
    A complete copy of all Wikimedia wikis, in the form of wikitext source and metadata embedded in XML. A number of raw database tables in SQL form are also available.
    Static HTML dumps
    A copy of all pages from all Wikipedia wikis, in HTML form.
    DVD distributions
    Available for some Wikipedia editions.

    Wikimedia Downloads.

  • Set MySQL to UTF-8

    Change the following in my.cnf

[mysqld]
default-character-set= utf8
skip-character-set-client-handshake
character-set-server= utf8
collation-server= utf8_general_ci
init-connect= 'SET NAMES utf8'

    via M’sNOTE　 MySQL(デフォルト文字コード)の設定 – Ubuntu9.04.

  • MySQL UTF-8

    A very informative page (in Japanese) about MySQL and issues to enable correct UTF-8 data handling with client applications.

    View original post

  • MySQL’s killer feature

    MySQL has a killer feature – you can have a timestamp field with DEFAULT as LOCALTIMESTAMP, so that when you insert a new record, the timestamp field will automatically have current timestamp inserted. But what about when the record is updated? Do you manually have to update the timestamp again? NO – MySQL allows you also specify another default on UPDATE so that whenever you update the record, it will automatically update the timestamp again.

    …
      `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    …

    Now, isn’t that a neat feature!