MYSQL :: Distinct List Of Parents With A Child Count Result

Foreign Keys (Multiple Parents For Child)
I have three tables (Site, Client and SubClient). All of these have a _msgSetId field that references to a row in a table called MsgSet. Each table has a one to one relationship with MsgSet. Site, Client and Subclient don't share data with each other via MsgSet. They just all happen to all need to store the same stuff, so I separated it out in to a separate table.

What I want to do is be able to delete a row from Site, Client or Subclient and have the row in MsgSet belonging to it also be deleted at the same time via a cascade.
Kinda like this:

List All Parents Of A Row
I have a table of suburbs. Each suburb has a parent (which is in the same table), which is identified by a parent_id. a suburbs parent can further have another parent etc.

How would i go about writing sql to return a suburb, plus the ID of all its parents, and their subsquent parents untill the root suburb is reached (parent_ID=0)?

Count Child/parent In Same Table
Is it possible to do a count in the same table.

I will explain it better.

I have a categorie table

catID
catParentID
catName

id1: parentID:0 >> europe
id2: parentID:0 >> asia
id3: parentID:1 >> france
id4: parentID:1 >> germany
id5: parentID:2 >> china
id6: parentID:3 >> paris
id7: parentID:3 >> bordeaux
id8: parentID:3 >> nice
id9: parentID:4 >> berlin
id10: parentID:4 >> munich
id11: parentID:5 >> bejing

So let's say I want a query which displays the amount of cities in the countries:

germany: 2
france: 3
china: 1

Or the total amount of cities in a part of the world

asia: 1
europe: 5

Can it be done within one query?

SELECT Result In A List
I've a lot of values in a table and they are referenced with a id:

+----------+----------- +
| id | value |
+----------+-----------+
| 1 | aaaaa |
| 1 | bbbbbb |
| 2 | ccccccc |
| 2 | dddddd |
| 2 | eeeeee |
| 3 | ffffff |

and now I want to do a select that returns all values from every id in a list like:

1: aaaaa,bbbbbb
2: ccccccc,dddddd,eeeeee
3: ffffff

How can I do this?

Two Dbs And Combine Result List
I have two dbs with identical structures. One of them is archive db, it contains over a million entries. The other is a smaller one and I use it for storing the new information. I want to create a query to search in both dbs (preferably starting with the smaller one for better performance) and display results from both dbs, possibly discarding duplicate entries (although there shouldnt be duplicates, but its possible).
I thought about querying the smaller DB first and if no results were returned, query the archive (I use PHP), but it would be hard to combine results from both dbs this way.

Distinct Result Rows
but I could not find it.how can I get the following query to return just one record rather than all of them...i.e., just list each 'category' and the corresponding 'cat' # rather then all the records

PHP Code:

SELECT category, cat FROM bus_list

Selecting Multiple Columnn Values Into 1 Distinct/unique List
I have a table with several fields for email (primary email, alternative email, contact email)

Some times, some of these fields will be blank and sometimes the same address will be enter for both primary and contact.

I use this table and these fields to do a mailout but I don't want to mail people twice because there address occurs in more than 1 column.

I need to end up with a list containing all email values from those 3 columns that is unique (no duplicates).

I can do this by checking each one and putting it into a php array but thought there may be a nicer way to do it in the SQL?

No Result For Zero COUNT
The following query

SELECT COUNT(Id) FROM References
WHERE refereeId='3'
GROUP BY targetId;

returns the desired result if there are several rows that contain the
specified refereeId, which is great.

But if there are no matching rows, nothing is returned, and I was
expecting a return value of zero.

Am I using COUNT wrong?

Distinct Count
I have a script automatically create a new table at the beginning of every month, for the current month. The tables all have the same exact columns, but a different name (i.e. stats_October_2006, stats_November_2006). I did this just to keep the amount of records in one table as minimal as possible.I would like to be able to perform a count query across both tables and have the values merge, so it would be like performing the query on one large table.

