MYSQL :: Simple Sql Question: Using A Query Result As A Query Variable

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

MySQL Variable Query
SET @tcslot = "my_column";
SELECT @tcslot FROM my_table WHERE id = 1;
The above code returns:

Quote:

@tcslot
my_column

It literally returns the value of the variable I set.

Is there any way to get it to parse the variable and then select the column contained in it? It's been driving me nuts for 3 days,

Can I Use A Variable In A Mysql Query?
this works:

$sql = 'UPDATE `personal` SET `interval` = '24' WHERE `ID` = 66 LIMIT 1';

but this does not (I get a parse error):

$variable=24; //php
$sql = 'UPDATE `personal` SET `interval` = $variable WHERE `ID` = 66 LIMIT 1';

my question is: why would this not work? Am I not allowed to use a variable in my query? Perhaps my syntax is wrong.

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?

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

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

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

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

What is the correct way to make this query?

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

SQL Query

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

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

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

PHP

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

But the following returns zero rows

PHP

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

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

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

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

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

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

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

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

Here is my

PHP

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

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

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

} else {

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

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

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

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

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

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

Select modelnumber from tbl_product where modelnumber is not distinct

OR

Select NOT Distinct modelnumber from tbl_product.

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

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

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

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

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

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

SELECT prevleaving1
FROM `jobemployment`
WHERE prevleaving1 IS NOT null

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

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

Passing Variable Into A Table Query
Newbie here again. I'm using MySQL, version 4.1.21-standard and php. This is probably one of those "boy this guy is an idiot" type questions, but I have to ask it anyway

My db has several tables of yearly statistics. The user has a drop-down menu with choices for which year to view data. This choice produces a variable labeled $year.

The tables are labeled like so:

2006_results
2005_results
2004_results
etc.

and fields such as

2006_results.winnings
2006_results.status
2006_results.sponsor

Can a query take the previous variable and use it to request the specific table, such as:

If $year = 2006, can the query structure uses $year_results.winnings to get 2006_results.winnings?

Oh, I almost forgot. If I force the query I'm using for a hardcoded year, such as 2005_results.winnings instead of using $year, the query works. I just can't get it to use the variable.

Trying To 'parse' A Variable Within A MySQL Query
I'm trying to do an INSERT query via a PHP script. Basically, I want to use a variable within the WHERE clause, but i'm not having success.

PHP

$SQL = "INSERT INTO users (uPassword)
VALUES ('$newp2')
WHERE (uName='$userid')";

The above results in the following error:

"check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (uName='test')' at line 3"(where 'test' is the variable stored in $userid).

1. Is it possible? If so how? What is the syntax?

2. If not, any other way I can constrain a WHERE clause by using a variable?

Assigning Query Results To A Variable
I would like to Execute this Select Statement...

SELECT MAX(VisitorID) FROM Visitors.

Is there any way to store the result directly into a VB variable??

lngVisitorID = ???

Passing Variable Into Mysql Query
I can't seem to get a value to pass from a url into a mysql query...

Here is my code:
The url is...page.php?record=$row[recid]

$record = $_GET['record'];

$query = "UPDATE databaseName SET data='$data' WHERE recid = $record ";
$result = @mysql_query ($query);

When I print $record I get the resulting number from the recid but when I put it into the query I get nothing. When I print $query I get nothing for $record. I'm very confused...

Assigning Query Results To A Variable
I would like to Execute this Select Statement...

SELECT MAX(VisitorID) FROM Visitors.

Is there any way to store the result directly into a VB variable?

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?

First Query Result Used For Second?
Is this possible / Practical?

Function1
I have a form that takes a users single field input and queries the database returning two values as result of what was entered, valueA and valueB. It then calls function2 passing it valueA and valueB.

Function2
takes valueA & valueB and queries the database again using these values within the query.

Im still learning mysql so the code is a real mess and not working.

Could someone give a simplified example of how this could / should be done?

Can I Create Temp Variable In Mysql Query?
I have a mysql query like this: SELECT SUM(num1), SUM(num2), SUM(num1)/SUM(num2) FROM mytable

I want to reuse the value from SUM() by assigning to a temp variable, so I don't need to redo it again later.

User Defined Variable & MySQL Query
I am trying to use input from an HTML form, 'post' this numeric value and (using PHP) query it against a mySQL database. This all works if I 'manually' enter the value (eg .'100') into the code/script but I cannot amend the value via an HTML 'edit' <INPUT> box.

Please find to follow my code:

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.