MYSQL :: Simple Count Query

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.

Getting Row Data And A Count - Maybe Simple
I have a query in MySQL which gets the details of members of a club.

e.g. Select * from members.

however, in the same query I want to return the amount of "functions"
the member has attended.

therefore there is a "functions" table and because functions has a
many-many relationship with members I also have a "functionslink"
table.

To get the amount of functions that a particular member has attended
one would write a query like so:

select count(functionlink_id) as cnt from functionslink where
functionlink.member_id=$MEMBER*_ID

What I want to do is just have the one query that not only returns all
the member data in its returned row, but also the count of the number
of functions they attended.
At present using PHP code, I make the two calls separately to construct
this data in to a php array, but this involves many more calls to the
DB because for each row returned by the first query, I make another
call to the DB and something tells me this is a slow bad way of doing
things.

Simple MySQL Count For Non Sql Idiot!
Trying to build a category list with the number of products in each category!

current perfectly working code!

PHP

function fetchCategories()
{
    $sql = "SELECT pd_name, cat_id, cat_parent_id, cat_name, cat_image, cat_description
            FROM tbl_category, tbl_product
            ORDER BY cat_id, cat_parent_id ";
    $result = dbQuery($sql);

    $cat = array();
    while ($row = dbFetchAssoc($result)) {
        $cat[] = $row;
    }

    return $cat;
}

and now i need to add the number of products that occur within each category..been trying for last hour or so with no luck...

Database Schema!
tbl_category

PHP

cat_id
cat_parent_id
cat_name
cat_description
cat_image

tbl_product

PHP

pd_id
cat_id
pd_name
pd_description
pd_price
pd_qty
pd_image
pd_thumbnail
pd_date
pd_last_update

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

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?

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.

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?

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

Change To Simple Query Causing It To Run Show, Help?
I m looking for some help if possible. I ve got a very simple query which just outputs all the records from the table (tbl_addresses). It has 8587 records.

SELECT a.ID, a.address, a.datecreated, a.datemodified
FROM tbl_addresses as a
WHERE a.statusid = 1
LIMIT #arguments.pagesize# OFFSET #arguments.startrow#

I ve been asked to update the query to get the name field from another table called tbl_organisations. This table has 8883 records. There is a link table to relate the two (lnk_addresses). This table has 8243 records.

I ve updated my query to the following:

SELECT o.name, a.ID, a.address, a.datecreated, a.datemodified
FROM tbl_addresses as a
LEFT JOIN lnk_addresses as la ON a.id = la.addressId
LEFT JOIN tbl_organisations as o ON la.orgId = o.id
WHERE a.statusid = 1
LIMIT #arguments.pagesize# OFFSET #arguments.startrow#

The query now runs at snails pace and takes minutes to display anything. Even If I remove the 2nd join (LEFT JOIN tbl_organisations as o ON la.orgId = o.id) its slow so I guess its related to the tbl_addresses and lnk_addresses.

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?......

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 ...?

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

Select Query With Sub-query For Count And Max
I have a table "test"... as below... I want to have a result set of all the parents with the count of their children and the lastest created date of their child ...

SQL Count Query
I have 3 tables:

j_photoparent (holds photo "parent" e..g "nature", "places")
j_photocat (holds photo categories, e.g. "leaves", "snow", "london eye")
j_photosmaster holds photo details

The keys are as follows:

j_photoparent.parentID
j_photocat.catID
j_photocat.parentID
j_photosmaster.photoID
j_photosmaster.catID

I have a simple SQL statement to randomly select a photo category:

SELECT jpc.catID, jpp.parentID
FROM j_photocat jpc, j_photoparent jpp
WHERE jpp.parentID = jpc.parentID
ORDER BY RAND()
LIMIT 1
What would be great though, would be if I could combine the SQL above, to also count how many photos in the "j_photosmaster" table for each randomly selected category.