This is the query I am able to run on just one of the tables: SELECT DISTINCT domain, count( * ) AS count FROM stats_October_2006 GROUP BY domain ORDER BY count DESC

However, I would like to perform the same query on multiple tables as if they were all one table.

How would I go about doing this? I have looked into LEFT JOIN but don't quite understand it.

COUNT DISTINCT
I have a table that stores information on visits to various business locations. In the table I have the user_id and the office_id. How can I count how many distinct users have visited all locations. I have tried the following but it groups it by office ID and I just want one total figure...

SELECT COUNT(DISTINCT(User_ID)) FROM My_Table GROUP BY Office_ID;

Get List (count) Of Records
I want to get a list of records with duplicate (or triplicate, etc.) fields.
I tried:

SELECT *, count(*) as q FROM test WHERE q > 1 GROUP BY field1;

This didn't work because "q" is regarded as a missing column. Is there a
way to do this in a single SQL statement?

Count The Result Of Group_concat?
I learned group_concat from this forum, very useful command, but I am wondering if I can * COUNT the result of group_concat *

for example, the following example, I know this is a wrong clause, but I hope I can COUNT and then use the result to do some HAVING works. So is it possible to count the result of group_concat?

SELECT class, sex, COUNT(group_concat(name)) as num from school group by class, sex HAVING num > 20

Result Of COUNT In Conditional
How can I conditionally test for the number of rows returned from a query for, say, use in a stored procedureâ€™s conditional expression?

For example, I want an INSERT to occur when

IF (SELECT COUNT(*) FROM myTable WHERE id=5) = 1

But Iâ€™ve been getting errors, and Iâ€™m guessing it has to do with the syntax of this first part of the conditional expression - how can I test the value returned by COUNT to conditionally execute some other statement?

Count A GROUP BY Result
I'm trying to count the result of a GROUP BY query without using the php mysql_num_rows() function. Is this even possible?

Retrive Result Of Count(*)
I am using Count(*) in my Select
Code:

String sqlStr = "Select count(*) from Bulletin where StartDate<=getDate() and getDate()<=EndDate";

ResultSet resultSet = executeSQL(sqlStr);

How do I get the the result of this select statement?

Count (distinct) Alternative
I have recently ported my dbase from MySQL to sqlite and i am having problems porting my sql statements over to sqlite. I cannot use the count (distinct ) feature, and need to workaround this.... what i need is an alternative to this query without the use of count(distinct) .... i need to obtain all the unique x elements that have correlating y elements that exist in table 1 and occur more than once.

table 1 has y
table 2 has x,y

i want to know retrieve all the x's where in table 1 the y values for the same x exceed one element (not the value but the number of y elements in table 1 that have the same x exceeding 1)....i hope this makes my question cleare..thanks again

| SELECT
| | x
| FROM
| | (
| | SELECT DISTINCT
| | | (2.x),
| | | count(DISTINCT(1.y)) AS count
| | FROM 1,
| | | 2
| | WHERE 1.y= 2.y
| | GROUP BY 2.x
| | ) AS collectionOFx
| WHERE count > 1

Using Count From Distinct Table...
select count(*) from tblproducts a,tblsubcategory b where (b.stSubCategoryName like '%".$keyword."%' or b.stSubCategoryDesc like '%".$keyword."%' or a.stProductName like '%".$keyword."%' or a.stProductDesc like '%".$keyword."%') and a.inSubCategoryId=b.inSubCategoryId

in above query i want to count only tblproducts
i used count(a.*) not worked

Getting A Count Of Distinct Elements
If I have a table like this:

