MYSQL :: Join To Find Rows Not In Second Table

Find Rows In One Table
I am setting up an osCommerce store and have discovered that some products do not have corresponding entries in the categories table. I need to find these items (there are roughly 5000 items) so that they can be fixed.
These are the relevant tables:

Table Name: products
Primary Key: products_id

Table Name: products_to_categories
Primary Keys: products_id and categories_id

I need to find all rows in products that do not have a corresponding entry in products_to_categoriesIs this possible?

How To Find Records That Are NOT Referenced In A Join Table?
Table structure...

CREATE TABLE category
(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL
);
INSERT INTO category (id, name) VALUES
(1, 'CSS'),
(2, 'HTML'),
(3, 'XML'),
(4, 'Javascript'),
(5, 'PERL');

CREATE TABLE category_j_article
(
category_id TINYINT UNSIGNED NOT NULL,
article_id INT UNSIGNED NOT NULL,
PRIMARY KEY (category_id, article_id)
);

CREATE TABLE article
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL
);

Let's say I want to find all articles that aren't filed under multiple categories? For example, if I want to find articles that aren't filed under the CSS and HTML categories?

Having MySQL Find Only Orders With Multiple Rows In Another Table
In PHP I could just do a querey of all ordernumbers and within the
WHILE do a querey of orderitems where the ordernum equals the original
querey, and then do what I need IF numrows > 1.

But surely there's a way to maybe do that in the mySQL querey? Some way
that might be more efficient than a querey of EVERY order?

If there's simply a name of some topic in mySQL that might apply, just
tell me to look that up. For example: "Take a look for 'distinct',

Getting Number Of Rows From Left Join Table
PHP

SELECT bulletin.id AS bid, bulletin.bulletinname AS bname, bulletin.bulletindesc AS bdesc, bulletin_post.id AS postid, DATE_FORMAT(bulletin_post.postingtime,'%M %d, %Y %l:%i%p') AS postdate, bulletin_post.bulletintitle AS ptitle, member.screenname AS mname, member.id AS posterid FROM bulletin LEFT OUTER JOIN bulletin_post ON bulletin.id = bulletin_post.bulletinid AND bulletin_post.postingtime = (SELECT MAX(postingtime) FROM bulletin_post WHERE bulletinid = bid) LEFT JOIN member ON member.id = bulletin_post.memberid WHERE bulletin.active = 1 ORDER BY bulletin.bulletinname ASC

My question is how would I get the count of rows if any from the bulletin_post table?

Adding Missing Rows In Table In 1 Select (+ Join) Command.
Hi all! Here's what I need to do :

I have two tables :
A B
a b c d
---- ----
1 z 1 k
2 x 5 l
3 c 6 j

I need a SELECT with JOIN that would give me :
A
a b
----
1 z
2 x
3 c
5 NULL
6 NULL

so I need to add the missing rows from the A.a and B.c JOIN,
how can I do that ?

I can't use a Union 'cause I can't use MySQL version 4.

Can't Find Rows From VB6
I have a DB with several tables. When I use the MySQL Query Browser to query the tables, I get all of the rows. By using a program written in VB6, I get the rows in all of the tables except for 5 of them. That is, if I run a SELECT on those tables, the resulting recordset is empty.

