MYSQL :: Change To Simple Query Causing It To Run Show, Help?

Count Query Causing "excessive Processor Usage"
For the second time in the past 6 months my hosts have shut down my site because it was apparently causing a shared mySQL 5 server to max out at 100% usage. I'm not much a programmer, know even less about mySQL and obviously don't have access to the server from the back end but I'd appreciate any advice as to whether it is my query that is causing the problem:

Set rsMail = objConnC.Execute("SELECT * ,(SELECT COUNT(*) FROM comments WHERE comments.submissionID = submissions.submissionID AND comments.commentInclude = 1) as CommentCount FROM submissions WHERE PigeonHole = 'mailbag' AND Status <> 'hold' ORDER BY submissionID DESC LIMIT 20")

Apart from two instances, this query has been working fine on a page that receives between 10,000 and 30,000 visits a day.

The site is running on Windows and coded in ASP but is calling a separate mySQL server.

Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method.

I have a very simple question.

I have 2 tables: 'users' and 'posts' with the following structure:

users: id, username, email_address
posts: id, user_id, post_title, post_text

in a my own mind's mysql, I would like to:

SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id

I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.

Simple SQL Query
I was wondering if anyone could help me with this SQL query?

I'm making a football prediction website and I want to be able to display the weekly fixtures in a GridView so the admin can edit, insert, etc.

This is a simple version of the two tables that I have: Fixtures and Clubs...

CREATE TABLE Fixtures
(
MatchID Int PRIMARY KEY,
HomeClubID Int,
AwayClubID Int
)

CREATE TABLE Clubs
(
ClubID Int PRIMARY KEY,
Name Nvarchar(50)
)

.. where HomeClubID and AwayClubID are actually foreign keys to ClubID.

My Problem is that I'd like to create a query that will show each fixture with both the names of the home and away clubs on the same row, eg:

| MatchID | Name | Name |
|--------------------------------|
| 1 | Arsenal | Man Utd |
|--------------------------------|
| 2 | Liverpool | Chelsea |

... etc.

Help With Simple Query
I know this is easy to accomplish, but my brain is fried and I can't figure it out.

I have a few markets (areaID) that vendors belong to. Within these markets, vendors are assigned categories (catID).

I am trying to do a SELECT on the vendors table to find out the total number of vendors to a particular category, within a particular market. So essentially, I want to know the number of vendors within each category within each market. Does that make sense?

Can Anyone Help Me With This Simple Query?
In my database. I want to pull out one record:

Where the date I wish to publish a story where the published_web_date(date) is less or equal to now
and
The published_web_time(time) is less or equal to now

However, using the below query data is nomatter what the published_web_date is if the published_web_time has not elapsed then nothing is shown.

select * from cms_stories
WHERE section = 'news'
AND published_web_date <= NOW()
AND published_web_time <= NOW()
ORDER BY story_id
DESC LIMIT 1";

A Simple Query?
In table "vb_user" I want to pull the data in field "username" and copy that username in another table called "vb_userfield" in a field called "field5". Both tables share the field "userid" which is how the relation is made. I know this is probably very simple but man... I just can't seem to pull it off.

I've got about 500 members and I really want to populate that field "field5" with thier username without having every one of them doing it manually.

