Professional Web Applications Themes

equivelant as a JOIN? - MySQL

SELECT DISTINCT s.cat_id, s.subcat_id,c.category FROM categories AS c, subcategories AS s WHERE c.cat_id=s.subcat_id AND s.cat_id IN (SELECT cat_id FROM categoryroots); +--------+-----------+----------+ | cat_id | subcat_id | category | +--------+-----------+----------+ | 1 | 2 | b | | 1 | 3 | c | | 1 | 4 | d | +--------+-----------+----------+ CREATE TABLE `jmichae3`.`categories` ( `cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category here', PRIMARY KEY(`cat_id`), UNIQUE KEY `ix_ccat_id` (`cat_id`), INDEX `ix_ccat`(`category`) ) ENGINE=InnoDB; CREATE TABLE `jmichae3`.`subcategories` ( `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'used in case we need to delete a ...

  1. #1

    Default equivelant as a JOIN?

    SELECT DISTINCT s.cat_id, s.subcat_id,c.category FROM categories AS c,
    subcategories AS s WHERE c.cat_id=s.subcat_id AND s.cat_id IN (SELECT cat_id
    FROM categoryroots);
    +--------+-----------+----------+
    | cat_id | subcat_id | category |
    +--------+-----------+----------+
    | 1 | 2 | b |
    | 1 | 3 | c |
    | 1 | 4 | d |
    +--------+-----------+----------+

    CREATE TABLE `jmichae3`.`categories` (
    `cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category here',
    PRIMARY KEY(`cat_id`),
    UNIQUE KEY `ix_ccat_id` (`cat_id`),
    INDEX `ix_ccat`(`category`)
    ) ENGINE=InnoDB;

    CREATE TABLE `jmichae3`.`subcategories` (
    `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'used in case we
    need to delete a specific row',
    `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
    categories table',
    `subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
    subcategory',
    FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE
    ON UPDATE NO ACTION,
    -- FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE
    CASCADE ON UPDATE NO ACTION,
    PRIMARY KEY(`sub_id`),
    INDEX `ix_sccat_id`(`cat_id`),
    INDEX `ix_scsubcat_id`(`subcat_id`)
    ) ENGINE=InnoDB;

    CREATE TABLE `jmichae3`.`categoryroots` (
    `cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id
    from categories table',
    PRIMARY KEY (`cat_id`),
    FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE
    ON UPDATE NO ACTION,
    UNIQUE KEY `ix_crcat_id` (`cat_id`)
    ) ENGINE=InnoDB;

    /*
    c
    f-b
    g |
    h |
    i-c-a
    j |
    k |
    l-d
    m
    ----n
    */
    INSERT INTO categories(cat_id,category) VALUES
    (1,'a'),
    (2,'b'),
    (3,'c'),
    (4,'d'),
    (5,'e'),
    (6,'f'),
    (7,'g'),
    (8,'h'),
    (9,'i'),
    (10,'j'),
    (11,'k'),
    (12,'l'),
    (13,'m'),
    (14,'n');

    INSERT INTO categoryroots(cat_id) VALUES
    (1),
    (14);

    INSERT INTO subcategories(cat_id,subcat_id) VALUES
    (1,2),
    (1,3),
    (1,4),
    (2,5),
    (2,6),
    (2,7),
    (3,8),
    (3,9),
    (3,10),
    (4,11),
    (4,12),
    (4,13);


    Jim Michaels Guest

  2. #2

    Default Re: equivelant as a JOIN?

    "Jim Michaels" <NOSPAMFORjmichae3> wrote in message
    news:yKOdneNvROhcXmPeRVn-qAcomcast.com...
    > SELECT DISTINCT s.cat_id, s.subcat_id,c.category FROM categories AS c,
    > subcategories AS s WHERE c.cat_id=s.subcat_id AND s.cat_id IN (SELECT
    > cat_id FROM categoryroots);
    SELECT s.cat_id, s.subcat_id, c.cateory
    FROM categories AS c
    INNER JOIN subcategories AS s ON c.cat_id = s.subcat_id
    INNER JOIN categoryroots AS r ON s.cat_id = r.cat_id

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: equivelant as a JOIN?


    "Bill Karwin" <billkarwin.com> wrote in message
    news:dtnnl102fn5enews1.newsguy.com...
    > "Jim Michaels" <NOSPAMFORjmichae3> wrote in message
    > news:yKOdneNvROhcXmPeRVn-qAcomcast.com...
    >> SELECT DISTINCT s.cat_id, s.subcat_id,c.category FROM categories AS c,
    >> subcategories AS s WHERE c.cat_id=s.subcat_id AND s.cat_id IN (SELECT
    >> cat_id FROM categoryroots);
    >
    > SELECT s.cat_id, s.subcat_id, c.cateory
    > FROM categories AS c
    > INNER JOIN subcategories AS s ON c.cat_id = s.subcat_id
    > INNER JOIN categoryroots AS r ON s.cat_id = r.cat_id
    so the last INNER JOIN applies to the first INNER JOIN? is that how it
    reads? or are they just ANDed automatically?

    and why INNER JOIN?

    sorry - I am new to JOINs - I am familiar with set theory, but I am having a
    time trying to map it to database queries yet.
    >
    > Regards,
    > Bill K.
    >

    Jim Michaels Guest

  4. #4

    Default Re: equivelant as a JOIN?

    "Jim Michaels" <NOSPAMFORjmichae3> wrote in message
    news:komdne-vZI0xZ2LeRVn-iwcomcast.com...
    >> SELECT s.cat_id, s.subcat_id, c.cateory
    >> FROM categories AS c
    >> INNER JOIN subcategories AS s ON c.cat_id = s.subcat_id
    >> INNER JOIN categoryroots AS r ON s.cat_id = r.cat_id
    >
    > so the last INNER JOIN applies to the first INNER JOIN? is that how it
    > reads? or are they just ANDed automatically?
    Yes, basically. It's the same as:
    "FROM c, s, r WHERE c.cat_id = s.subcat_id AND s.cat_id = r.cat_id"
    > and why INNER JOIN?
    INNER JOIN is the same, logically, as the "comma-syntax" join you had in
    your query before. I prefer to use this syntax because then it's easy to
    make my queries consistent when using OUTER JOIN. The latter cannot be
    represented with the comma-syntax.

    The comma-syntax was published in the SQL-89 specification, and the JOIN
    syntax was published in the SQL-92 specification, after the committee
    realized you couldn't do some types of joins using the older syntax.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Is self-join appropriate?
    By Michal Stankoviansky in forum MySQL
    Replies: 6
    Last Post: January 3rd, 08:42 PM
  3. SQL join
    By sikhar in forum Coldfusion Database Access
    Replies: 15
    Last Post: April 20th, 04:21 PM
  4. CSS equivelant
    By David in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 12th, 08:09 AM
  5. How to Join...
    By CD in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 06:26 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139