Strangely, if I run a SELECT in VB6 on a view that has an INNER JOIN on 4 tables (3 of the tables mentioned above plus another one), I get the correct result. And if I run an INSERT in VB6 on one of those tables, the new row is correctly inserted (but I can't retrieve it later with a SELECT...).

I tried with several users, including root, but the result is always the same.

How To Find Rows Where SUBSTRING Is...
I want to extraxt all rows from a table where some column starts with some given letter. For exampple : Give me all brands from the table brands where the first letter of the brand is an 'a' ?

Find Equal Rows
what could be the easyest (and shortest) way to find all doubled rows in a table? any function with mysql, or should i select all and then compare in php?

Find Rows From Table1
I am a software programmer and not a sql programmer.
Hope this is the right place to post my question.

It is about comparing two tables.

I would like to find the elements from table1 that do not exist in table2

This is my query (which is not functional)
SELECT *
FROM TABLE_1
WHERE EXISTS
(SELECT *
FROM TABLE_2
WHERE TABLE_2.MODIFDATE IS NULL);

and the tables

create table TABLE_1
(
NAME VARCHAR2(50),
SURNAME VARCHAR2(50),
MODIFDATE DATE
)

create table TABLE_2
(
NAME VARCHAR2(50),
SURNAME VARCHAR2(50),
MODIFDATE DATE
)

These are my tables

TABLE1
NAME SURNAME MODIFDATE
1 Williams1 Bernie 2006-10-30 14:25:15
2 Williams2 Bernie 2006-10-30 14:25:15
3 Williams3 Bernie 2006-10-30 14:25:15
4 Williams4 Bernie 2006-10-30 14:25:15
5 Williams5 Bernie 2006-10-30 14:25:15
6 Williams6 Bernie 2006-11-02 10:27:10
7 Williams7 Bernie 2006-10-30 14:25:15
8 Williams8 Bernie 2006-10-18 11:07:06
9 Williams9 Bernie 2006-10-30 14:25:15
10 Williams10 Bernie 2006-10-30 14:25:15

TABLE2
NAME SURNAME MODIFDATE
1 Williams1 Bernie 2006-10-30 14:25:15
2 Williams2 Bernie 2006-10-30 14:25:15
3 Williams3 Bernie 2006-10-30 14:25:22
4 Williams6 Bernie 2006-10-30 14:25:22
5 Williams7 Bernie 2006-10-30 14:25:22
6 Williams8 Bernie 2006-10-30 14:25:22
7 Williams9 Bernie 2006-10-30 14:25:22
8 Williams10 Bernie 2006-10-30 14:25:22

I would like to make query so as to get these two records from table1:
4 Williams4 Bernie 2006-10-30 14:25:15
5 Williams5 Bernie 2006-10-30 14:25:15

Find Rows With Same Info In A Column
How can I query so that I find all the rows where the information in the same columns is the same? So that I can get the information from both rows?

Can't Find Table Error, Even Though Table Exists
I use the following query:

SELECT * from ads where user!='$id' and ready=1 and category='$category' and main.id=user and main.credits>4;

And it returns #1109 - Unknown table 'main' in where clause

But the "main" table exists. What gives?
I've tried it with other tables in the same database and it says that they all don't exist.

Any ideas?

Compare 2 Tables And Find Rows Which Don't Match (was "Mysql Noob Question")
I have a products table and a products_description table. They both have products_id as primary key. However, products_description table has more products_id keys then the products table. How do i compare the 2 tables and show just the products_id that dont match from products_descripition table?

SELECT products.products_id, products_description.products_id FROM products, products_description WHERE products.products_id != products_description.products_id;

I tried that query but got some wierd results and also the results gave me 2 columns when I just want one.

Mysqldump Can't Find Table
I'm trying to do a backup of just 1 table but when I run the command mysqldump -h machine -u lagoona -p database_name [collection] > collection.sql
it complains that it can't find the table called collection which is definitely in the database. mysqldump: Couldn't find table: "[collection]"
Is this the right command to use, I'm not that familiar with mysqldump? It's on linux and I'm running it from the command line. I can connect OK to the database itself when I use the command mysql -h machine -u lagoona -p
I've also tried it as root and as my own username but no difference.

Find Names Of Every Table In A DB Using PHP
I have a database where each username has their own table, and Im making it so that instead of a ba-zillion tables, there will be just two. But I need a way to find the names of every table in my MySQL DB. I searched google and came up empty handed.

Where To Find Table Prefix
I'm trying to convert my forum to PBPBB3 from PHPBB2 and its asking what my table prefix is. Where can I find this?

Find The Size Of The Table
Is there any function OR any way to find a size of the table?

Let us assume the table contains the 3 rows and 4 columns, if i add the one more column does this increases the table size. How to find the size of the table?

Join Rows Into One Result
I'm trying to select letters in a word from a table with the ascii representations. I am selecting the letters successfully, in the correct order. The rows returned give me a letter in each row. I would like it to return one row with the joined word.

aka. It is returning
a
r
d
v
a
r
k

I would like
ardvark

This probably has to do with GROUP BY, but I don't know any functions to join characters into a string.

Can I Use Mysql Query In Jsp To Find Table?
we can use sql query to find attribute in jsp. If I want to find table?is it possible?

How Can I Find Out The Names Of The Indices Of A Table?
I can do CREATE INDEX ...e.x.:

CREATE INDEX foo ON some_table;
CREATE INDEX bar ON some_table;

Is there a SHOW commaand that will display the indicies of a table?

e.x.: SHOW INDICIES ON some_table;

Find If Field In A Table Exists Or Not
Is there a way to find out if a field in a table exists before trying to send data to it?
then if not exists create it.

How Can I Use A Join With Possibly Nonexistent Rows?
here's the situation: I'm using mysql to house a database of quotes, complete with ratings by users. So in one query, I need to get everything with a certain rating from the "quotes" table, get the submitter's username from the "users" table and finally check the "ratings" table to see if the user viewing the quotes has already rated it.

The "ratings" table consists of the user id and the quote id. So ideally, I'll be able to to get the first 25 quotes and end up with each in a row something like this:

submitter's id - quote id - quote text - user id - quote id 2

where "user id" and "quote id 2" come from the ratings table and will be NULL if the quote hasn't been rated by the current user, and will be equal to the current user's id and the quote id if it has already been rated.

So far, I think the closest I've come to success was was using this subquery (which would probably be terribly slow even if it did work):

SELECT quotes.*, user.username, ratings.quoteid AS votedid, ratings.userid AS votedby
FROM quoteratings AS ratings
INNER JOIN quotes AS quotes
LEFT JOIN user AS user ON user.userid = quotes.userid
WHERE quotes.quoteid IN (SELECT quoteid FROM quotes WHERE average >= 0 AND approved = 1)

I hope that explanation makes sense, I know there has to be a fairly easy way to do this, but I just can't find it.

Listing Multiple Rows In One Row With JOIN
I have assets and tasks in my table. There are multiple assets assigned to each task. What I want to do is to list the asset followed by all the tasks linked to it in one row.

SELECT * FROM asset
LEFT JOIN task ON (asset.id = task.asset_id)
WHERE asset.id=task.asset_id
GROUP BY asset.name

The above is close, but it will only join the first matching entry from the task table, the result I was hoping to get was something like.

asset1.id asset1.name task1.name task1.status task2.name task2.status

Is there an easy way to do this in SQL?

Error 1032 - Can't Find Record In Table
I have a problem with a very simple table when using partitioning in mysql 5.1.11 on SuSE Linux 10.0:

Table Information:

CREATE TABLE `adr-0` (
Adr varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
Qty int(10) NOT NULL,
PRIMARY KEY (Adr),
)
ENGINE=MyISAM DEFAULT CHARSET=latin1
COLLATE=latin1_german1_ci
PARTITION BY KEY (Adr) PARTITIONS 10 ;

now i'm entering data into the table:

insert into `adr-0` values ('TestAdr','1')
on duplicate key update Qty = Qty + 1;

this works fine until the "on duplicate key" triggers. When a duplicate key is found I get the error 1032 "can't find record in adr-0" instead of an increasing field qty.

I see no limitations in the partioning section of the mysql documentation which could cause this error. On a non-partitioned table it works fine under 5.1.11.

Is someone out there who knows if this is a bug or a feature?

How To Find Out If A Msql Table Is Locked From A Php Script
Is there any way with a php to find out if a table is locked before using a mysql_query on the table?

Remove Duplicate Rows - MySQL4 JOIN
I am looking to remove duplicate rows from a table.
I have limited knowledge of MySQL - so please bear with me.
The following code correctly displays the duplicate rows:

SELECT firmname, address1, custom_2, MIN( selector ) AS min_sel, count( * ) AS issimilar
FROM my_table
GROUP BY firmname, address1, custom_2
HAVING issimilar > 1;
Now I try to run an inner join to show the rows, so that I can then delete them (delete is not included yet)

select bad_rows.*
from my_table as bad_rows
inner join (

SELECT firmname,address1,custom_2,MIN(selector) AS min_sel,count(*) AS issimilar
FROM my_table
GROUP BY firmname,address1,custom_2
HAVING issimilar > 1

) as good_rows on good_rows.firmname = bad_rows.firmname
AND good_rows.address1 = bad_rows.address1
AND good_rows.custom_2 = bad_rows.custom_2
AND good_rows.min_sel <> bad_rows.selector;
The problem is that I can't use a select inside a JOIN in MySQL4.

I don't know enough about MySQL to rewite the above so that it will work in MySQL4 - I know its something like:

SELECT t1.firmname, t2.firmname, count(*) AS issimilar
FROM my_table AS t1 INNER JOIN pmd_listings AS t2
ON t1.firmname = t2.firmname
GROUP BY t1.firmname, t2.firmname
HAVING issimilar > 0
ORDER BY issimilar DESC;
Hoping someone can help. Also have a question on comparing data from other tables - but I need the above to work first.

Creating Non-existent Rows In Query With Join
I want to make report using PivotTable/CrossTab and I used an application to create it.
The problem is, I want to so show NULL value to the temp table that will be the source of my report.

I'm using this query:

Can't Use JOIN And SUM To Roll Up Rows Into Multiple Columns
Let's say I've got the following table "Eats" that contains row after
row of Calories consumed by children eating lunch at the school
cafeteria. The "ID" column is an autoincrement field I added, but it
doesn't seem to be doing me any good.

mysql> SELECT * FROM Eats;
+------+------+------+----------+----+
| dow | who | sex | Calories | ID |
+------+------+------+----------+----+
| Mon | John | Boy | 2600 | 1 |
| Mon | Tom | Boy | 1900 | 2 |
| Mon | Jane | Girl | 1200 | 3 |
| Tue | Tom | Boy | 1600 | 4 |
| Tue | Jane | Girl | 1300 | 5 |
+------+------+------+----------+----+

The output I WANT is a table with total calories broken down by gender
and day, i.e., one (and only one) table with a "Boys" total and a
"Girls" total. In this case I want to see:

+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 1200 |
| Tue | 1600 | 1300 |
+------+------+-------+
It's important to have One table with Two headers, not the other way around.

Not knowing any better I tried a JOIN and came closest with:
mysql> SELECT a.dow AS "Day",
-> SUM(a.Calories) AS "Boys",
-> SUM(b.Calories) AS "Girls"
-> FROM Eats AS a
-> JOIN Eats AS b
-> ON (a.sex="Boy" AND b.sex="Girl" AND a.dow=b.dow) GROUP BY a.dow;

Which produces:
+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 2400 |
| Tue | 1600 | 1300 |
+------+------+-------+

Everything is right except Mon-Girls, which is twice the real value.
I figure this is due to the pairing of Jane-John and Jane-Tom, causing
Jane's meal to get counted twice. But that pairing is necessary to make
sure we count all the boys' Calories too.

Am I overlooking something obvious? Are JOINs and SUMs just a bad idea?
Most importantly, is there some other way to accomplish the task? I've tried
many, many variations on the above code (with and without using the ID field)
and gotten nowhere.

For this simple example I could do SELECTs INTO variables, but that solution
doesn't generalize to hundreds of rows, nor cases where the children are
further broken down by age. Code:

LEFT JOIN Produces Extra Rows
The `suggestions` table contains 2,265 rows. The `ALL_PHS_SCHEDULES` table contains over 22,000 rows. I have done my homework and I read that the Left Join return ALL rows from the first table and the matching ones from the other table. I am expecting exactly 2,265 but the query is returning 2,734 rows.

Quote: SELECT *
FROM `suggestions`
LEFT JOIN `ALL_PHS_SCHEDULES` ON `suggestions`.course_number = `ALL_PHS_SCHEDULES`.course_number
AND `suggestions`.section = `ALL_PHS_SCHEDULES`.section AND `suggestions`.id = `ALL_PHS_SCHEDULES`.id
ORDER BY `ALL_PHS_SCHEDULES`.`course_number` , `ALL_PHS_SCHEDULES`.section ASC

Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A..

Example:

B.part = "abcdefg" matches A.part ="cde"

I cannot put literal in query.. it must be from column data..

It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.

Problem With Left Join And Count, Returning More Rows Than What It Should
Am having a problem with a query, strangely ...

PHP

SELECT *
FROM table1 AS mt
LEFT JOIN table2 AS pt ON mt.p_id = pt.p_id
WHERE my_field = 'somevalue'

Is returning a much bigger number (12 rows) for me, then what it should.

PHP

SELECT *
FROM table2
WHERE my_field = 'somevalue'

Is returning only 2 rows

Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site

Titles
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And POSTS
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan

Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being

PHP

SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
            FROM grps
            RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
            GROUP BY grps_c.catid
            ORDER BY grps_c.title

which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add

PHP

LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')

however adding that removes the rows that have a 'count' or NULL or Zero.

Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured.

A & B

Table A, has column: Product (product code) as primary key

Table B doesn't.

Apart from that they have the exact same fields.

There's also a column: supplier

I want to

Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples

So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes.

mysql version 4.0.27

ALTER TABLE Deleted The Rows - Error: Table Is Full
Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211).

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 5359211
Avg_row_length: 801
Data_length: 4294967288
Max_data_length: 4294967295
Index_length: 45783040
Data_free: 0
Auto_increment: 5406252
Create_time: 2007-04-20 18:26:38
Update_time: 2007-08-22 09:55:22
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Here is the table structure.

