Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MYSQL


SuperbHosting.net have generously sponsored dedicated servers to ensure a reliable and scalable dedicated hosting solution for BigResource.com.





Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?


I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's.

$sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )";
return $sql2;
$res = mysql_query($sql2, $connDB) or die(mysql_error());
$row = mysql_fetch_assoc($res);
based on a test zip code gives a result like


SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 )
the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table.

In my db table lon and lat are decimal(10,6) type with a default value of 0.000000




View Complete Forum Thread with Replies

Related Forum Messages:
Function Doesnt Work
i tried write simple function:

CREATE FUNCTION `getLastNumber`(some_guy INT(50), date DATE) RETURNS float
BEGIN
DECLARE a FLOAT;

SET a = NULL;

SELECT number INTO a FROM numbers p WHERE p.number_owner = some_guy AND p.date_created <= date ORDER BY p.date DESC LIMIT 1;

RETURN a;
END

that would return last inserted number into table for some person. When some_guy has number in table, then is everything ok and it returns last number, but when some_guy does not have row in table it returns: Query OK, -1 rows affected. Pls why? and how to fix it?

View Replies !
GROUP BY Doesnt Work
i have a table that contains the folowing fields:

some_table:

company_number
something_number
price
date_price_established

for a certain company i need to get all the dates that prices were established. and i dont want any date_price_established to repeat themselfs.

for example, for the following database:

1,1,15.5,1.1.2006
1,1,16,1.1.2006
1,1,17.5,1.1.2006
1,1,16,3.1.2006

the results will be : 1.1.2006, 3.1.2006.

this is what i've tried:

SELECT date_price_established
FROM myDb.some_table
WHERE company_number = 1
GROUP BY date_price_established";

View Replies !
Domain Name Now Doesnt Work
I have a software script I had installed on my server that works with a mysql database. It was working fine as far as pulling information from the database under my old domain name, however, I changed to a new domain name as primary on my server and now the database does not pull anything into the template.

I know this is probably simple but not sure how to fix this.

View Replies !
Where Clause Doesnt Work
im creating a website with a mysql database. ive jus got started. im havin a few problems
i have a product table and i execute a simple query but it doesnt seem to give me any results: heres the query:

Select * from product where ProductName = 'Age of Empires III';

the above query doesnt work but this 1 does
Select * from product where ProductName LIKE 'Age%';

View Replies !
Mysqldump Doesnt Work
i try to insert the followind data to mysql database
mysqlimport -u root -p anaktisi2 < phpbb_db_backup.sql

But this doesnt insert anything in the db.

View Replies !
Full Text Searsh Doesnt Work
I am trying to do a Full text search, but I have two problems.

1. If I enter a word that is found under multiple entries, I get no return. But it i search a word tha's only under one enetry, then it works.

2. This query works:
PHP

SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice') AND `approved`=&#391;'

, but when I add WITH QUERY EXPANSION, it doesnt work.

PHP

SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice' WITH QUERY EXPANSION) AND `approved`=&#391;'

I get this error:


MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH QUERY EXPANSION ) AND `approved` = &#391;'
LIMIT 0, 30' at

View Replies !
Work Out Daily Interest Rate Based On 2 Tables
Hi

I have the following DB structure

charges
cID [bigint]
cpID [bigint]
amount [decimal 3,2]
date_charge [datetime]

petitions
pID[bigint]
interestrate [decimal 3,2]
date_claim [datetime]
And need to do the following calculation:

($amount * ($date_claim - $date_charge) * ($interestrate / 36500))
The ($date_claim - $date_charge) section needs to work out the difference in days

Is something like that possible?

View Replies !
Geocoding / Radius
We have a listing of professionals and we're looking to have a user enter a zip code and find all the professionals within a radius of 100 miles of the user's zip code.
We have an existing table containing all the zip codes, counties, and cities in the US.
We also have zip codes for the physical locations of each of the professional listings.
When a user enters a zip code and radius, we need to somehow know which zip codes are within that radius.

