SQL Looping Query

Query Help Looping Through Records
The below Query loops through two tables in mysql and outputs all records where a match_date in a reports table (re) is equal to a match_date in a fixtures table (f).

Now this works well, but what I want to do is say to my query is find all of these dates BUT

- As soon as the FIRST f.date is found that does not have a re.date matching it output that information and limit it to 1 .....

---

Looping Certain Database Entries
I need to determine how many links there are in a database table. Then loop the links to be displayed in the menu. Can someone please help me with this? Thanks in advance.

---

Looping Through Results - Different Display For The First 3?
I have a simple news database table (id,title,body,date)

There is a page I've created which should show the first 3 recent articles in full and then only links to the others.

what is the neatest way to do this ? (php/mysql)

---

Looping Through Database Objects
I am trying to create a process that loops through all the databases on my server (75) and run the same set of sql statements on each database. Here is what I have so far...

begin
declare dbname char(15);

SELECT schema_name INTO dbname FROM information_schema.schemata WHERE schema_name like "acc340%" LIMIT 1;

while dbname IS not null do (this is what doesn't work)
SELECT dbname;

SELECT schema_name INTO dbname FROM information_schema.schemata WHERE schema_name > dbname AND schema_name like "acc340%" LIMIT 1;
end while;
end

The loop works but since the select into never changes the value to null after it runs out of data, my loop continues forever. Is there a way to get around this annoying problem?

---

Looping Through Dates Without Table
Is there a way to have mysql simply output all the dates between 2007-10-10 and 2007-10-15 without using a single table?

Resulting rows would be :

2007-10-10
2007-10-11
2007-10-12
2007-10-13
2007-10-14
2007-10-15

---

Write A Script With Looping Constructs
I want to write a script to insert some rows in a database. The user of the script will login to mysql and paste the script at this point.

Is there a way to use looping constructs, variables, etc. Iam thinking of a PL/SQL equivalent.

---

Looping Through Insert Statements Is Timing Out, Alternative?
I am looping through and creating about 600 inserts/executes in PHP and it's timing out.

I have tried aggregating them into one string that ends up like:
INSERT INTO table VALUES(val1, val2,...);INSERT INTO table VALUES(val3, val4,...);INSERT INTO table VALUES(val5, val6,...);...

But is errors out.

I am using PHP5 and MySQL 4.1. How can I get around that? Is there an alternative?

If prepared statement is a way around it, any easy examples to look at?

---

How Do I Calculate A New Value From 2 Columns Whilst Looping A Table
I have a single table and i would like to calculate two columns (imp and clicks) while looping through the results.

this is what the table would look like...

campaign | imp    | clicks
A        | 200000 | 100     
B        | 100000 | 40
C        | 50000  | 10
D        | 300000 | 200

and here's what i would like to achieve with a mysql query and php. I would like to create a new column CTR% and calculate the CTR for each row by looping through the table...

campaign | imp    | clicks | CTR %
A        | 200000 | 100    | 0.05
B        | 100000 | 40     | 0.04
C        | 50000  | 10     | 0.02
D        | 300000 | 200    | 0.06


i've tried a few things that i've read up on by googling but none really worked (based on using a while loop). now i would like to wipe the slate clean and get your suggestions on what to do??

---

Looping Through MySQL To Change The Id (using PHP) To Equal The Number Of Rows
When deleting a row from the database, that id is now missing.
So what I'm trying to do is update in a loop (maybe an sql loop if
there is one) of all of the id numbers - sort of like renaming them.
It did partly work because all the id's were set to 22. Thats because
there was 22 rows.

Here's the code I used:

---

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.

---

How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post):
*table person with fields:
-persid: autoincrement id
-name: name of the person
*table material with fields:
-materialid: autoincrement id
-material: name of the material eg "wood"
*table color with fields:
-colorid: autoincrement id
-color: name of the color eg "green"
*table persmaterial with fields:
-persmatid: autoincrement id
-persid: link to table person
-materialid: link to table material
*table perscolor with fields:
-perscolorid: autoincrement id
-persid: link to table person
-colorid: link to table color

