Menu
My category table is looking like the following:
------------------------------------
id | name | parent_id |
------------------------------------
1 | Vehicles | 0 |
2 | Car Insurance | 1 |
3 | Van Insurance | 1 |
4 | PhoneRecharge | 0 |
5 | prepaid | 4 |
6 | postpaid | 4 |
The output should look like the following:
---------------------------------------------------------
id | parent_id | main_category_name | sub_category_name|
---------------------------------------------------------
2 | 1 | Vehicles | Car Insurance |
3 | 1 | Vehicles | Van Insurance |
5 | 4 | PhoneRecharge | prepaid |
6 | 4 | PhoneRecharge | postpaid |
To get the above record, I need to minimize my database interaction. So I need to achieve this above data in a single query.
921
We use the SELECT * FROM table_name command to select all the columns of a given table. In the following example we are selecting all the columns of the employee table. mysql> SELECT * FROM employee; And we get the following output.
This form of SELECT uses * , which is shorthand for “select all columns.” This is useful if you want to review your entire table, for example, after you've just loaded it with your initial data set.
The first command you will need to use is the SELECT FROM MySQL statement that has the following syntax: SELECT * FROM table_name; This is a basic MySQL query which will tell the script to select all the records from the table_name table.
MySQL SELECT specific rows When a user wants to retrieve some individual rows from a table, a WHERE clause has to be added with the SELECT statement immediately followed by a condition.
Here you go: http://sqlfiddle.com/#!9/1b1a7a/14
SQL:
SELECT * FROM category t1 INNER JOIN (SELECT * FROM category WHERE parent_id != 0) t2 ON t1.id = t2.parent_id
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
