Re: category with parentid
Hello Pesebe-san, how about this? SELECT t1.name AS cat, GROUP_CONCAT(t2.name) AS subcat FROM table AS t1 LEFT JOIN table AS t2 ON t1.id = t2.parentid WHERE t1.parentid = 0 GROUP BY cat; +---+---+ | cat | subcat| +---+---+ | cat A | subcat A,subcat A | | cat B | subcat B | +---+---+ 2 rows in set (0.00 sec) regards, ts. tanaka// 2012/5/28 HaidarPesebe haidarpes...@gmail.com: Thanks for the information; I tried to call the database with something like this; select id,name from TABLE WHERE parentid='0' and a second call to the same table as this; select id,name from TABLE WHERE parentid='$id' (this $id is the result of calling the first call TABLE) and successfully. or are there other examples that simple? Thanks __ Information from ESET NOD32 Antivirus, version of virus signature database 7173 (20120527) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Query help...
I have been working with a query but need to add a few more conditions. I was a to do a query that contains a few more selects but want to know if there is a more efficient way to get results I need: This query is to find NEW or FORMER CLIENT within a moving window (Date Range) of time. I am starting off with finding a group of: paid (status = 3) appointments (appts) within a DR in a Location (r.location_id = 1) by on or many users (apt_user_id) --- SELECT c.client_id, c.first_name, c.last_name, apt.* FROM tl_appt apt JOIN tl_rooms r on r.room_id = apt.room_id JOIN tl_clients c on c.client_id = apt.client_id JOIN tl_users u on u.user_id = apt.user_id WHERE apt.appt_id IS NOT NULL AND apt.time_start between '1293858000' and '1325393999' AND apt.appt_status_id = '3' and r.location_id = '1' and apt.appt_status_id IN (3) and apt.user_id IN (506) ORDER BY apt.user_id, apt.client_id, apt.time_start --- From this above set of results, I want to find the earliest appt for each client. Then from that set of rows, I want to check to see if that client has every had an appt earlier with ANY user, those appts can be earlier than the start of the date range. If they have a count of ZERO appts prior to the earliest appt within the date range, the are a NEW CLIENT else a FORMER CLIENT. Any assistance would be appreciated. Thanks! Don Wieland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
2012/05/28 12:54 +0700, HaidarPesebe
select id,name from TABLE WHERE parentid='0'
and a second call to the same table as this;
select id,name from TABLE WHERE parentid='$id' (this $id is the result of
calling the first call TABLE)
Others have said, and I agree, you have a graph, and some graph algorithm is
needed for the indenting that you seek.
You use some other programming language than MySQL for making up the queries.
In that language keep an array of arrays; your first query,
select id,name from TABLE WHERE parentid='0'
generates more id's. Make an array of them, and that array is the other array s
first element. Make a new query,
select id,name from TABLE WHERE parentid in ($formerIDs),
and so on, each $formerID all the id's in the earlier query returned, until no
ID is returned.
Then use the array-array, and hope that each id only once appears in it:
if parentid=0, indent 0;
if parentid found in first element, indent 1;
if parentid found in second element, indent 2;
...
You can do this with a generated CASE:
CASE
WHEN parentid = 0 THEN 0
WHEN parentid IN (first element) THEN 1
WHEN parentid IN (second element) THEN 2
..
END
. This is in a REPEAT concatenated to your name, or LPAD with your name one of
the arguments, say
SELECT id,
REPEAT(' ', CASE
WHEN parentid = 0 THEN 0
WHEN parentid IN (first element) THEN 1
WHEN parentid IN (second element) THEN 2
..
END) || name, parentid FROM TABLE
.
If it is guaranteed that for each parentid there is at most one id, then all
this becomes much simpler: it is an array of id's; the variable-comparison CASE
is used.
**
Scanned by MailScan AntiVirus and Content Security Software.
Visit http://www.escanav.com for more info on eScan and X-Spam.
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
O, and the algorithm that I described only then works when the graph is cycle-free. Otherwise it forever runs. ** Scanned by MailScan AntiVirus and Content Security Software. Visit http://www.escanav.com for more info on eScan and X-Spam. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query help...
2012/05/28 08:03 -0700, Don Wieland Any assistance would be appreciated. Thanks! Maybe something like this: SELECT usie.client_id, first_name, last_name, COUNT(anie.client_id) FROM (SELECT client_id, first_name, last_name, time_start FROM tl_appt JOIN tl_rooms USING(room_id) JOIN tl_clients USING(client_id) JOIN tl_users on USING(user_id) WHERE appt_id IS NOT NULL AND time_start between '1293858000' and '1325393999' AND location_id = '1' and appt_status_id IN (3) and user_id IN (506) GROUP BY user_id, client_id) AS usie LEFT JOIN (SELECT client_id, time_start FROM tl_appt JOIN tl_rooms USING(room_id) JOIN tl_clients USING(client_id) WHERE appt_id IS NOT NULL AND location_id = '1' and appt_status_id IN (3)) AS anie ON usie.client_id = anie.client_id AND usie.time_start anie.time_start GROUP BY usie.client_id ** Scanned by MailScan AntiVirus and Content Security Software. Visit http://www.escanav.com for more info on eScan and X-Spam. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