CREATE TABLE `messages` (
`id` int(11) NOT NULL auto_increment,
`subject` varchar(255) NOT NULL default '',
`message` text NOT NULL,
`attachment_path` varchar(255) default NULL,
`new` tinyint(4) NOT NULL default Ɔ',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

On searching I found the this link.
http://dev.mysql.com/doc/refman/4.1/en/full-table.html

According to manual i executed the following query

MySQL
ALTER TABLE messages MAX_ROWS=20000000000;
I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this.

After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left.

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 170109
Avg_row_length: 649
Data_length: 110563036
Max_data_length: 281474976710655
Index_length: 1961984
Data_free: 0
Auto_increment: 5409214
Create_time: 2007-08-23 10:07:08
Update_time: 2007-08-23 13:41:57
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=4294967295
Comment:
1 row in set (0.00 sec)
What could be the reason of this rows deletion?

I am on 32 bit system.
MySQL version: 4.1.18-standard-log
Operating System : CentOS 3.x
Memory: 4 GB DDR

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning.

current query looks like this:

SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id;

So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?

Deleting Rows In One Table That Don't Have A Corresponding Row In Another Table.
I have two tables:

Table: Users
UserName (primary key)
Password
PurgeDate

Table: Logins
UserName
LoginDate

I want to do two things:

1) Delete all records from the Logins table where the value of UserName cannot be found in the UserName column in the Users table.