+----+---------------+-------------+
| id | A | B |
+----+---------------+-------------+
| 1 | 1 | -1040187392 |
| 2 | 1 | -1040187392 |
| 3 | 2 | 1409286144 |
| 4 | 2 | 1409286144 |
| 5 | 2 | 1409286144 |
| 6 | 2 | 1442840576 |
| 7 | 3 | 989855744 |
| 8 | 3 | 2080374784 |
| 9 | 3 | 989855744 |
| 10 | 4 | -671088640 |
| 11 | 4 | 1107296256 |
| 12 | 4 | 1107296256 |
| 13 | 4 | 1073741824 |
| 14 | 4 | 1073741824 |
| 15 | 4 | 1107296256 |
+----+---------------+-------------+

I want to get a frequency count of the elements. Can I do it with mysql? I mean, I want something like the following:

+---------------+-------------+---------+
| A | B |frequency|
+---------------+-------------+---------+
| 1 | -1040187392 | 2|
| 2 | 1409286144 | 3|
| 2 | 1442840576 | 1|
+---------------+-------------+---------+

UNION And Count(DISTINCT)
I have a bunch of tables that store messages sent by users of my system. All of the tables are identical in structure and include a SentDate and a UserID.

I have been asked to create a query that gives a count of active users within a specific time period, an active user being a user who sent a message in the given time period.

If it were one table, then easy;

SELECT count(DISTINCT(UserID)) FROM Messages1 WHERE SentDate>=FROM and SentDate<=TO

Is it possible to get a count of distinct users across many (identical) tables? I can't see how to do it with UNION...

Select Count From A List Of Digits ??
I'm using php to extract data from postgres and print to browser. I have data that was input through checkboxes on a form - each checkbox had a different value but got pushed into the same column. Code:

Wrong COUNT() Result With Joins?
I'm using this query:

SELECT DISTINCT COUNT(Programme.ProgrammeID) AS Count
FROM Programme
INNER JOIN ProgrammeCategoryLink ON Programme.ProgrammeID = ProgrammeCategoryLink.ProgrammeID
INNER JOIN ProgrammeCategory ON ProgrammeCategoryLink.ProgrammeCategoryID = ProgrammeCategory.ProgrammeCategoryID
WHERE Programme.Enabled = Ƈ'
Which I was expecting to return the total number of records (11) that had Enabled = 1, but it returns 21 (there aren't even that many records).

I've messed around with GROUPING etc but I just can't figure it out. (By the way, the joins need to be there even though im not using them in this instance, as sometimes filtering is on ProgrammeCategory.ProgrammeCategoryID too).

Non-Numeric Result From SELECT COUNT(*)
I'm using MySQL 4.1.11 and MyODBC 3.51.11.
I've a problem with SELECT COUNT(*) query.

I'm using mysql in ASP. I'm executing this query for example:

Select Count(*) as TotalMembers From Members; //with recordset("Totalmembers")
-- or --
Select Count(*) From Members; // with recordset(0)

but both of these queries aren't giving Numeric (Integer) results...
I'm cotrolling them with IsNumeric function by ASP. But it gives False result.

Also i cannot using mathematical operators such like +-*/ . Because result isn't numeric. And an error occurs.

If you know asp, could you try to use math operators with select count(*) result?

Can I Get A Total COUNT From A UNION DISTINCT?
I've got a rather large table (18M rows) of customer prospects and I need to count the number of rows that can be returned for different customers. The wrinkle here is that I have over 20 different "scores" for each prospect and they can be combined in virtually any combination which means I cannot index them all in one index as that's too many for one index and the leftmost prefix rule means the indexes would be ignored if one of the scores wasn't used. The fastest query I've been able to construct is using UNION with individual indexes for each score:

SELECT COUNT(memberID) FROM members WHERE score1 > 100 AND areaCode IN (1, 2, 3, 4)
UNION DISTINCT
SELECT COUNT(memberID) FROM members WHERE score2 > 100 AND areaCode IN (1, 2, 3, 4)
UNION DISTINCT
SELECT COUNT(memberID) FROM members WHERE score7 > 100 AND areaCode IN (1, 2, 3, 4)

Now unfortunately this is giving me three counts of memberIDs, one for each SELECT statement, whereas I want a total number of distinct rows for the whole statement. I can't just add the three results together as each count can contain some duplicates which means the total would be inaccurate.

If I remove the COUNT() from the queries (i.e. SELECT memberID FROM members) it returns large recordsets of around 50K rows but I get a list of distinct IDs and doing a countrows on this is 100% accurate. But as you can imagine this is slow and I don't actually need this large recordset it returns. Is this slow because of the large numbers or rows or because it's merging the list to be distinct? If it's the latter then getting a distinct COUNT() will be slow anyway.

But, assuming it's not the merge that is slowing things can anyone suggest a way of getting an overall count of distinct records?

Selecting Item List And Count From Other Table
I have been failing at trying to get this query to work. Here is the query I can't get to work:

SELECT cont.key_no, (
SELECT COUNT(1)
FROM items itm
WHERE itm.cont_key_no = cont.key_no ) AS item_count
FROM containers cont
WHERE cont.status = 'OPEN';

Here is a query I managed to get working, but it doesn't quite do what I want it to. It doesn't return a container and item count for containers that don't have items:

SELECT cont.key_no, COUNT(itm.key_no) AS item_count
FROM containers cont, items itm
WHERE cont.status = 'OPEN'
AND itm.cont_key_no = cont.key_no
GROUP BY cont.key_no

Here are what the tables look like:

containers
----------
key_no
status

items
---------
key_no
cont_key_no

If you can't tell from the examples above, I am trying to return a list of all 'OPEN' containers and how many items are in the container, even if no items are in the container.

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Using Count(*) With Distinct To Determine Multiple Copies
I have an sql table 'messages' that stores peoples messages, in the form below: (Cname = conversation name)

User Recipient Cname Subject Message
Fred Bill Weather Rain Levels I think they are low
Fred Harry Traffic Congestion Its getting worse
Fred Bill Traffic Congestion Its getting worse
Fred Harry Money Savings The interest rates dropped
Fred Bill Money Savi

Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.

This is my query:

select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate

It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....

Multiple Parents
I have a table that stores listings, the type of listing can be either
an event at a night-club event, at a gig or at a bar. So i could just
assign a number to define the type.
But, the listing needs to have a parent of either club, gig or bar.
What's the best way to handle this?
At the moment there are 3 FK-fields that if not set to zero contain the
id of either club bar or gig. It somehow doesn't seem right.

Sort Children Under Parents
I am sure this has been asked many times, and I bet that it is super easy, but for some reason I am not able to wrap my head around it right now. I have a table with an id and a parent_id. I have a report that lists all of them onto a page the admins can look at. I want to have all the rows with a parent_id to fall under their parent's id. How can I pull this off?

Btw if there is no parent then parent_id is 0.

Delete Entries With No Parents
how may i delete entries with no parents (last three in this case):
Code:
mysql> select * from tree;
+----+-----------+--------+
| id | parent_id | title |
+----+-----------+--------+
| 1 | 0 | home |
| 2 | 1 | fruits |
| 3 | 2 | red |
| 4 | 3 | cherry |
| 5 | 2 | yellow |
| 6 | 5 | banana |
| 7 | 1 | meet |
| 8 | 7 | beef |
| 9 | 7 | pork |
| 41 | 40 | fgnghn |
| 38 | 37 | ss |
| 39 | 37 | dfvfbv |
+----+-----------+--------+

Select X Parents That May Have Multiple Childs
I'm playing with writing a product review system that allows multiple rating types per review (overall, cost, quality, etc.). When I display the reviews, I only want to display 5 reviews, but I don't know how to limit. I also want to be able to set up pagination for the reviews, so a preferred solution would allow me to say LIMIT 0, 5 or something.

My table structure is similar to:

Using A Field, Which Is A Comma Separated List, As The List In An IN Comparison Test.
The following query only reads the first character of a comma separated list stored in a field of the records being queried.

The varchar field in table d named "display" holds 1,4

The query " select * from d where 4 IN (display) " tests false.

If I change the field "display" to 4,1

Then the query tests true.

It is either testing only the first character, or is stopping at the comma.

I need this to test true if the value is anywhere in the list, what am I doing wrong here?

Select * Using Id List V.fast, Delete Using Id List Taking Hours.
I have a table of about 20 million rows, with about 20 columns. I want to delete 10 of these rows. I have the ids already, and id is the primary key so it should be simple - i thought.

This query completed in 0.27 seconds -

select * from users where id in (3811150, 4470160, 479422, 2491277, 9443643, 4594310, 2556165, 12075603, 2533320, 45737);

This next query, however, has been running for several hours - how can there be such a discrepancy? Should i kill it and retry?

delete from users where id in (3811150, 4470160, 479422, 2491277, 9443643, 4594310, 2556165, 12075603, 2533320, 45737);

Child Row
What is a child row?

Full List Of MyTable21 And Some List Of MyTable22
data in myTable21

(id) country
(1) Canada
(2) France
(3) Egypt
(4) America

data in myTable22

(id) city name
(1) Montreol Jane
(2) Paris Mary
(3) Cairo Tom
(4) New York Jane
(4) Chicago Tom
I have data in myTables like the above.

The following code produces the following result. but I like to produce my target result.

code1

select myTable21.id, name, country, city
from myTable21
left join myTable22 on myTable21.id=myTable22.id

where
name='Tom'

result1

(3) Egypt Cairo
(4) America Chicago

target result1

(1) Canada
(2) France
(3) Egypt Cairo
(4) America Chicago

code2

select myTable21.id, name, country, city
from myTable21
left join myTable22 on myTable21.id=myTable22.id

where
name='Jane'

result2

(1) Canada Montreol
(4) America New York

target result2

(1) Canada Montreol
(2) France
(3) Egypt
(4) America Chicago

code3

select myTable21.id, name, country, city
from myTable21
left join myTable22 on myTable21.id=myTable22.id

where
name='Mary'

result3

(2) France Paris

target result2

(1) Canada
(2) France Paris
(3) Egypt
(4) America
How can I get the full list of myTable21 and selected values from myTable22?

How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set?

I would like to pass the calculated offset into the limit half of and ordered select statement.

E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.

To do that I need to find the offset of photo with id=23 in

select id, datetime, caption from photos order by datetime;

Then I could get the result I want by doing....

select id, datetime, caption from photos order by datetime limit $offset, 5;

I've spent several hours scouring around and found some people with similar problems, but no solutions yet.

SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have;

SELECT DISTINCT `field1`, `field2`, `field3` FROM table1

This returns what I need. There is also another field (field4) which I also want to select, but not distinctly.

Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1`

The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct)

I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me.

How can I display this not-wanted-distinct field in a distinct query?

Select Distinct And Include Non-distinct Columns
I have a publication table that tracks the products assigned to various publications.

I want to select all of the distinct products, based on product_ID, assigned to a specific publication but I also want to return additional columns that do not need to be distinct.

If I use the following select:

Select distinct publication.product_ID, publication_ID.code, publication.region from publication where publication_ID = '12'

I would get a list showing the three columns that I want to see which are product_ID, publication_ID and region for publication 12 but I get too many items as the distinct function means that all three of these columns must be distinct and I only need the product_ID to be distinct.

So how do I find all of the distinct product_ID but also show other columns such as region?

Do I need to do this with some kind of self-join?

Need Help On Praent Child Query
I have a table where child categories are stored in parent with comma seperated. Is there any query with the help of which I can group them bring all of them at once.

Child Tables MySQL 4.1
Is there a way to look for all the child tables of a table in MySQL
4.1?

I know for the latest version of MySQL you can use
information_schema.table_constraints to look the same. Is there
something similar there for MySQL 4.1?

Right now, I'm running SHOW CREATE TABLE `$table` for all tables, and
then seeing if they have the table I'm looking for any foreign key
relationship. This dosnt seem to be the most effective way of doing it.

Finding Child Tables?
Is there an easy way to find child tables for any given table in MySQL
4.1? Right now, I'm running SHOW CREATE TABLE `$table` for all tables,
and then seeing if they have the table I'm looking for any foreign key
relationship. There has got to be a better way, right?

#1216 - Cannot Add Or Update A Child Row:
#1216 - Cannot add or update a child row: a foreign key constraint fails

Can anyone explain what this means. I have been updating this table today already with no problems.

Find All The Child Tables
my requirement is.. I need to list all the child tables associated with a parent table.
ie for eg : table EMP has one field emp_id as PK.
This PK is referenced by say tables WORKER,MANAGER etc.

The result of the query should be WORKER,MANAGER etc...
ie the associated tables.

Parent And Child Category Query
I have a simple table that has category_id and parent_category_id I want it to loop through and get all the categories and and their child categories - cant seem to get it.

I read this article and it hasnt helped much http://sqllessons.com/categories.html

category_id
cat_name
parent_category_id
network_id

The query that wont work is

SELECT parent.category_id, parent.cat_name as parentCat_name, parent.parent_category_id, child.cat_name as ChildName from faq_category parent
LEFT JOIN faq_category child ON parent.category_id = child.parent_category_id
where parent.network_id = '$var'

Parent-child Sophisticated SQL Query?
I have a table (relation) in my database:

---------------
| parent | child |
---------------
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 2 | 5 |
| 3 | 6 |
| 5 | 7 |
| 5 | 8 |
---------------

This table reflect the relation between two persons, the first column
is the parent, the second column is the child, the a child could be a
parent to other childs.

Is it possible to construct a SQL query to start from a parent and get
all his siblings, I was able to get the direct children of a parent
but not all his siblings, for example to get the direct children of
parent 1 I used the following query:

select child from relation where parent=1.

So Many Child Process Of Mysqld (version 4.0.21)
I upgread mysql 3.23.56 to 4.0.21, succesfully upgraded. But, I
observed abnormal behaviour of mysql (or may be i dont know). Thing
is, in mysql 3.23.56 when I start service, only one procses exist [
check thru command ps -A | grep mysql* ] but when I upgraded to mysql
(4.0.21). Above command shows me 25+ mysqld child process. Is this is
correct or any problem. Logs [/var/log/mysql.log] shows me successful
start/stop.

Deleting Parent And All Child Records
I got a table with parent records with children who can also have children. I need to delete a parent and all child records cascading down.

1) what would the query look like
2) is the table set out correctly

------------------------------------
| ID (PK) | CHILD | PARENT | NAME |
------------------------------------

the ID is made of a trimmed out datestamp and is the primary key, the child field is simply indicating wether it has children 1 = yes / 0 = no. And the parent shows 0 for a top level record or whatever value its parent has as a its value in the ID field. Code:

How To Get The Last Auto Increment Key Value For Child Tables
How to find out the last insert primary key value when a field is auto
incremented. Select the maximum value from the table is not an option
because users can add rows very quickly.

First I have to insert the values for table t_id. The value of id is needed
to insert for table t_idreply. How to get the primary key value for t_id ?

CREATE TABLE `t_id` (
`id` bigint(20) NOT NULL auto_increment,
`subject` varchar(128) NOT NULL default '',
PRIMARY KEY (`id`),
) TYPE=MyISAM AUTO_INCREMENT=1;

CREATE TABLE `t_idreply` (
`id` int(11) NOT NULL default '0',
`volgnr` int(11) NOT NULL auto_increment,
`reply` longtext,
PRIMARY KEY (`id`,`volgnr`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;