In the webapplication the search can be entered by the users as a kind of pseudo query:
(color=red OR color=blue) AND color=green AND material=iron

My question is: how can I automatically transform this pseudo query into an efficient MySQL query?
I have tried out some different options:


Option 1:
(SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION
(SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1))
Remarks:
*I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query?


Option 2:
SELECT persid FROM person p WHERE
(EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid)
OR
EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid))
AND
EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid)
AND
EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid)
Remarks:
*very easy to get from pseudo query to MySQL query but what about performance?

Option 3:
SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE
p.persid=pc.persid
AND
(pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3)
AND p.persid=pm.persid
AND pm.materialid=2
GROUP BY p.persid HAVING
sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1
AND
sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1
AND
sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1
Remarks:
*this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form;




Option 4
SELECT DISTINCT pc1.persid FROM perscolor pc1
INNER JOIN perscolor pc2
ON pc1.persid=pc2.persid AND pc2.colorid=2
INNER JOIN persmaterial pm1
ON pc1.persid=pm1.persid AND pm1.materialid=2
LEFT OUTER JOIN perscolor pc3
ON pc1.persid=pc3.persid AND pc3.colorid=1
LEFT OUTER JOIN perscolor pc4
ON pc1.persid=pc4.persid AND pc4.colorid=3
WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL
Remarks:
*this option requires the pseudo query to be turned into a product of sums form

Option 5:
SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid
Remarks:
*very easy to get from pseudo query to MySQL query but what about performance?



-- phpMyAdmin SQL Dump
-- version 2.6.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 19, 2006 at 01:13 PM
-- Server version: 4.1.9
-- PHP Version: 4.3.10
--
-- Database: `aston`
--

-- --------------------------------------------------------

--
-- Table structure for table `color`
--