2) Records are purged from the Users table based on the PurgeDate. When that happens, I want to purge all records from the Logins table where UserName has the same value as the value in UserName in any record purged from the Users table.

Rows Of A Table As Columns Of Another Table
with the following tables i have the problem written below:

table group
gr_id, name

table gr2kr
id, gr_id, kr:id

table krit
kr_id, name

now i don't want to get an output like

name1 krit1
name1 krit2
name2 krit1

i want something like this
name1 krit1 krit2
name2 krit1

does anybody have an idea how or if it is possible?

Copy Rows From One Table To Another Table
One is the "main" machine and all the others will synch data up with it it once in a while. Is there a command to insert rows into one table on a different machine using the table on the current machine? Like....

Main Table 1 on main machine e.x. 192.168.1.1
name|email|listdate
------------------------
tom|a@b.com|1-1-2005
dick|c@d.com|1-1-2005
harry|e@f.com|1-1-2005
etc.

Temp Table 1 on field machine e.x. 192.168.1.2
name|email|listdate
-----------------------
larry|g@j.com|6-1-2005
mike|h@k.com|3-1-2005
fred|i@l.com|5-1-2005

i know you can do something like the following if they are both located on the same machine but i don't know how to structure the command for different machines:

insert into real_table (col1, col2) select x, y from temp_table;