Simple Query
I was curious as to what is the best way to determine if a SELECT query was successful in retireving any rows? I've been using..
IF(!$getline){echo "no rows found"
but if there were rows found I would have to run the same query again so I can print them. Is there another way?

$get="select * from orders'";
$getresult=mysql_query($get, $link) or die(ErrorDB($realname));
$getline=mysql_fetch_array($getresult, MYSQL_ASSOC) or die(ErrorDB($realname));

Simple SQL Query
I am looking for some help on a simple (yet beyond my reach) SQL query that I can run via phpMyAdmin whenever I need to.
In a nutshell, I have a Database called A with table B, in which fields C and D sit. For each record, field C has an integer and field D has a human-readable date/time entry in the format: 2005-08-23 17:35:33.
It is important to note that the above date part is YYYY-MM-DD as the convention might be different in your locality.
Anyway, I am looking for an SQL query that will go through the table and whenever the date/time shown in field D is no more than 5 days ago, to add X (some whole number chosen and manually specified in the query each time it's run) to the value in field C

Simple Query
At least I think it should be simple, but I can't get it.

SELECT * FROM 'tableA'
WHERE 'tableA.id' = 'tableB.target_id'

Which returns an empty set, though

SELECT * FROM tableB
WHERE target_id = "b327f34a-54ea-071a-3b9d-451810085fb9"

and

SELECT * FROM tableA
WHERE id = "b327f34a-54ea-071a-3b9d-451810085fb9"

both work. That is to say that there is a record in tableA WHERE, tableA.id = tableB.target_id for AT LEAST one record in tableB

MyOLEDB Show Tables Query
Is there a way to retrieve the names of the tables in a database if you are using the myOLEDB provider in a C# application? Unfortunately Show Tables doesn't seem to work.

Query To Show Unique Entries
In the table word_list is a list of words. This field is not unique so you can get 3 of the same word (they have different attributes in other fields). How can I alter this query below so that I only get unique entries in the word field (so that the query does not return more than one of the same word).

Simple Query That I Can't Figure Out
First off, here's my table:
CREATE TABLE order_info (
ID int(10) NOT NULL auto_increment,
ordernum int(10) NOT NULL default Ɔ',
item_code text NOT NULL,
item_qty int(5) NOT NULL default Ɔ',
item_price text NOT NULL,
have int(5) default NULL,
standby int(5) default NULL,
backorder int(5) default NULL,
refund int(5) default NULL,
`status` int(2) NOT NULL default Ƈ',
PRIMARY KEY (ID),
KEY ordernum (ordernum),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=477150 ;
I would like to make a simple query that returns ordernum's that match this critera:
All of the items have a 'status' of 3
Have at least 1 'refund'
and I would like the ordernum's returned to be distinct.

Here's the query I had before the requirement of the 'refund' column:

SELECT
DISTINCT order_info.ordernum
FROM
order_info
WHERE
order_info.ordernum NOT IN (
SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.status != 3
)
AND
order_info.status = 3
I tried adding:

SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.ordernum NOT IN (
SELECT
order_info.ordernum
FROM
order_info
WHERE
order_info.status != 3
)
AND
order_info.status = 3
AND
SUM(order_info.refund) > 0
GROUP BY
order_info.ordernum
But it returns incorrect information (orders with 0 'refunds').

What is the correct way to make this query?

Bug In A Simple MySQL Query
Having a problem with a very simple query I am trying to execute. Hopefully someone can shed some light on the issue.

SQL Query

SELECT tblUserInfo.PersonalID, tblUserInfo.mainPicLink, tblUserInfo.Location FROM tblUserInfo, tblCoreUser
WHERE tblUserInfo.Orientation = 'Straight'
AND tblUserInfo.Location = 'Hampshire'
AND tblUserInfo.PersonalID = tblCoreUser.PersonalID
AND tblCoreUser.usrSex = 'M'
LIMIT 10;
Error:

Unknown column 'tblCoreUser.PersonalID' in 'where clause'

A Simple Query Question ...
The following returns one row -

PHP

SELECT ab.id, ab.menu_name, ab.keywords, bb.content, NULL , NULL , NULL , NULL , NULL , NULL
FROM menu_management AS ab
JOIN dynamenu_content AS bb ON ab.id = bb.menu_id
WHERE 1 =1
AND bb.publish =1
AND ab.version = 'fr'
AND ab.menu_name LIKE '%Nanotechnologies%'
OR ab.keywords LIKE '%Nanotechnologies%'
OR bb.content LIKE '%Nanotechnologies%'
LIMIT 0 , 30

But the following returns zero rows

PHP

SELECT ab.id, ab.menu_name, ab.keywords, bb.content, NULL , NULL , NULL , NULL , NULL , NULL
FROM menu_management AS ab
JOIN dynamenu_content AS bb ON ab.id = bb.menu_id
WHERE 1 =1
AND bb.publish =1
AND ab.version = 'fr'
AND (
ab.menu_name LIKE '%Nanotechnologies%'
OR ab.keywords LIKE '%Nanotechnologies%'
OR bb.content LIKE '%Nanotechnologies%'
)
LIMIT 0 , 30

In fact I am trying to make a query (which will later be with other queries thro' UNION ALL) where upto ab.version = 'fr' will remain constant, but the following part will change.

Means, among ab.menu_name, ab.keywords and bb.content, there may be all the three, or two or one condition only, depending on user's input. Also, the condition between them can be OR or AND, depending on whether user tends to search for all or any matches.

Simple Between Query Problem
I have multiple fields on my form where a user submits a minimum and maximum value. Such as minimum price and maximum price, so I use a Between in my queries like this.

SELECT COUNT(*) AS 'numrec' FROM tblmls WHERE mlsstatus IN('Active', 'New', 'Back on Market', 'Price Change') AND mlsclass = 'Residential' AND mlslistprice BETWEEN ?' AND ?'
My question is, is there a way to do wild cards using a between statement? Or is there a better way to do this?

Currently if they don't put anything in I don't run that part of the querie, but if they put in a minimum but no maximum I just autosubmit a large number. If they put in a maximum but no minimum I just submit a 0.

Simple Query Problem
I'm a newbie having trouble properly executing a simple query.

I'm trying to make a pathway to prevent tennis players from signing up more than once on a specific date.

Here is my

PHP

$play_date=($_POST['play_date']);
$user_id=$_SESSION['user_id'];

// Make the query to check if the player already signed up on that date.
$query = "SELECT user_id, play_date
FROM table
WHERE user_id=$user_id AND play_date=$play_date
";
$result = mysql_query ($query);
if (mysql_num_rows($result)>0) {

echo '<p><font color="red" size="+2">Sorry, you already signed up to play on this date!</font></p>'

} else {

Although the above code runs, it doesn't prevent one from signing up twice on the same date.

Simple Query Trouble
I have written a simple select query...but it is not doing what I want it to:

$query = "SELECT * FROM helpdeskticket WHERE (`customerid` = '$customerid') AND (`helpdeskticket`.`status` = 'Assigned') OR (`helpdeskticket`.`status` = 'Unassigned') OR (`helpdeskticket`.`status` = 'Can Be Closed') ORDER BY `helpdeskticket`.`ticketid` DESC

Because its wrong i'll explain what I wish it to do. Select all fields from helpdesk ticket where customerid is equal to $customerid, and the status is either assigned, unassigned or can be closed. Then order by ticketid descending.

At the moment it seems to be messing around with the AND and the OR, and is selecting everything from the database table, ignoring the customer id.

Simple Not Distinct Query?
I have a very large product table and believe that some of the fields contain duplicate values when they shouldn't. So I'm trying to create a simple query to identify those that do.. ie something like

Select modelnumber from tbl_product where modelnumber is not distinct

OR

Select NOT Distinct modelnumber from tbl_product.

These obviously don't work (Otherwise I wouldn't be asking for help!), and was wondering if there is a not distinct function that im missing or misusing?

Simple Count Query
I am trying to use the below query to count the number of images held for each book. Unfortunately it is only returning the books with images. I would like all books returned even if they have no corresponding image.

SELECT tblBooks.Title, tblBooks.Description, tblBooks.Author, tblBooks.Annotation, tblBooks.Collection, tblBooks.Price, tblBooks.SortID, tblBooks.Heading, Count( tblImage.ImageID ) AS CountImage
FROM tblBooks
INNER JOIN tblImage ON tblImage.BookID = tblBooks.BookID
GROUP BY tblBooks.BookID

Mysql Simple Query
I am doing a project in mysql and php..
While inserting the data, i made a mistake of inserting date values into the month field and viceversa. can i swap it with a query. I cant take a backup of this table.. because its very big and the webspace for mysql database for me is running very low.

ONE MORE THING :- there are many tables almost 565... can this all be swapped in a single query or procedure.

Simple Null Query
How do you say I want to select all data from this column but only show the data where the data is not equal to null.
I have;

SELECT prevleaving1
FROM `jobemployment`
WHERE prevleaving1 IS NOT null

Simple MySQL Query
I want to do this simple query but I cant remember how... (Ive done it before)...
UPDATE users SET credit = '$credits+10' where user='$user'
Basically whatever is in the credits column gets 10 added to it and the column is updated

Simple Mysql Query
I know this will be simple but I just cant get it to work and I am being tested here by my line manager.
Our database is named: lsql_Category and we have a table column named: Keywords
We have thousands of rows
The column Keywords has data in most of the rows and some without any data at all. Normally assorted and various text words.
I want to clear all data from this coloumn but DO NOT want to delete the coloumn cos we want to input data at later time.
The result should be that the column Keywords remains but will not have any data.
Can someone help? I think it is a select command and a where command but that is all I know.

Mysql Logs Show Query With T_tablename
select col1 from table1;

shows

select t_table1.col1 from table1;

in the logs.

Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57
# User@Host: counter[counter] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647
SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7;

mysql> select count(*) from _1_log;
+----------+
| count(*) |
+----------+
| 111824 |
+----------+
1 row in set (0.00 sec)

How To Show All Records In MYSQL Query Using Default Value
I'm working on somesite and i face this problem:

i want to send a parameter entered by a user from text box that called (txt1) to the query below ....

Simple Mysql Query Not Working
I have a simple table set up listing stockists and there details, included in this table I have a name column and a website column.

I wish to select the name and the website of the stockists only if a website has been entered into the website column.

Here is the query I have tried to use
Quote:

SELECT name, website FROM stockists WHERE website IS NOT NULL ORDER BY name ASC

This query is returning stockists with no website as well as the ones with websites and I donâ€™t know why!

Query Cache: Simple Question
I just have a simple question about MySQL QQ.

What I want to know is: Is the query cache only setup to apply to queries that stay the same?

PHP

"SELECT one,two,three FROM table LIMIT 1"

(Standard query, no WHERE clause, works the same for all visitors)

But a Query like this then doesn't stay the same

PHP

"SELECT one,two,three FROM table WHERE id='".$id."' LIMIT 1"

Since the ID value depends on a php variable, thus is different each time, then the query is in fact not the same.

Is the use of the query cache still beneficial in a case like this?

Simple Query? Why Is My Hair Falling Out?
Im trying to pull all records from table a and table b for 14 hrs now. It seems so simple?
both tables are indexed with the fields 'classid' and 'states'
actual results should be 36 records as you can see that is not the case.
Here is what ive tried so far:

SELECT * FROM classifieds_g WHERE states='AZ'

records 4

SELECT * FROM classifieds WHERE states='AZ'

records 32

Samples and results of what I've tried (should return 36 records)

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT classifieds.states, classifieds_g.states FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' GROUP BY classifieds.states

records 1

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ'

records 4000

SELECT DISTINCT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g, classifieds WHERE classifieds_g.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g INNER JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.states

records 1

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.classid

records 32 (all the same)

SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )GROUP BY states

records 1

SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )

records 4

Issue With Simple SELECT Query
i have three rows in my articles table. for some reason the query below does not show the first entry in that table, only the last two. is there an obvious reason why this would be the case? im displaying just the $title and the $id, simple enough! i can post database structure if required. stumped!

$queryGetArticles = "SELECT * FROM articles";
$resultGetArticles = mysql_query($queryGetArticles) or die ("couldnt get the data");
$row = mysql_fetch_array($resultGetArticles,MYSQL_ASSOC);

echo "<div id='stories'><ol>";
while ($row = mysql_fetch_array($resultGetArticles)) {
extract($row);
echo "<div class='story'><li><h3>$title (id: $articleID)</h3></li></div>";
}
echo "</ol></div>";

PLEASE HELP*SIMPLE - Mysql INSERT QUERY
And YES its connected to mysql DB i need help (a fix) ASAP thanks

<?

$sql = "UPDATE `games` SET `sponsor` ='$WebsiteURL' WHERE `gameid` =$GameID LIMIT 1";

$result = mysql_query($sql);

echo "$result";

?>

Having Problems Woth This Simple Sql Query!
hi, i have two tables essentially they are the same but one gets all normal file details placed in to it and the other has all the files that have been attached to comments put in to it...it just made programming far easier this way....

Now this seems like such a simple query but i just cant get it to work...

I want to select file_name,file_type, and upload_id from both tables so i can create an archive of all files posted to the site...how do i do this....

its something like

SELECT file_name,file_type,upload_id from uploads, comment uploads ORDER BY date_submitted DESC LIMIT 30

but thats obviously not it....i also tried a join query:

SELECT uploads.*, upload_comments.* from uploads INNER JOIN comment uploads ORDER BY date_submitted DESC LIMIT 30

Simple Select Query Take 5 Secons
This select i've got take 5 seconds and i really don't know why!!!!

The number of rows in any table is NOT bigger than 50 records!

SELECT DISTINCT(j.id) as idunic,j.* FROM jobs j
INNER JOIN jobs_categs jc ON j.id = jc.id_job
INNER JOIN jobs_type jt ON j.id = jt.id_job
INNER JOIN jobs_cities jci ON jci.id_job = j.id

WHERE j.status = 'OK'
AND j.deleted = 'NO'
AND j.suspend = 'NOK'
AND j.date_end > NOW()
AND jci.id_city = ƌ'

ORDER BY j.date_begin DESC LIMIT 0, 25

Simple Query Question: LIMIT
I'm looking to list all the rows in a database, but skipping the first 5. I know that I can use "LIMIT 0,5" to get the first 5 results, but what query do I use to list everything from 6th result onwards?

Need Help Formulating (somewhat) Simple MySQL Query
I'm more experienced in PHP, but not very experienced in MySQL. I really know nothing of joins, left joins, right joins, unions, and so on. Naturally, some of my code is becoming extremely cluttered because I don't know anything about these things. Sooo... I really need to get off my rear and learn how to manage data better.

I have two tables: users and news.

NEWS:

news_id
news_title
news_authorid
news_timestamp
USERS:

user_id
user_username
user_displayname
user_password
You'll notice that in each and every one of my news posts, it stores the author id of the user that posted the news. It'd be nice to just enter in the name, rather than an id, right? But the problem is, usernames are becoming somewhat dynamic, so it's useful to use the user's id as reference so we can select whatever we want about a user.

My question is, how can I "join" these two tables to get the user's display_name when selecting and displaying data from my news table? I could go like this:

$myfancydbclass->query("select * from news order by when_it_was_created");

while ($this)
{
$myfancydbclass->query("select * from users where user_id = $row->news_authorid");
}
But is there a way I can "join" the two tables so I dont have to run two queries just to get the news and user's display name (use fields user_id and news_authorid to join the two)? I hope I've made my question clear. If you don't understand what I'm asking, ask me a question back. As you can tell, I know nothing about this. Basically, what would the query be to join these two tables?

MySQL Crashes On Simple Query
I am setting up MYSQL/ MS 2000 server on a p4 2.6 w/ 512 RAM/80gb server.

I was testing my server, i tried to insert 7million + rows in a table, then simply try to double it by "insert into .... select * from 'same table'". I understand it could take a while to double the 1.3 gb table. howver, the MYSQL crashes after several minutes and I can't even connect back again. the error msg is 'localhost connection lost'

it happens a few times , and everytime i need to reboot the machine to get it working.
How come? BTW, i am using 4.1 alpha.

Another question, i tried to be patient and delete 5millions rows from the same table, it took about 4 mins to do it. BUt after the console told me it is done, I tried to look at the table status from the Mysql CC, i found that there are still a large number of rows in the table, and the "select count(*) "statement stills take a while to do it.

If i refresh in the mysql CC, the number of row keeps decreasing and after a while, it finally go to 1000 row(which is what I expected). But how can this happens?

I didn't experience such stuff in Oracle and MySQL. As the console told me it is done, it is done.

FYI, I tried the run the same select count(*) statement, it takes less and less time to run until the table is back to 1000 rows. It looks like that mysql is still deleting the rows after it tells me it is done...

Using A Simple 'function' In SELECT Query
Is there any way of creating a SELECT query which has as it's result a column which is comprised of a function whos inputs are the values in other results columns.
For a simple example - a four column output, x, y, x+y, x/y
SELECT A.x, A.y, A.x+A.y, A.x/A.y FROM A
This is works fine, as is, and produces the correct results. However, as the function gets more and more complicated and the A.x and A.y turn into sub-queires (and generally the expressions all become larger and then more unwieldy) not only does the function get very painful to write and debug but the query times rise rapidly also.
Can alisas' be used here (or any other methods people know of) to refer to a columns' results to ease writing the functions and keep the load on the DB to a minimum?

Can Error Message Show Entire Sql Query Instead Of Excerpt
I'm running MySQL 3.23.58 on Red Hat Linux 9.

In my web server error log I see things like:

[Thu Jan 18 00:33:56 2007] [error] [client 72.86.29.196] DBD::mysql::st
execute failed: You have an error in your SQL syntax near ' (NOW(),
"Setup [precheck] failed for 72.86.29.196 port 1239 [72.86.29.196]
(Con' at line 1 at /var/www/html/blah/mysql-help.pl line 34.

Is there a MySQL setting I can change somewhere so that it will log the
entire SQL query that generated the error, instead of just an excerpt?
It's a lot easier to figure out what went wrong if I can see the whole
query.

Simple Message Board Query Question
my table "messages" looks like this:
id
parent_id
root_id
title
author
timestamp
Maybe it is possible to pull out latest threads with last post data
My query only shows replies and last post time, but I want a complete row of the last post
SELECT threads.*, COUNT(posts.id) - 1 as replies, MAX(posts.time) AS lastpost
FROM messages AS threads
LEFT JOIN AS messages AS posts ON posts.root_id = threads.id
WHERE threads.parent = 0
GROUP BY threads.id
ORDER BY lastpost DESC

Join 2 Simple Queries Into A Single Query?
I would like to perform both these tasks in a single query, is there some kind of AND ? to chain multiple test criteria into a single query?......

MyOLEDB Doesn't Support Commands Like SHOW FIELDS, SHOW TABLES
My code is shown below, I can select * from employees but I cannot get any data back from SHOW FIELDS, I do not receive an error, in fact, something is returned but I'm not sure what. I can execute the command:

SHOW FIELDS FROM test.employees

in the Control Center just fine, even cmd.ExecuteReader will enter reader.Read() once, something is returned but what does it look like? ......

Seemingly Simple FULLTEXT Query Over Multiple Tables
Can't seem to get my head around this query. Here's what I have:

I have 2 tables:

TABLE a, TABLE b:

Table a
Columns: UID, FName, LName, Bio

Table b
Columns: UID, Specialty

I want to search these FULLTEXT Indexed table columns (bio, Specialty) from the different tables for a string, and output the resulting UID and Username, one row only per UID (UID matches between tables)...

To put it another way, if the search string is found in either of the 2 columns in the 2 tables, I would like to output the UID and FName from TABLE A.

SELECT DISTINCT E.UID, E.FName, E.LName
FROM contact AS E, brg_uid_specialties AS C
WHERE E.UID=C.UID
AND MATCH (E.Bio) AGAINST ('bubble noodle Oranges')
OR MATCH (Specialties) AGAINST ('bubble noodle Oranges')"
The resulting output from my sample tables is an enormous dataset (when it should only get 1 hit), and I can't figure out why... .

Prefix Blank Lines To A Simple Select Query
I would like to display a certain number of blank lines before the output of a select query, ie:

SELECT Var1, Var2 FROM SOME_SOMETABLE UNION ...?

Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following:

-----------------------------------
SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC

while(FetchRowAssoc())
{
SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id

echo cat_it, cat_name, itemcount, blah... etc.
}
------------------------------------

I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table.

Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category?

"SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.

Trying Simple Embedded Query ... Get Syntax Error Using PhpMyAdmin
I am trying to do this in the SQL page of phpMyAdmin, but I get a syntax
error. Can someone please help me out ?
Thanks for your time and efforts!
JC

INSERT INTO `Test` ( `pkey` , `col2` , `col3` )
VALUES (
SELECT max( `pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww` )

pkey is the primary key, not null, varchar 11
col2 and col3 are both varchar 25, and can be null.

Also, the "Select Max('pkey') from Test" query works fine as a stand-alone
query.

This is the exact error I get from phpMyAdmin.

Error
SQL-query :
INSERT INTO `Test` ( `pkey` , `col2` , `col3` )
VALUES (

SELECT max( `pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww`
)
MySQL said:

#1064 - You have an error in your SQL syntax near 'SELECT max(
`pkey` )
FROM Test, `rrrrrrrrr` , `wwwwwwwww` )' at line 3