how to use sql query in sql query

Question

I have two tables:

table category(cat_id,category)
table category_details(cat_id,id,...)

I have sql code like bellow:

$sql=mysql_query("select * from category");
while($rows=mysql_fetch_array($sql)){
$id_count=$rows['cat_id'];      

$sql1=mysql_query("select * from category_details where cat_id='$id_count'");
$count=mysql_num_rows($sql1);

}

Can I use like this?

I want to create a category of products and each category have many products in it. So I just want to select all category to show on website and count the products of each category, for example(Hotels(20),Guesthouse(15)) just like that. — Bee
– Bee, Commented Mar 29, 2013 at 3:55

Dipesh Parmar · Accepted Answer · 2013-03-29 04:49:56Z

3

Use JOIN.

Example

SELECT * FROM category LEFT JOIN 
   category_details ON category_details.cat_id = category.cat_id;

above query will return all the category and associated category detail.

OP Comment Response

SELECT    c.name,
          IFNULL(sub_c.total, 0) num
FROM      category c
LEFT JOIN ( SELECT   COUNT(*) total, cat_id
            FROM     category_details
            GROUP BY cat_id
          ) sub_c ON (sub_c.cat_id = c.cat_id);

Complete Code

<?php
    $query = "SELECT    c.name,
                IFNULL(sub_c.total, 0) num
            FROM      products_category c
                LEFT JOIN ( SELECT   COUNT(*) total, cat_id
                    FROM     product
                    GROUP BY cat_id
                ) sub_c ON (sub_c.cat_id = c.id)";

    $result = mysql_query($query)or die(mysql_error());

    echo "<table><tr><td>NameCount</td></tr>";
    while($row = mysql_fetch_assoc($result))
    {
        echo "<tr><td>".$row['name']."(".$row['num'].")"."</td></tr>";
    }
    echo "</table>";
?>

MySQL Table

ProductCategory Table

CREATE TABLE IF NOT EXISTS `products_category` (
  `id` int(11) NOT NULL,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `products_category`
--

INSERT INTO `products_category` (`id`, `name`) VALUES
(1, 'Erasmus'),
(2, 'Preston'),
(3, 'Ulric'),
(4, 'Gray'),
(5, 'Joseph'),
(6, 'Merrill'),
(7, 'Alan'),
(8, 'Jeremy'),
(9, 'Solomon'),
(10, 'Andrew'),
(11, 'Galvin'),
(12, 'Craig'),
(13, 'Cameron'),
(14, 'Omar'),
(15, 'Addison');

Product Table

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`id`, `name`, `cat_id`) VALUES
(1, 'Wesley', 1),
(2, 'Graiden', 2),
(3, 'Cruz', 5),
(4, 'Hayden', 5),
(5, 'Kennedy', 6),
(6, 'Uriah', 8),
(7, 'Alan', 8),
(8, 'Cade', 1),
(9, 'Ryan', 5),
(10, 'Brody', 7);

Above will output

Erasmus(2)
Preston(1)
Ulric(0)
Gray(0)
Joseph(3)
Merrill(1)
Alan(1)
Jeremy(2)
Solomon(0)
Andrew(0)
Galvin(0)
Craig(0)
Cameron(0)
Omar(0)
Addison(0)

edited Mar 29, 2013 at 4:49

answered Mar 29, 2013 at 3:22

Dipesh Parmar

27.3k8 gold badges65 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bee Over a year ago

I want to show like this: for example i have 2 records of table category Hotel,Guesthouse and then in each record have many records and i want to select all category records and count how many sub record in each look like this (Hotel(15), Guesthouse(9))

Dipesh Parmar Over a year ago

@user1116954 so you can use subquery and add count into it

Bee Over a year ago

it still not working for me. Ok can you show me the sample code. I have table products_category and table products, in each products_category have many products in it. I just want to show it like this e.g: book(30),pen(15), it mean in book category have products 30 and pen category have products 15 in it.

Dipesh Parmar Over a year ago

@Bee okay give me few minute i setup in my local

Bee Over a year ago

Thank you so much. That what i want. I will try it.

sgeddes · Accepted Answer · 2013-03-29 04:03:03Z

1

Are you looking to use COUNT with GROUP BY:

select c.cat_id, c.cat_name, count(cd.*)
from category c
   left join category_details cd 
       on c.cat_id = cd.cat_id
group by c.cat_id, c.cat_name

This will return each category with the count of category_details associated with it. Using LEFT JOIN will return all categories -- replace with an INNER JOIN if you want only those with details.

answered Mar 29, 2013 at 4:03

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Collectives™ on Stack Overflow

how to use sql query in sql query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related