Trying To Join Nested Table With Another Table
I am very much a newbie at sql and I was wondering if anyone here could give me some help with my sql. What I am trying to do is display a list of categories in a hierarchal way along with the number of products that fall into each category.

I have a nested table that hierarchically stores categories. ....

How To Get Top 8 Rows From A Table
I have a table with the fields xId, yId and count. count is an int from 1 to n. I would like to get the 8 rows that has the highest count. Like 983, 980, 850, 843.. and so on. Also I want the 8 highest counts grouped with yId.

That was a pretty simple query I found out. I read about it here:
http://www.plus2net.com/sql_tutorial/second-highest.php

Get Rows From One Table That AREN'T In Another
I have 2 tables, e.g., as so:

Set Of Rows From A Table
I have a table which has 2000 rows, I want to take a sample set of rows (500) and interested in four column values (out of total 10 columns), one column value amongst this is most important and have multiple type of value, i want my sample to reflect each type of value (single digit, multiple digit, alphanumeric etc).
right now i have absolutely no idea how to do it, and i want it done soon.

How To Clear All Rows In The Table?
I wonder how can I quickly delete all row in the table? Can I do it just with one query without dropping table and creating new one?

Maximum Rows In Table
I heard there's a max of about 65k rows not counting longtext and blobs in a mysql table

How does longtext and blobs affect the max row count? And, what happens if you go over the max rows -- does the table auto-trim or does everything blow up? (Sorry... too scared to try myself )

Combining Rows In Table
I'm not sure if this can be done in MySQL, but i'll ask anyways..

id Name a b c d
------------------------------------------
1 Same 1 0 0 1
2 Same 0 1 0 0
3 Same 0 0 1 0
4 Sametwo 1 0 0 0
5 Sametwo 0 1 0 0
6 Samethree 0 0 1 0
Is there a way to turn this table into:

id Name a b c d
------------------------------------------
1 Same 1 1 1 1
2 Sametwo 1 1 0 0
3 Samethree 0 0 1 0
Trying to clean up legacy databases and it's proving to be quite painful..