Could it be done in the same statement - or would I need to do two - the first one above and then this one:

SELECT COUNT(*) FROM j_photosmaster
WHERE CatID = &catID_FROM_ABOVE
Could it be done with a sub-select?

Help With Query Using Count
I have the following query, but for some reason I can't seem to get the price count part to work.

What I want to do is the get the max and min price for each indivdiual product and I then want to count how many products of the same where found with these different prices.

The only problem is that the count part seems to be giving me the wrong totals.

For some it is giving me 783 when there is only 2 of the same products.

Here is the query that I have.

PHP

select ides,merchantes.merid,merchant,price,min(price) as minprice,max(price) as maxprice,count(price) as pricecount,rand3,title,imageurl,brand,description,pm from merchants,prodsearches where prodsearches.merid=merchants.merid and app=Ƈ' and match (brand,category) against ('"games"' IN BOOLEAN MODE) group by brand,category order by random2 asc

Query Count
I've seen on some sites that there is a way to total up the number of queries ran on a page, and the time it took to run them - as per the bottom of this page:
Is this something which can only be done using PHP, or could I achieve the same result using ASP, by accessing something within MySQL?The reason I ask is that I have a site running MySQL on a Windows server, running IIS and ASP, and would like to set something up like the above.

COUNT() 2 Tables In 1 Query
So I have these two queries:

$result= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_main where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)");

$result2= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_pop where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)");

They output data like
Month Year, Total Views
So I can see how many total times a page was visited that month for a specific user_id.

The problem is I'm tracking 2 sections of the site "main" and "pop" which pop is a unique page that pops up when something is clicked.

I tried EVERY way to join them together but the count(user_id) doing a LEFT JOIN kept adding the count()'s from both tables together.

Is it not possible to do a count() on two tables in 1 query?

Here's one of the many combined I tried.

$sql = "SELECT a.user_id,b.user_id,YEAR(a.add_date),MONTH(a.add_date),COUNT(a.add_date),COUNT (b.add_date) FROM clogsyn_track_main a LEFT JOIN clogsyn_track_pop b ON a.user_id=b.user_id AND YEAR(a.add_date)=YEAR(b.add_date) AND MONTH(a.add_date)=MONTH(b.add_date) WHERE a.user_id='$uid' GROUP BY YEAR(a.add_date),MONTH(a.add_date)";

Can anyone help me here or is it not possible to do it in 1 query?

Query To Return Two Count()s
I'm trying to figure out a better way of doing this:
SELECT
(SELECT COUNT(*)
FROM GuestEvents
WHERE EventID = 1
AND EventStatus = 'y'
AND UserID = 'bob')
AS Event1,
(SELECT COUNT(*)
FROM GuestEvents
WHERE EventID = 2
AND EventStatus = 'y'
AND UserID = 'bob')
AS Event2
As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit:

SELECT COUNT(EventID=1) AS Event1,
COUNT(EventID=2) AS Event2
FROM GuestEvents
WHERE EventStatus = 'y'
AND UserID = 'bob'

WHERE COUNT MySQL Query
PHP

$queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews GROUP BY soundcard_model ORDER BY value DESC LIMIT 10";

Okay, there's my MySQL query.

I have a crappy little review script that lets people review home recording sound cards. This script will display 10 sound cards with the highest average score for overall value.

As you can tell, this selects the the company, model, and average overall value rating of sound cards that have been reviewed on my site. It puts them in descending order and limits the results to the top 10.

I've found that these results are easily skewed when a particular sound card gets only one review. I don't want a single person to have the power to put a sound card on top of my ratings. So, I want to select sound cards from the database that have had at least 3 votes.

While I could come up with a php solution, I'd prefer to use MySQL.

I have tried this:

PHP

$queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews WHERE COUNT(soundcard_model)>2 GROUP BY soundcard_model ORDER BY value DESC ";

But I keep getting a "Invalid use of group function" error.