SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Simple.. SELECT (RETURN) N_Numbers_Of_Categories_Id From Categories_Table

    SELECT (RETURN) N_Numbers_Of_Categories_Id From Categories_Table

    How to ?

    My Table : categories
    Columns are
    Id , int
    name, varchar(255)
    parent_id , int


    Data is stored as
    ID Name PARENT_ID
    1 A 0
    2 B 1
    3 C 2
    4 D 3
    5 E 4


    I want out put like

    Column name ID
    Values will be
    1
    2
    3
    4
    5


    HOW TO... Need Simple SQL Statement HERE!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT id
      FROM categories
    ORDER
        BY id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Quote Originally Posted by r937 View Post
    Code:
    SELECT id
      FROM categories
    ORDER
        BY id
    I am searching query like


    ~~~ Select id from Category WHERE PARENT_ID = ID AND id = 2 OR ( SELECT id from Category WHERE ******** )


    Well r937 WHERE IS WHERE CLAUSE IN YOUR SQL QUERY FOR JOIN

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    don't get angry, but the solution i gave you works perfectly for the requirements that you gave in post #1

    now it appears that you want something different, but you have not explained it very well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    JOINs are not used within WHERE. You also shouldn't get angry with people who are helping you for free and you really should learn the basics of SQL before trying to argue who's right and who's wrong

  6. #6
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Quote Originally Posted by r937 View Post
    don't get angry, but the solution i gave you works perfectly for the requirements that you gave in post #1

    now it appears that you want something different, but you have not explained it very well
    So show can you collect with n number of Categories with this SQL Statement

    SELECT c.name , c.id, (SELECT COUNT(p.id) as total FROM posting as p WHERE p.cat_id = c.id AND p.mode = 1) as total1 FROM `categories` as c WHERE c.cat_type = '1' AND c.mode = 1 AND c.cat_parent = 0 ORDER BY name



    ???????????????

  7. #7
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sql query n lavel of child categories

    I have tow tables

    Each POSTING have Any SUB OF SUB OF SUB OF SUB OF CHILD CATEGORY MEANS 5 LAVEL OF CHILD CATEGORY WILL ASSIGN TO ANY POST BY FOLLOWING JOIN


    posting as p , categories as c WHERE p.cat_id = c.id



    MY TABLES



    CREATE TABLE IF NOT EXISTS `categories` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `descc` text,
    `cat_type` int(11) DEFAULT '0',
    `cat_parent` int(11) DEFAULT '0',
    `seo_keywords` varchar(255) DEFAULT NULL,
    `seo_desc` varchar(255) DEFAULT NULL,
    `title` varchar(255) DEFAULT NULL,
    `marque` text,
    `image` varchar(255) DEFAULT NULL,
    `seqno` int(11) NOT NULL,
    `mode` int(11) DEFAULT '0',
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=106 ;

    -- --------------------------------------------------------

    --
    -- Table structure for table `posting`
    --

    CREATE TABLE IF NOT EXISTS `posting` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(2) unsigned NOT NULL,
    `cat_type` int(11) NOT NULL,
    `city_id` int(2) unsigned NOT NULL,
    `country_id` int(11) unsigned NOT NULL,
    `location` varchar(255) NOT NULL,
    `type` int(2) NOT NULL,
    `cat_id` int(11) NOT NULL,
    `title` varchar(255) NOT NULL,
    `amount` int(11) NOT NULL,
    `phone_number` int(11) NOT NULL,
    `desc1` text NOT NULL,
    `listner_type` int(2) NOT NULL,
    `featured` int(2) unsigned NOT NULL,
    `publish` int(2) NOT NULL,
    `map` varchar(255) NOT NULL,
    `publish_date` date NOT NULL,
    `mode` int(2) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;



    I WANT OUT PUT LIKE

    SHOP (10) "Title (Total of 5 lavel of child )

    i did following MySQL query

    SELECT c.name , c.id, (SELECT COUNT(p.id) as total FROM posting as p WHERE p.cat_id = c.id AND p.mode = 1) as total1 FROM `categories` as c WHERE c.cat_type = '1' AND c.mode = 1 AND c.cat_parent = 0 ORDER BY name


    But it collect 2 laved of child category while simple We want N lavel of Child Category TOtal...
    HELP ... PLEASE!


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
  •