CREATE TABLE `color` (
`colorid` int(11) NOT NULL auto_increment,
`color` varchar(30) NOT NULL default '',
PRIMARY KEY (`colorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `color`
--

INSERT INTO `color` VALUES (1, 'red');
INSERT INTO `color` VALUES (2, 'green');
INSERT INTO `color` VALUES (3, 'blue');
INSERT INTO `color` VALUES (4, 'yellow');

-- --------------------------------------------------------

--
-- Table structure for table `material`
--

CREATE TABLE `material` (
`materialid` int(11) NOT NULL auto_increment,
`material` varchar(30) NOT NULL default '',
PRIMARY KEY (`materialid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `material`
--

INSERT INTO `material` VALUES (1, 'wood');
INSERT INTO `material` VALUES (2, 'iron');

-- --------------------------------------------------------

--
-- Table structure for table `perscolor`
--

CREATE TABLE `perscolor` (
`perscolorid` int(11) NOT NULL auto_increment,
`persid` int(11) NOT NULL default Ɔ',
`colorid` int(11) NOT NULL default Ɔ',
PRIMARY KEY (`perscolorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `perscolor`
--

INSERT INTO `perscolor` VALUES (1, 1, 1);
INSERT INTO `perscolor` VALUES (2, 1, 2);
INSERT INTO `perscolor` VALUES (3, 2, 1);
INSERT INTO `perscolor` VALUES (5, 3, 3);
INSERT INTO `perscolor` VALUES (6, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `persmaterial`
--

CREATE TABLE `persmaterial` (
`persmatid` int(11) NOT NULL auto_increment,
`persid` int(11) NOT NULL default Ɔ',
`materialid` int(11) NOT NULL default Ɔ',
PRIMARY KEY (`persmatid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `persmaterial`
--

INSERT INTO `persmaterial` VALUES (1, 1, 1);
INSERT INTO `persmaterial` VALUES (2, 1, 2);
INSERT INTO `persmaterial` VALUES (3, 2, 1);
INSERT INTO `persmaterial` VALUES (5, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

CREATE TABLE `person` (
`persid` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
PRIMARY KEY (`persid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `person`
--

INSERT INTO `person` VALUES (1, 'john');
INSERT INTO `person` VALUES (2, 'emily');
INSERT INTO `person` VALUES (3, 'liz');

---

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.

---

Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles.

based on this I now want to find all the individuals that are affiliated to those listed in the first query ....

---

Big Query - Query Not Completely Stored In Memory
I have this query and when executed in mySQL query window throws error that "Big Query - Query not completely stored in memory". Also executed thru app, the program justs hangs. I have checked all indexes, they all look good.....

---

Pagination W/1 Query + How To Use Query With Indexes
i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind.

i have mysql 4.1.10

1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes.

2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries?

---

Reusing A Query Output In The Same Query
I am guessing a basic question but not one I can find an obvious answer to.

If I create a calculated or modified column in a query (such as a modified text string), and then want to reuse that in the same query as I need to do three or four operations on it in sequence, how do I do it in mySQL 4.1?

Do I need to create a new column to store the interim result in an existing table (and then clear or alter it each time I run the query), or create a temporary table, or is there an easy way to reuse the query output in the same query (does the query have a name like a table name)?

If it requires a new column or table, are there particular disciplines to ensure it is robust and self maintaining?

---

Possible To Execute A Saved Query From Within Another Query?
Does MySQL provide a way to refer in a query to stored query instruction as if it were an existing table, in such a way that the stored query is exected when the query that refers to it is executed?

---

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

---

Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop.

$sql = "SELECT * FROM mytable order by points desc limit 10";
$rec = mysql_query($sql) or die(mysql_error());
$datas = mysql_fetch_array($rec);

do{
$sq = "Select * from secondtable where linkid = '$datas[id]'";
$rst = mysql_query($sq) or die(mysql_error());
$rows = mysql_fetch_array($rst);
echo "$rows[somefield]";
}while($datas= mysql_fetch_array($rec));
This works perfectly but I want the second query to be out of the loop if there is a way and how.

---

Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database:
PHP

$get_stats_newthreads = $db->query_read("    SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible    FROM " . TABLE_PREFIX . "thread AS thread    WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10)    ORDER BY rand() DESC LIMIT 5");

and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid

---

Using A Query Result In Another Query
Can I use the results of a SELECT query as a "table" in another query?

I want to let my user pick a subset of the data, then refine it further. So ... do I have to repeat all the selection criteria at each step, or can I just do refer to the last query result?

If so, what's the PHP syntax for this?

---

Run A Query On The Results Of A Query?
Lets say I have a query that searches for people living in Colorado.
That results in a list on a "results.php" page. Now I want to query
that result and search further for people who use Linux.

I know I can do this from one query, but I would like to create
several checkboxes on my results.php page where I do a further
secondary query. Possible? I guess I want to query a query.

---

Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array).

With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...

---

Rewriting A Query Without A Sub Query
I've recently changed hosts and found that some of my code broke. The new host is using mysql version 4.0.25 which does not support sub-queries (and they won't upgrade). I'm trying to figure out how to rewrite the following query so it will work on 4.0.25 but not getting anywhere.....

---

Is It Possible To Run A Query On The Results Of A Query?
I have been trying to figure this out but no luck.

Lets say I have a query that searches for people living in Colorado.
That results in a list on a "results.php" page. Now I want to query
that result and search further for people who use Linux.

I know I can do this from one query, but I would like to create
several checkboxes on my results.php page where I do a further
secondary query. Possible? I guess I want to query a query.

---

Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order.

I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So:

SELECT * FROM order_log WHERE product's group_id = 1.

---

Sub Query And Count Query
i have a database with the following structure

id | MoveDate | ItemId | SiteID

(a new entry is entered when an item is moved from 1 site to another)

and i am trying to forumlate a query so that i can count how many days each item was at a specific location
so lets assumes i have the following data

1 | 01/01/2007| 1 | 1
2 | 03/01/2007| 1 | 2

how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5

---

Query From Query Results
I have a report I'm working on that is sort of like google adsense, where it tracks ad clicks and views.
I need to provide the option for the user to narrow down the results by date. I have my query worked out, but would it be better for me to run the query again and add the date information to the query, or should I cache the results and then query them?
If I were to cache the results and then run queries off that, what would be the advantages or disadvantages? I also have no idea how to go about doing that. Should I create temporary tables to hold the queried information or is there another way?

---

Mad Query
Hi every body I have this query in multi tables

select t.id, t2.id from table t, table2 t2 where t1.id = t2.id

the result is
t1, t2
1 3
1 6
1 8
2 4
3 5
3 8
3 3
...
...
..

I need to make it
t1 , t2
1 3,6,8
2 4
3 5,8,3
....
...

is there anyway to make this query?

---

Can This Be Done In One Query?
I have the following two tables:
article: article_id, subject_id_1, subject_id_2, subject_id_3
subject: subject_id, subject_text
subject_id_1, subject_id_2, subject_id_3 are the subject_id in subject table
for article_id 5, I want to get all the subjects, i can do this with 3 querys:
for subject 1:
select subject.subject_text from article, subject where subject.subject_id = artible.subject_id_1 and article_id = 5.
for subject 2:
select subject.subject_text from article, subject where subject.subject_id = artible.subject_id_2 and article_id = 5.
for subject 3:
select subject.subject_text from article, subject where subject.subject_id = artible.subject_id_3 and article_id = 5.
Is there a way to do the same thing with one query?

---

Query Help Please?
Completely puzzled by this one and wondered if someone could help

I have three tables:

A players table:

A appearances table:

A goals table:

Now here's what I want to:

- look at all records in the goals table
- Get the values from player_one_goals, player_two_goals etc...
- Match it against the appearances table ie: player_one=player_one_goals ie player with id= 2 scored 1 goal.
- Then find the player_name value of player_one by matching the value of the player against the player_id in the players_table

---

Need Help With A Query
Ok, I can't get this to work..
this is my database:

id nav1 nav2
1 company jobs
2 company this
3 company that
4 services foo
5 services bar
6 services blabla
now i want to select company, jobs and services, foo
so i want to select each nav1+nav2 pair with the lowest id (id1 and id 4 in this case)
makes sense?

---

Help With Query
So I have two integers, start and limit. I also have a table:

-----------------
days
------------------
id
sa01
sa02
sb01
sb02
sb03
sb04
sb05
sb06
sb07
sb08
sb09
sb10
sb11
sb12
sb13
sb14
sb15
sb16
sb17
sb18
The id column is a standard auto_increment primary key, whilst all the other columns are unsigned smallints that default to 0.
Basically, I want to select every s* column that is 0 (or any given number) from the rows with id's between the start and limit.

So kinda merging this query:


SELECT * FROM days WHERE id >= 2 AND id <= 200
and this pseudo-

PHP

row_as_array = db_resultforeach (row_as_array as key => value)   if (value == 0)      // action   else      // we didn't need this column

---

One Query From 2 DBs
I have a form that needs to input data into more than one table in my DB. I'm using this basic function for single table data entry:

PHP

function libraries_addTher($data) {

    $sql = "INSERT INTO ".TABLE_INSURANCE." SET";
    $sql .= " insCompanyName = '".$data["insCompanyName"]."'";
    $sql .= ", address1 = '".$data["address1"]."'";
    $sql .= ", address2 = '".$data["address2"]."'";
    $sql .= ", city = '".$data["city"]."'";

    my_query($sql);
    return true;
}//libraries_addTher

I also need to include a query like this with the same form submission:

PHP

$sql = "INSERT INTO ".TABLE_PROVIDER." SET";
    $sql .= " firstName= '".$data["firstName"]."'";
    $sql .= ", middleName= '".$data["middleName"]."'";
    $sql .= ", lastName= '".$data["lastName"]."'";

How do I concatenate those two queries into the $sql variable?

---

How Can I Fix This Query?
I need to select rows where country_code = "JE" and feature_code = "1" or "2". This doesn't work for me:

SELECT * FROM table WHERE feature_code = &#391;' OR feature_code = &#392;' AND country_code = 'JE'

I wonder how can I fix this query to select right rows?

---

Help With Sql Query
PHP

$q1 = "select * from re2_listings, re2_agents, re2_priority where re2_listings.AgentID = re2_agents.AgentID and re2_agents.PriorityLevel = re2_priority.PriorityLevel and re2_agents.AccountStatus = 'active' order by DateAdded desc limit 0,9 ";

what i want to do is add username in the query how would i do this if i put username='$username' i get a error Column 'username' in where clause is ambiguous
so how do i put username in the query without errors
thanks paul

---

Need Some Help With A Query
I'm not that bad at MySQL but I'm struggling with this one. I'm setting up a tutorial site and on the front page I'm trying to display the latest 5 tutorials. It was fine before but this time I've added categories. Every tutorial must be in a subcategory, a tutorial may NOT be in a parent category.

'tutorials' table has a field called 'cat' for the id of the category.
'categories' has cid ( for the id ), cat_name ( for the name )

I need thus to select all the tutorials then join it on to the categories table to get the subcategory. This is fine but my problem is that I need to also select the parent category of the subcategory. I'm also trying to do all this in 1 query rather than a new query for each tutorial.

---

Help Me With A SQL Query
I have two tables with following structure:

Table A:
int a_id

Table B:
int b_id
int a_id

I want to remove all records from B if their a_id don't exist in A. It would be good to use one SQL query to do this. As these two tables are very big, it's not workable to check each record in B with one query.

---

How To Log Every Query
I want like to log every query which I run on my database server. So for every insert,update and delete I would like to log. So how can I go about seting up this log.

---

How To Do This Query?
table 1: (ID, name)

1, joe
2, frank
3, mary


table 2 (ID, id_fk, color)

1, 2, green
2, 2, blue
3, 1, red
4, 3, orange
5, 3, gray

now, the result (in CSV format) needs to be:

joe, (red)
frank, (green, blue)
mary, (orange, gray)

of course its a 1 to many (table 2) and the many is where i am getting stump by grouping them.

using mysql 5.0

---

Query Is Off By One
I wrote a complex query that appears to be giving me HALF of what I'm looking for. Essentially, the query is returning rows with 1's in them when I know that they should be zero.

Basically, it's the table which handle a polling script for my site. The overall poll is in the 1st table, the choices are in the 2nd table, and the results are in the 3rd table.

When I run my query:

SELECT tbl_poll_options. * , COUNT( tbl_poll_options.option_ID ) AS option_total
FROM tbl_poll_options
LEFT JOIN tbl_poll_tab
USING ( option_ID )
WHERE tbl_poll_options.option_liveID =1
AND tbl_poll_options.option_pollID =1
GROUP BY (
option_ID
)
ORDER BY option_total DESC
LIMIT 0 , 30
I get the following results:



option_ID | option | option_total
--------------------------------
1 | opt -1 | 3
2 | opt -2 | 1
3 | opt -3 | 1 [* should be zero]
4 | opt -4 | 1
5 | opt -5 | 1 [* should be zero]
6 | opt -6 | 1
I've never been good at GROUP BY and never had a reason to use a LEFT JOIN - until now. Can anyone take a look at my query for me? I've attached the tables and the query in the attached text file.

---

Is This Query Right?
I am selecting each row based on the number of times it occurs in the table...
SELECT * FROM `searches` GROUP BY value ORDER BY value DESC LIMIT 10

It orders it by the highest occuring row.

---

How To Fix This Query
I ahve the following query:

SELECT facility_id, facility_name
ORDER BY facility_name asc
WHERE atm_facilities.facility_resort = null;

It doesn;t seemt o like the null part at the end. Basically I am just trying to return all the results where atm_facilities.facility_resort is blank.

---

New DB Or A Query?
Not sure if I need a new DB for this or if a specific query can do this? I have a DB called pcomments, it stores poem comments. On my page I want to show up to the last 5 commented poems. The DB stores the actual poem_id and the time of the comment. But I don't want to show the last 5 comments, I want to show the last five commented poems. Example, if a new poem gets two comments before another poem, I don't want the poem to be listed twice. Is there a way to do this in a query or should I set up a new DB with the poem_id and a newcom_date that gets overwrote each time a comment is submitted, then just get the 5 newest dates from that DB?

---

Query Help!
I need some help before my head implodes. I am building an application that manages student placements with families. I have a list of beds which can meet certain requirements such as access to broadband or access to a piano. I have created a table that list the beds and met requirements so that bed may appear multiple times in this table.

My problem when it comes to searching for beds that meet certain requirements how do I build the query to return a bed that met the requirements you are looking for, say return all the beds that have access to broadband and a piano.

If I use a WHERE statement such as WHERE requirement = requirement1 AND WHERE requirement = requirement2 it returns nothing and if I use and OR statements it returns beds that only meet one of the requirements.

---

Query
I have a query to search the lowest price of each hotel

PHP

SELECT a.hID,
SUBSTRING( MIN( CONCAT( LPAD( dbl, 8, &#390;' ) , mID, rmID ) ) , 9, 1 ) AS mID, SUBSTRING( MIN( CONCAT( LPAD( dbl, 8, &#390;' ) , mID, rmID ) ) , 10 ) AS rmID, 0.00 + LEFT( MIN( CONCAT( LPAD( dbl, 8, &#390;' ) , mID, rmID ) ) , 8 ) AS price FROM hotels a, hotel_rates r
WHERE country = '".$cn."'
AND cID = '".$city ."'
AND dbl IS NOT NULL
AND a.deleted = 0
AND r.deleted = 0
and r.hID = a.hID
AND stars >= ". $stars."
AND valid_from <= '".$out ."'
AND valid_to >= '".$out ."'
GROUP BY hID ORDER BY airport

What's the meaning of ' SUBSTRING( MIN( CONCAT( LPAD( dbl, 8, &#390;' ) , mID, rmID ) ), 9, 1)'?
I want to change this query to search all the price of each hotel for different room.(mID is meal ID and rmID is room ID) How to change the query above?

---

SQL: Query Help Plz
SELECT c.ID AS 'Course ID',
date_format(c.start_date,'%d/%m/%Y') AS 'Start Date',
date_format(c.end_date,'%d/%m/%Y') AS 'End Date' ,
t.description AS 'Course Description',

FROM media_courses AS c
INNER JOIN media_courses_type AS t
ON t.ID = c.media_courses_type_ID

WHERE c.start_date > NOW()
GROUP BY c.ID
ORDER BY c.start_date ASC

Q1. Briefly explain what the query above would return?

Q2. Re-write the query to retrieve all courses in the past that a name that starts with Cert

Q3. Re-write the query to return all courses that are more than 1 day in length

---

Help With Query
am having a problem selecting three fields from a colum. i hope someone
would help me.

here is it

table

num int
page varchar

SELECT * FROM counter WHERE num = ( SELECT MAX(num) FROM counter ) LIMIT 0, 3

am trying to select 1 or 2 or 3 records were the num is highest

example

page--------num

test1-------20
test2-------22
test3 ------9
test4-------30
test5-------10

am trying to select the highst three but display the pages
like this

test4
test2
test1

---

Sub Query Help
I have a posting script in which I need to do a query to display all categories which have posts in which I have not commented on.

The tables involved are posts, catposts and category. Category and Posts table are linked to each other using the catposts table. Each post can have many categories and each category can have many posts. I store the post comments in a comments table.

SELECT cat_name,post_name,post_id FROM
posts, category, catposts
WHERE
posts.post_id = catposts.post_id and
catposts.cat_id = category.cat_id
and post_id not in
(select comment_post_id from
comments where poster_id='ashjsdhjhdjhsjd')
Though I have so many categories that I need to poll on, I am not getting those categories out of this query.

I can see the polls of those categories
If I do the query

select post_name from posts where post_id not in (select comment_post_id from comments where poster_id='ashjsdhjhdjhsjd')

---