View Replies !
Zip Code Radius
I am using the following function from phpclasses.org.

How can I gues this to return also the distance for each zip code?

PHP

function inradius($zip,$radius)
    {

        $query2="SELECT * FROM zips WHERE zip='$zip'";
        $row = $db->get($query2);

            $lat=$row["lat"];
            $lon=$row["lon"];
            $query2="SELECT zipcode FROM zips WHERE (POW((69.1*(lon-"$lon")*cos($lat/57.3)),"2")+POW((69.1*(lat-"$lat")),"2"))<($radius*$radius) ";
            $result = $database->query($query2);
            if($result->count()<>0) {
                while($row = $result->fetch()) {
                    $zips[$i]=$row["zipcode"];
                    $i++;
                }
            }
        }else{
            return array();
        }
     return $zips;
    }

View Replies !
How To Work With Millions Of Rows
I need to handle few millions rows(cca 25M) with about 100 bytes for each row. They will be indexed (4 byte integer) becouse I need fast data retrieval.

Would it be better to store it in multiple tables or all in one table?

What type of table engine should I use and how fast should I expect retrievals to be(with using indexed colum)? About hardware it isn't anything great or slow, just normal desktop PC.

View Replies !
Rows Based On First Letter
I'm trying to figure out the fastest/best way to return rows based on the first letter of the title column in my table. For example, I want to get all the article titles that start with the letter "a".
Is there any difference between the two select statements? Any reason to use one over the other? Memory? Speed?

SELECT title FROM table WHERE LEFT(title, 1) ='a';
or
SELECT title FROM table WHERE title like 'a%';

View Replies !
Filtering The Rows Based On Its Timestamp
I'm trying to write a backup script that takes the last modified rows of a table in x days. I can do it if I have a timestamp column for every table in the database, but my aim is to write a script for general use as anyone can use it with his/her database without the need to add that column.

This will be possible if there a property gives the last modified timestamp of a row.

does anybody has any way to reach that target?

View Replies !
Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The
thing is, in my system, I have a set of users who have a 'quota'
limiting their usage. They can create n number of tables and put m
number of rows in it, but at the end, their 'usage' can not exceed,
lets say, x MB. My problem is, how to I evaluate the space being used,
given the fact that I can pull all data for a user by including a user
ID in the where clause. In other words, I need to find size of the
result set containing all user's rows.

I am using mysql jdbc driver and not sure if there
is any API that deals with the physical size of the resultset.
resultset.getFetchSize() talks in terms of rows v/s the actual space
they take.

View Replies !
Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag:

resource - 1:M - tagged_resource - M:1 - tag

So a resource can have many tags and a tag can belong to many resources. The data would look like this:

RESOURCE TABLE
id: 1
name: resource1

TAG TABLE
id: 1
tag: new
id: 2
tag: tested

TAGGED_RESOURCE
resourceID: 1
tagID: 1
resourceID: 1
tagID: 2

How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE?

I have tried using 'IN' but it acts like an OR operator rather than AND.

View Replies !
Selecting Multiple Rows In The Based Column With AND Condition
I am facing a very big headache for a few days. I have looked through the forum for solution but found no satisfactory answer. Essentially my problem is the same as the one found in another thread, hence I use some parts of that thread to illustrate my point. Assuming the following scenario: ...

View Replies !
Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem:
Simplified tables:
ARCADES
=======
ID,
name
GAMES
======
ID,
name
ARCADES_GAMES
=============
ID,
arcade_iD
Games_ID
Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those.
I can do soemthing like...

SELECT a.name
FROM arcades a
WHERE EXISTS(SELECT 1 FROM arcades_games
WHERE arcade_id=a.id AND game_id IN (11,14))
But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14.

It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?

View Replies !
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 ....

View Replies !
What Query Would Work For This?
I have two tables: say Yes and No.

Yes(ID, YesEmail, EmailOwner);
No(ID, NoEmail);

Both YesEmail and NoEmail contain email addresses.

What query can retrieve all the email addresses in Yes table that are not listed in No table? Thanks in advance.

View Replies !
Cannot Get MySQL Query To Work In .asp
I have a MySQL database and in the front-end software I am using to Run MySQL, I have created a query on the tables which runs in the front-end, similar to the MS Access query grid.

I now wish to pull this query on-line using .asp, but I seem to have found out that I can only pull data from tables in MySQL? ....

View Replies !
Query Wont Work
PHP Code:

if(mysql_query("UPDATE users SET referer_score=referer_score+1 WHERE userID='$frommysql_referer'"))
{
               die(mysql_affected_rows());

im trying to figure out why refer_score isnt being iincremented as im expecting it to. i know that the value of $frommysql_referer is the right value that im expecting it to be. just it wont increment referer_score. and mysql_affected_rows() wont print anythnig to the screen

View Replies !
Query Doesn't Work With Mysql4
Can someone tell me what's wrong with the mysql query below? It does work on mysql 5 but gives me an error with mysql 4.

SELECT rev_profile_indiv. *
FROM rev_profile_indiv, rev_profile_kids_indiv
WHERE 1
AND reviewing
IN ( 1, 0 )
AND active
IN ( 1 )
AND contract
IN ( 1 )
AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) >= UNIX_TIMESTAMP( &#55614;&#57156;-12-15' )
AND UNIX_TIMESTAMP( DATE( rev_profile_kids_indiv.birthdate ) ) <= UNIX_TIMESTAMP( &#55614;&#57158;-12-15' )
GROUP BY rev_profile_indiv.username
ORDER BY lastname
LIMIT 0 , 30;

View Replies !
What Do I Need To Do To Make This Query Work?
I must've been working too long hours this week, as my flow of ideas has come to a complete stop.

Its really quite simple: 2 tables - bookings , invoices

These two counts gives me precisely what I want

PHP

$unpaid_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings    WHERE invoice_id = 0 AND client_id = $id AND status != &#390;' AND finish <= '$time'"),0);$total_bookings = mysql_result(mysql_query("SELECT COUNT(id) FROM bookings    WHERE client_id = $id AND status != &#390;' AND finish <= '$time'"),0);


so I present this in a ratio, $unpaid_bookings / $total_bookings... awesome

now, when I try and flip the coin and show unpaid invoices over total invoices, it all falls apart once I have more than 1 booking per invoice.

First i'll explain the table structure. Each booking has a column `invoice_id`, which is assigned to the primary `id` of the invoice table. So one invoice might be associated with x bookings.

PHP

$unpaid_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b     WHERE i.id = b.invoice_id AND b.client_id = $id AND i.is_paid = &#390;'"),0);$total_invoices = @mysql_result(mysql_query("SELECT COUNT(i.id) FROM invoices i, bookings b     WHERE i.id = b.invoice_id AND b.client_id = $id"),0);


Again so it works fine when 1 invoice is associated to 1 booking, but when 1 invoice is associated with say 2 bookings, the invoice count will return two instead of one. The worst part is, I know exactly why (because there are two bookings for the invoice), I just can't get around it!

I've tried all sorts of joins, group by's, and i'm not having any luck. I'm trying not to be weak and add the client_id into the invoice table,

View Replies !
Query Does Not Work On Some Server Instances
query:

Select * From Students Where C_Name = "XXXXXX"

WHen I run this query on 2 of my computers, the result returns no rows.

When I run this query on a remote machine hosted by 1and1, with a copy of the same database, the query returns the correct row.

The syntax is identical.....

View Replies !
Cant Work Out Correct Select Query For ..
mysql> select * from parent_cat;
+-------+----------+----------+
| catid | pname | psection |
+-------+----------+----------+
| 1 | Trainers | Men |
| 2 | Bags | Woman |
| 4 | Boots | Woman |
| 5 | Fragance | Men |
+-------+----------+----------+
4 rows in set (0.00 sec)

mysql> select * from categories;;
+-------+---------------------------+---------+------+
| catid | catname | section | pid |
+-------+---------------------------+---------+------+
| 1 | Nile | Men | 1 |
| 2 | Bags | Woman | 2 |
| 4 | Boots | Woman | 4 |
| 6 | Air Force I & II Trainers | Men | 1 |
| 7 | Adidas Trainers | Men | 1 |
| 8 | Timberland Trainers | Men | 1 |
| 9 | Lacoste Trainers | Men | 1 |
| 10 | Gucci & Hogen Trainers | Men | 1 |
| 11 | D&G Trainers | Men | 1 |
| 12 | Prada Trainers | Men | 1 |
| 13 | Chanel Trainers | Men | 1 |
+-------+---------------------------+---------+------+

and the following query:

$pid = $_GET['catid'];

$query = "SELECT categories.catid, categories.catname, categories.pid
FROM categories, parent_cat
WHERE categories.section = 'Men'
AND parent_cat.catid=categories.pid
AND categories.pid = '$pid'";

At the moment the query only returns all categories.catname when my categories.catid = 1, but returns nothing if it equals something else?

View Replies !
Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....

View Replies !
Degrading Sub Select Query To Work In MySQL 4
I have the following query working as intended in MySQL 5. However, I had not realised that the target server is running 4.0.2 and it's not possible to upgrade.

SELECT
records.player_id,
records.record_id,
records.game_id,
records.updated,
game_modes.mode_title,
records.record_time,
players.firstname,
players.lastname,
players.nickname,
players.country,
teams.team_title,
classes.class_title,
tracks.track_title,
games.game_title,
games.version,
record_types.type_title
FROM games, records, classes, teams, tracks, record_types, players, game_modes
WHERE
records.record_time = (
SELECT MIN(record_time)
FROM records
WHERE records.player_id = players.player_id
AND records.game_id = 1
AND records.mode_id = 1
AND records.track_id = 1
AND records.class_id = 5
AND records.type_id = 1
GROUP BY records.player_id)
AND

records.track_id=tracks.track_id
AND record...................

View Replies !
Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows

SELECT * FROM news WHERE ((news.date)>$today ORDER BY date

where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.

View Replies !
Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' with a field representing the topic author's username. I then have another table called 'ignorelist' which has two fields; one is an account number and the other a reference to the aforementioned author. There can be many ignored authors for any one account.

For any given account number, I'd like all the rows from the forumtopics table where there is no match for (account, author) held in the ignorelist table. Obviously it's easy to check for existence but can the opposite be done?

I run MySQL 4.0.17.

View Replies !
Sub Query - Aggregate Fields Based On Min N Max
In the Users table below there are duplicate users by email address
+---------------------+------------------------------+-----------------+-----------+
| ts | email | field1 | field2 |
+---------------------+------------------------------+-----------------+-----------+
| 2009-01-31 06:51:14 | user1@rediffmail.com | 05 | 03
| 2009-01-31 16:07:39 | user2@yahoo.com | 02 | 02
| 2009-01-31 16:15:02 | user2@yahoo.com | 09 | 04
| 2009-01-31 16:16:00 | user2@yahoo.com | 06 | 08
| 2009-01-31 16:19:52 | user2@yahoo.com | 01 | 09
| 2009-01-31 02:04:36 | user3@rediffmail.com | null | 01
| 2009-01-31 02:12:34 | user3@rediffmail.com | 01 | 03
| 2009-01-31 02:20:31 | user3@rediffmail.com | 08 | null
+---------------------+-----------------------------+--------------+-----------+

I want to fetch one record per user ‘user1,field1,field2’
For user 1
select field1 where min(ts)
select field2 where max(ts)

the final output should be
user1,05,03
user2, 02, 09
user3, 01,03 (max of ‘field2’ is null so it should pick the field value which matches the next min ‘ts’ val)

View Replies !
Query Based On Data Of 3 Tables
this is what I've been trying to implement:

I have, say, three tables.

One is a user table, with id, username, etc
Second is a question table with Question ID, Question, Category etc
And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns.

What I want to do is this:
Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.

View Replies !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query?

Example result for ProductX:

View Replies !
WHERE Doesnt Equal
I am trying to select something my DB where a clause does not equal something but for some reason I cant seem to make it work.

Here is what I have tried.

PHP Code:

SELECT * FROM golf_groups WHERE name!="something" 


but this doesnt seem to work. i had done something like this before and thought that I had done it this way, but since this does not work,

View Replies !
It Doesnt Use My Index!
`player_1` mediumint(7) unsigned NOT NULL default &#390;'
`player_2` mediumint(7) unsigned NOT NULL default &#390;'
reffering to users' id-number in another table.

I have two indexes:

KEY `one` (`player_1`),
KEY `two` (`player_2`)
The cardinality of them is 6000 and 7000. My primary-key has cardinality 150 000.

When i run this query:

EXPLAIN SELECT *
FROM `gamebase`
WHERE player_1 =1
It uses the "one"-key (doh!).

But now, that I run my next query (using OR), it tells me that one and two are possible keys, but it uses neither, but loops through my entire table "Using where".

EXPLAIN SELECT *
FROM `gamebase`
WHERE player_1 =1
OR player_2 =1

View Replies !
Aggregated Variable Only Work With Transaction In Query Browser?
I want to select rows and use user variable to generate an incremental id for the result. For example, I want the following query

select @id:=(@id+1) as id from file_list limit 5

to return 5 rows with values 1 to 5. However, the result is ....

View Replies !
Reformatting Email Address To Work On SELECT Query
This is probably basic, but so far I cant find any articles about this.

I want to "SELECT * FROM dbase WHERE emailaddress = $email;"

$email is a feed from PHP, and will contain a string like "some.one@someplace.com"

Apparently with $email as a normal string, it gives an error saying:
"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '@someplace.com)' at line 1"

I reckon it's the dots and the @s that terminate the syntax and making it not work.

How can i get out of this problem? Is there any function I can call to solve this? Or probably a little bit of more programming?

View Replies !
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.

View Replies !
How To Query Multiple Tables Based On Value Difference
I 5 tables all with equal columns,but with different values. Per example:

time_start,time_end,num_a,num_b,price etc.

How can I retrieve the values of all columns `price` between tableX and tableY where time_start in table Y is higher as a datetime than time_start in X.
So,the query is about finding the values of `price` between 2 dates in differnt tables.

View Replies !
Howto Make A Query Based On Another Result?
I need to make query from another query result.

tbl_A
fields: ID and Date

tbl_B
fields: ID and FileName

Code:
Select ID FROM tbl_A where Date>=CURDATE()
this 1st_query_result will be any ID with current date from tbl_A.
Then I need this 1st_query_result to query the filename which store at tbl_B.

something like this:

Code:
select FileName from tbl_B where ID = 1st_query_result

View Replies !
Its Returning A Value That Doesnt Exist
Okay here is the deal. I am running a query to find out if there is an ID in my table that has a case number that matches the one being submitted. If there isnt once its should step into an if statement that would submit a number of records. Otherwise it should jump to the else statement.

I the problem comes from: the query keeps returning "Record id#2" but there is no record matching the case number being submited from the form.

if( isset($_POST['Submit'])){ //Begin If statement logic
$resultcheck = mysql_query("SELECT * FROM submissions WHERE wfmcase = " . $_POST['wfmc']) or die(mysql_error()); //Query string to test if wfmcase was already submitted
echo "Sucess, form varibles have been passed.<br />";
echo "Resultcheck query results: $resultcheck <br />";

if ($resultcheck == ""){ //Checks to see if the query was empty.
echo "There are no duplicate case escalations pending. <br />";

I added the echo statement so that i could see how far it was getting through the script. It dies after printing the varible $resultcheck which gives "Record id#2". It never enters the 2nd if statement because for what ever reason the query has the "record #id2" in it... What is causing this? I have verfied that there is no case that matches the one coming from the form and i get the same error.

View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables:

default_categories
column 1: category_id
column 2: category_name
column 3: category_parent

custom_categories
column 1: custom_cat_id
column 2: custom_cat_name
column 3: custom_cat_parent

The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table.

So if the default category has 3 rows with IDs | names:
123 | Dogs
456 | Cats
789 | Fish

And the custom category has 1 row with IDs | names:
456 | Very Cute Cats

I want my query of these 2 tables to produce the following IDs | names:
123 | Dogs
456 | Very Cute Cats
789 | Fish

I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id.


MySQL
SELECT *
        FROM default_categories
        LEFT JOIN custom_categories ON category_id = custom_cat_id
        WHERE category_parent = ''
        AND custom_cat_parent = ''
        ORDER BY $order_by $sort




View Replies !
Time Based Reservation System - Only 1 Query Should Succeed
a user can select a time they want and submit the page

* The code selects all the bookings for a range of time.
* It then counts how many concurrent bookings there are for each hour.
* If there's less than the total (4) it inserts another row ( the user's requested booking ) into the booking table.

We also notify people if there has been a cancellation

This has led to a situation where a few different people are trying to book the same session at the same time, and succeeding!
We've ended up with 5 sessions booked

In the course of 2 page requests this seems to happen:
User1 - submits their booking
site selects to see if there's availability ( there is!)
User2 - submits their booking
site selects to see if there's availability ( there still is!)
site inserts User1's booking
site inserts User2's booking

View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2?

TABLE 1:
+---+------+
| i | name |
+---+------+
| 1 | item1 |
| 1 | item2 |
| 7 | item3 | <-- delete all rows with i=5,6,7
| 6 | item4 | <-- delete
| 5 | item5 | <-- delete
| 5 | item6 | <-- delete
| 7 | item7 | <-- delete
+---+------+

TABLE 2:
+---+---+------+
| j | i | name |
+---+---+------+
| 1 | 1 | item1 |
| 1 | 3 | item2 |
| 1 | 2 | item3 |
| 2 | 5 | item4 | <---- j=2 => i=5
| 2 | 6 | item5 | <---- j=2 => i=6
| 2 | 7 | item6 | <---- j=2 => i=7
| 3 | 8 | item7 |
+---+---+------+

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

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

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

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

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

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

I need a query that will update ALL rows.

View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it.

It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time.

I've simplified what I'm doing above, but it is detailed below.....

View Replies !
Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

View Replies !
ORDER Based On One Field But LIMIT Based On Another?
Say I have a table with students and their grades, and I want to get the students with the top 10 grades, but the result to be sorted based on their name. How would I do that?

View Replies !
What Is Execution Time Of A Query Based On? (was "a Mysql Question")
when selecting data from the database, does the time taken to retrieve it vary from 56k connections to T3 connections? or does it all depends ont he general server speed/amount of connections on the db?

im not sure if ive explained that in the best way for people to understand
but im sure someone will get what i mean

View Replies !
Last N Rows Of The Query
SELECT expensive query ORDER BY field ASC;
It generates somewhere around 2.9m rows. I want the last 10:

SELECT expensive query ORDER BY field DESC LIMIT 10

But I want them the other way around. Sure, I can do that programatically, but for "application reasons" I want that done in the query, so what I want to do is along the grounds of

SELECT expensive query ORDER BY field LIMIT 10 OFFSET rows()-10
We're using MySQL 4.0. Is there a way to achieve the above without using a temporary table?

View Replies !
Several Rows From One Query
I've this sql-query...

$sql = "INSERT INTO database (some_kind_of_id, names) VALUES $xxx, $_POST['xxx2']";

The thing is that my post xxx2 are several values that I want inserted on several rows with the values xxx in front...

View Replies !
Eliminate Rows In A Query (without Many OR)
This is my query :

SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas

There must be a more efficeint way to find what i want than this :

AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598

Something like cas.id is not (list of values)...

View Replies !

Copyright © 2005-08 www.BigResource.com, All rights reserved