MYSQL :: Slow Query W/ Join & Ordering

Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query?

The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

$query = "select distinct materials.* from materials";
$query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

There's 914 rows in the materials table and 1348 row in the materials_products table

Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

Ordering By The Product Of A Left Join
I have a query which performs exceptionally poorly. I've isolated the problem as being the ORDER BY clause...

SELECT
table_a.item_id,
IFNULL(table_b.priority, 0) AS priority
FROM table_a
LEFT JOIN table_b
ON table_a.item_id=table_b.item_id
WHERE
<<..various conditions...>>
ORDER BY priority DESC

('priority' is a signed integer, and probably 1% of rows in table_a have a matching row in table_b, in the other 99% of cases I default it to 0 using that IFNULL).

So you see, what I'm doing there is ordering by a column that, for the most part, doesn't really exist, making indexing kind of tricky.

And so it's very, very slow to order by that 'virtual' column.

Join Too Slow
I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?

Slow Left Join
I've never come across this before, a very straight ahead query running extremely slowly...

Table1
Id | Field1 | Field2

Table2
Id | Table1Id | Field1 | Field2

Query....

SQL
SELECT Table1. * FROM Table1 LEFT JOIN Table2 ON Table2.Table1Id = Table1.IdWHERE Table2.Table1Id IS NULL

NOTE: An existing record in Table2.Table1Id is never null, it will always have a record of some description. We're just looking for missing records in Table2.

This query is taking anywhere from 6-20 seconds to run. It's really got me baffled as I thought it should run extremely quickly.

There's about 10,000 records in each table and the query appears to be producing the correct result, just extremely slowly.

Slow Left Join
Well I'm trying to do a left join on a couple tables "USERS" and "EVENT_LOG", event_log has a considerable number of rows (1.7mil). I'm trying to do a query to find out which USERS have no entries in the EVENT_LOG of a specific EVENT_TYPE.

Here's the query I'm attempting:

select USERS.USER_USERNAME, 0 as eventCount from USERS left join event_log on USERS.USER_ID=event_log.USER_ID and EVENT_TYPE='AUTHENTICATION' where event_log.USER_ID is null and USERS.CUSTOMER_ID=33700077 group by USERS.USER_ID

EXPLAIN output:
SIMPLE USERS ref CUSTOMER_ID CUSTOMER_ID 4 const 155 Using where; Using temporary; Using filesort
SIMPLE event_log ref USER_ID USER_ID 32 const 12139 Using where; Using index; Not exists

This takes 1.5sec, which is quite long considering the right join takes 31ms which is doing alot more (i.e. counting).

Right Join:

select USERS.USER_USERNAME, count(event_type) as eventCount from USERS right join event_log on USERS.USER_ID=event_log.USER_ID and EVENT_TYPE='AUTHENTICATION' where USERS.CUSTOMER_ID=33700077 group by USERS.USER_ID

I have indexes on USER_ID in the USERS table, and USER_ID/EVENT_TYPE ("USER_ID" index) in the EVENT_LOG table.

So anyone have an idea as to why this is so slow? Shouldn't it be querying the "USER_ID" index in the EVENT_LOG table and simply requesting the event type "AUTHENTICATION" and adding it to the resultset if it comes back null?

Slow Join On Large Tables
I have two tables:
D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:
SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer
settings.
Adding the following buffer settings only slightly decrerased the time
(~3:00).
key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

Ordering Query
i have a query were i need to select the top 250 by a number but i want the result to be ordered alphabetically by a different column. i can't figure out how to do this because i need to order by the numerical value to select the top 250. for example:
Code:

SELECT the_tag, count(distinct(article_id)) as cnum FROM tags GROUP BY the_tag ORDER BY cnum desc limit 250

in the above example i get the top 250 but they are order by cnum for the purpose of selecting the top 250. how can i modify this query to still select the top 250 and also have the results ordered alphabetically by 'the_tag'?

Stop Query Ordering
select ... FROM ... WHERE ... IN ('2', '1', '7', '3', '17')

I have a query in the same format as above, where I want to select only certain items from from a table based on there ID. The problem I have is that MySQL then orders the results based on this ID (I presume this is because it is the index).

What I want to happen is the results to be returned in the same order as the query... is this possible?

Quesion On Ordering In A GROUPED Query
Got a question about grouping and ordering. What I am trying to do is:

a) Group by a criteria by pick which result I am getting as part of that group
b) Order the list of groups.

Lets say I have a list of emails in the form:

| id | timestamp | author | subject

Now, I am trying to generate a list of the latest email subjects per author, ordered by timestamp.

So I want to do:

SELECT * FROM table GROUP BY author ORDER BY timestamp

The problem with that, is it dosen't allow me to choose which result I am getting as part of the group, so how do I do that?

How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)
I used subquerys because they made more sense to me, until the table got "a lot" of data in it (not really... just 1000 entries) - then all querys including subquerys slowed down to 4-5 secs EACH!! :) This is insanely slow.

What I am doing here below is looking into what messages a user has already read in the subquery, so that none of the ones he already has read will EVER again show up for him.

So, how do I rewrite this subquery:

NOT IN (SELECT reffen FROM $readt WHERE sender = $nr)

From this entire SELECT:

SELECT halfref, brokensms, DATE_FORMAT(arrived, '%y'), DATE_FORMAT(arrived, '%m'), DATE_FORMAT(arrived, '%d'), DATE_FORMAT(arrived, '%H'), DATE_FORMAT(arrived, '%i'), priv FROM $halft WHERE sender = $nr and halfref NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) order by id desc limit 1

Into a faster join of some sort?

I'd aprichiate if you told me what each part of the rewrite actually does, because I been reading about joins for a day now and still don't get them at all!

Slow Query
i have this query on a website/webapp that has expanded beyond all expectation. It now takes nearly 30secs to return results from the database

SELECT cl_t.Client_ID, Buyer_1_Title, Buyer_1_Prename,
Buyer_1_Surname, Tel_No, Mob_No, Buyer_2_Title,
Buyer_2_Prename, Buyer_2_Surname, Email_Add,
Price_Max, MAX(activity_t.Date) AS lastcomm
FROM cl_t
INNER JOIN cl_want_t
ON cl_t.Client_ID = cl_want_t.Client_ID
AND Agency_Code ='$agencyloggedincode'
AND Deleted = 'N'
LEFT JOIN activity_t
ON Buy_Sell = 'B'
AND Ref_No = cl_t.Client_ID
WHERE cl_t.Sales_Agent_ID = $agentid
GROUP BY cl_t.Client_ID
ORDER BY $order
The problem is the call to MAX(activity_t.Date) AS lastcomm

activity_t holds all known contact with all known clients and as such is a very large table, the call to search through all of these records and return only the date of the last entry for this client is taking the time. If I remove this from the query I get results in 3 seconds.

I have indexing on activity_t.Date & activity_t.Ref_No

Question, is there a way of doing this quicker within the table I already have, or should I create another table that just holds the last update date for each client, and get the date from this much smaller table.

Why Is This Query Too Slow?
I find this query to be exceptionally slow(around 2.5 seconds), could some tell me why this is so?

MySQL
SELECT st.profile_views,count( DISTINCT p.ID ) news_submitted, count( DISTINCT pv.ID ) news_voted, count( DISTINCT pcom.ID ) news_commented, u.joined, u.weight FROM users u LEFT JOIN posts p ON p.submitted_user_id = u.user_id LEFT JOIN post_votes pv ON pv.user_id = u.user_id LEFT JOIN post_comments pcom ON pcom.user_id = u.user_id LEFT JOIN stats st ON st.user_id=u.user_id WHERE u.user_id='john' GROUP BY u.user_id
I traced the cause to this line
count( DISTINCT p.ID ) news_submitted (from LEFT JOIN posts p ON p.submitted_user_id=u.user_id)
But when i execute something like this

MySQL
SELECT count( DISTINCT p.ID ) news_submitted FROM posts WHERE submitted_user_id='john'
it is quite fast (around 0.03 seconds)
So why does it slow down when i'm joining the above query with 3 other tables ?
Should i use INTEGER for user_id instead of string like 'john'?

Slow Query Log
my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?

Slow Query Using NOT IN
I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....

Slow Query
I have a query that is running really slow !!!!
I have joined on Key fields and indexed the tables fully but it is still solw.
--------------------------------------------

select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual
from details d
join poten a
on a.id = d.id
left join recieved p
on d.id = p.id
left join users u
on a.signed = u.userid
where d.paid > '01-Dec-2005'
and d.authorrceived is not null
and d.authorrefused is null
and ((d.payment starting 'E' or
d.payment starting 'e') or
(d.payment starting 'Q' or
d.payment starting 'q' and
p.target = '500'))
order by d.paid, a.signed, d.id

Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows.
My table is getting kinda slow here is the query i am using
UPDATE nametable SET
sectionname = replace(sectionname,'".$oldsec."','".$sec."'),
categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=Ƈ'
where sectionname='".$oldsec."'
and categoryname='".$oldcat."' ;

I am wondering if the same thing is possible with an insert... on duplicate key statement?

I cant seem to get the insert statement to work, but not even sure whether it is appropriate.

With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)

Slow Query Log Files
I have edited my.ini file to create a file called slow queries. My problem is that when the server starts up i do not get a full list of all slow queries. Is there anything else i need to change? How else can i come about in getting all queries that took a long time to execute? im using mySQL 5.0.9.

Slow Query Log Not Staying On
I'm running MySQL 4.0.16 on Windows 2003. I just added the mysqld-nt
command line option to enable the slow query log, started MySQL, and the
option showed up as turned on. Then later I restarted the server, and the
slow query log option went back to being turned off. Is this a Windows
problem in not remembering the service parameter? Has anyone else seen
this?

View Slow Query Log
I searched here, google and MySQL docs but did not find an answer.
I'm using MySQL-Front from a Windows platform to administer a remote database. The remote server doesn't have phpMyAdmin or anything like that.
MySQL-Front reports 133 Slow Queries and an average of 15 queries per second. But I dont' know how get more information than that.

Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out.
The query I'm running is too slow...can anyone tell me what I can do to
speed it up..if I can at all? I was wondering if because ZipListMatrix has
3.3 million rows that 8 seconds is all the faster it's going to be. Any
help is greatly appreciated! I have already "optimized" the tables.

Can't Turn On Slow Query Logging
long_query_time = 1
log-slow-queries = /var/lib/mysql/slow_queries.log
Is the above syntax not correct for enabling slow query logging? All examples I've seen have the dashes in the second variable and underscores in the first.

When I restart MySQL with those lines in my.cnf, it fails to start, but writes nothing to its error log.
/var/lib/mysql/slow_queries.log exists, is owned by mysql, and has read/write permission.

Turning On Slow Query Logging?
Background: I paid a young admin set me up on a database server. He installed the basic I needed for the server...at my request...No Cpanel...mysql and apache and some tight security w/o even a domain name to SSH into. Unfortunately, he's a busy kid, and teens sometimes don't realize that people depend on them...and well, I can't really seem to get him to do much so I gave up and figured it's a good way to force me to learn all this myself...
Well anyway, now I want to turn on Slow Query logging. But before I do that, I need to know how MySQL is running. Is SQLogging turned on already? Where is it logging to? So first thing I want to look up is, when the server is rebooted, what's the command to restart mysql? No clue. How do I change the setting? And of course, the server is production, so when I make the change, it needs to be quick, it needs to be smooth, and I need to be able to roll back to the previous config if necessary.
I'm running Redhat Enterprise.

Help Rewriting A Slow Phpbb Query
I have a "glance" or "Recent Topics" list on my forums that have become fairly complex. I modified an already feature rich glance mod to allow users to select individual forums to exclude from showing topics in the list. As well when users are members of certain forum groups, they see topics from the group forum in the list, and they are highlighted a different colour.

The main SQL query to create the list often is showing up in the MySQL Slow_query log and I'm pretty sure is the main cause for the page loading slow.

I am no mysql Guru, so I thought I would seek the advice of some to improve or totaly rewrite this slow query.

PHP

$sql = "SELECT
    f.forum_id, f.forum_name, t.topic_title, t.topic_id, t.topic_last_post_id, t.topic_poster, t.topic_views, t.topic_replies, t.topic_type,
    p2.post_time, p2.poster_id,
    u.username as last_username,
    u2.username as author_username
FROM "
    . FORUMS_TABLE . " f, "
    . POSTS_TABLE . " p, "
    . TOPICS_TABLE . " t, "
    . POSTS_TABLE . " p2, "
    . USERS_TABLE . " u, "
    . USERS_TABLE . " u2
WHERE
    f.forum_id NOT IN (" . $forumsignore . $glance_recent_ignore . ")
    AND t.forum_id = f.forum_id
    AND p.post_id = t.topic_first_post_id
    AND p2.post_id = t.topic_last_post_id
    AND t.topic_moved_id = 0
    AND p2.poster_id = u.user_id
    AND t.topic_poster = u2.user_id
ORDER BY t.topic_last_post_id DESC";
$sql .= ($glance_recent_offset) ? " LIMIT " . $glance_recent_offset . ", " . $glance_num_recent : " LIMIT " . $glance_num_recent;

The "NOT IN" list varies per user, but here is an example:
NOT IN (77,75,76,25,26,37,63,64,66,67,67,1,25,26,37,70,28,75,76,78)

Mysql 5.0 - Using My.cnf - Unix / Slow-query-log
I installed mysql 5.0 and need to set up slow-query-log and other logging options.

Here is what I did. But I dont see it working yet.

1. cd /var/db/mysql
2. chown mysql slowquery.log
3. touch /usr/local/etc/my.cnf
4. chown mysql /usr/local/etc/my.cnf

vi my.cnf

[mysqld_safe]
-u mysql
--log-slow-queries=/var/db/mysql/slowquery.log

so now when I type:

mysqladmin shutdown
and than

mysqld_safe &
my sql restarts but the log files are not being used.
also - how do I know if my my.cnf is being used at all?

Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team.

The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem.

The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit.

Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?

Slow Inefficent Query (FIXED)
I have this query which pulls members from a table of 1200 members details, filtering out those without and email address and those that don't want Newsletters, and also any whose email address exists in a second table (emails that have hard bounced previously)

My first attempt took an age and returned about 10,000 members, clearly loads of repeated rows

My 'final' attempt with a DISTINCT shoved in works perfectly but is really slow, PHPAdmin says it takes 1.1s but on the website the data doesn't appear for over 30s, all the other sundry queries on the same table work nice and quick on the same website using the same php to generate the table of results

The googling I've done suggests (to me) that there's nothing wrong with the query but I suspect that there's far too much checking of fields going on from the pre DISTINCT query's results ....

VER VERY VERY Slow MySQL Query HELP URGENTLY NEEDED
I have the following MySQL query, but it is VERY VERY slow and seems to be crashing the server. There are 300,000+ records in the 'tracker' table.

SELECT sites.*, SUM(if(tracker.type='view',1,0)) AS numberOfViews, SUM(if(tracker.type='click',1,0)) AS numberOfClicks, SUM(tracker.revenue) AS totalRevenue FROM sites LEFT JOIN tracker ON tracker.site_id = sites.id GROUP BY sites.id ORDER BY sites.domain_name

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

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

Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do:

There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns.

The most common query is SELECT data FROM table WHERE n1 = ?

The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column.

Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset:

SELECT COUNT(1) AS total FROM table WHERE n1 = 3;
index = random number from 1 to total
SELECT data FROM table WHERE n1 = 3 LIMIT index,1;

How can I speed this up? I need it to take less than half a second if possible. Thank you.

Using 'if' In A 'join Query' ?
I have two tables items and food_names in mysql db

items ->structure
****************************************
id food_items

1 veg

2 non-veg

3 veg & non-veg
****************************************

food-names ->structure

****************************************

id items_id foods

1 1 vbvcb

2 2 cvbvbv

3 3 gfdgdfgd

4 3 bbvcbvcb

*******************************************

if i choose veg if(id=1) I want to write a db query for both veg and veg & non-veg else if i choose non- veg I want to write a db query for both non-veg and veg & non-veg else if i choose 'veg & non-veg' then
i have to dispaly all.

Query: Inner Join Bug
What is wrong with this query? I cant get it to match the "company" field and it is throwing an error... this query works fine if i do "MATCH(date_year, market1, market2, market3, market4, market5, market6)" , but if I put "MATCH(company)" it breaks.... what am I missing here?

PHP

$query = "SELECT u.id
     , u.username
     , r.id
     , r.company
     , r.description
     , r.market1
     , r.market2
     , r.market3
     , r.market4
     , r.market5
     , r.market6
     , r.location
     , r.date_year
     , r.date_month
     , r.source
     , r.video
     , r.audio
     , r.pp
     , r.execsum
     , r.report_url
     , r.exec_url
  FROM user as u
INNER
  JOIN user_reports as p
    ON p.user_id = u.username
INNER
  JOIN emt_report as r
    ON r.id = p.report_id
WHERE username = '$username' AND MATCH(company) AGAINST ('$P_search' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC";

Here is my database schema:

=================
user_reports
=================
user_id
report_id

=================
user
=================
id
username

=================
emt_report
=================
id
company
description
market1
market2
market3
market4
market5
market6
location
date_year
date_month
source
video
audio
pp
execsum
report_url
exec_url

Using Both JOIN And AVG In One Query
I'm looking to use both JOIN and AVG in the same query but not sure how I'm going to it. I'm VERY new to using MySQL.

The ultimate goal is to take one table which counts votes for various criteria in a poll. I want to average those results together. Then I want to join that averaged table with another table and GROUP By URL (which is the column common to both tables).

I've successfully used AVG with the first table and used JOIN (with a totally different) table, I'm not sure how to approach using both together.

Help With A Query Join?
I have a query whereby I look in two tables a teams table and results table to output some data for some football scores.

SELECT th.team_name AS home_team, ta.team_name AS away_team
FROM results r
INNER JOIN teams th ON r.team_one_id = th.team_id
INNER JOIN teams ta ON r.team_two_id = ta.team_id
Now I want to add a third join there on another table named reports to see if the match_date in the results table matches a match_date in the reports table

SELECT th.team_name AS home_team, ta.team_name AS away_team, re.match_date
FROM results r , reports re
INNER JOIN teams th ON r.team_one_id = th.team_id
INNER JOIN teams ta ON r.team_two_id = ta.team_id
INNER JOIN reports WHERE r.match_date = re.match_date
Now this works great, but I want to output everything in the results table, and not just where results.match_date = reports.match_date as there may not be a report for every result and I want to still output every result nomatter if there is a report or not?

Using Join In A SQL Query
I have a problem which cant sort out.
I have 3 tables where the primary keys in two tables also serve as keys in the third table.
Table1:one two three four
Table2:five six seven eight
Table3:nine ten eleven

one holds the same information as nine
five does the same for ten

Now I need to get information from the first and third tables but also need to use the second table to get the information from the third table.
I can get the info from the first table grand , but its joining the 3 tables together is where I get stuck.
I know its very abstact but this is the way I got the Tables.

Join Query
My problem is with my query below(sname means student name lecname means lecturer name) the table it produces means that the same lecturer can be supervising 2 projects at the same time(eg. Mon 9.00am in the query below) which is impossible.
So is there a way I could change it so that lecname can only be associated with one projtitle for each time?

INSERT into time select distinct p.sname,p.projtitle,s.lecname,s.time
from projectstatus as p,properstafftimetable as s
left outer join time as t
on t.sname2=p.sname where t.sname2 is null
and s.lecname = p.supervisor
and s.time = "Mon 9.00am";

Join Query
I'm having trouble with a (simple?) concept in MySQL join. This query gives me the correct data from all 3 tables, but I need to sort by the date from 2 of the tables:

SELECT det.CalendarDetailsID, dai.StartDate, wee.DisplayStart, det.Title, det.DetailsFROM phpCalendar_Details detLEFT JOIN phpCalendar_Daily dai ON det.CalendarDetailsID = dai.CalendarDetailsIDLEFT JOIN phpCalendar_Weekly wee ON det.CalendarDetailsID = wee.CalendarDetailsID
Is there a way I combine dai.StartDate and wee.DisplayStart dates fields into a new (alias?) field that I can sort with?

Or is there a way that I can simply sort the two fields within each other without combining them?

JOIN Query
This query won't work... tried to execute it a million times, it just loads forever. Any clue why?

SELECT oh.client_id, oh.id AS load_number, oh.deldtime, oh.pickupdtime, oh.dtime, oh.status, oh.order_uid, oh.driver_id, cm.name AS client_name, oo.order_id, oo.city AS origin_city, oo.state AS origin_state,
od.city AS destination_city, od.state AS destination_state, od.order_id, os.id,
os.status AS order_status, dm.id, dm.name FROM order_header oh INNER JOIN client_master cm ON cm.id=oh.client_id
INNER JOIN order_origin oo ON oo.order_id=oh.id INNER JOIN order_destination od ON od.order_id=oh.id INNER JOIN order_status os ON os.id=oh.status
INNER JOIN driver_master dm ON dm.id=oh.driver_id ORDER BY oh.id DESC LIMIT 10

Join Query
I have two tables dispatch and pending orders now i want to get fields from both the tables where either dispatch.mail_id = 'p@yahoo.com' or pending.mail_id = 'p@yahoo.com' order by either dispatch.order_no or pending.order_no.
How would I be able to do this with a join query.

Wish To Join A Query
I have this query:
PHP Code:

SELECT * FROM `news` ORDER BY `id` DESC LIMIT 6

I wish to also select the users id and username from the members table where the users id equals the "owner" field from the query above. As using another query apparently would be stupid?

Join-query
I cannot figure out how to query the db.
I got 3 tables:

players (player_id, name, number)
stat_start (opponent_id, name_id) name_id = players.player_id
stat_avb (opponent_id, name_id name_id = players.player_id

Now I want to list all my players, and count how many times they have occured in stat_start and stat_avb.

I guess I need to do an Inner join as i want all my players listed...

Join Query
this is embedded in a php file, and the request should bring all open assignment_events. it works fine except it spits them out 3-5 times, its the query and not the php, could someone please help with a join?

Code:

SELECT c.customer_id
, a.assignment_id
, ae.desc
, ae.assignment_event_id
, ae.closed
FROM customer c
, assignment a
, assignment_event ae
, event_type et
WHERE a.customer_id = c.customer_id
AND a.customer_id = 1
AND a.assignment_id = ae.assignment_id
AND ae.closed = 0
";

Join Query
i've got two table in the format:

referrers
--id, name, url, image

country_refs
--id, name, domain, ref1, ref2, ref3, ref4

and i'm using the query Code:

SELECT referrers.name, referrers.url, referrers.image, referrers.description
FROM country_refs
JOIN referrers ON ref1 = referrers.id OR ref2 = referrers.id OR ref3 = referrers.id OR ref4 = referrers.id
WHERE domain = 'gb'

Need Query To Join 2 Tables Through A 3rd
I have a table called client (primary key = clientID), another called clientFamily (pk = clientFamilyID) and a table that joins those two called client2clientFamily. The latter table only has two rows: clientID and clientFamilyID (no pk).

On the client table, say the client has 3 children and 2 siblings. Those would go on the clientFamily table. The part I don't know how to do is how to assign those 3 kids and 2 sibs to that particular client. I know I do it via the client2clientFamily table but I don't know the query.

I'm also not sure I'm thinking of this right. The fields on clientFamily start off like:

clientFamilyID
spouseFirstName
spouseMiddleName
spouseLastName
spouseOccupation
motherFirstName
motherLastName
motherOccupation
fatherFirstName
fatherLastName
fatherOccupation

I think that part's ok, but if I add:

childFirstName
childLastName
childBirthday
childSex

and

sibFirstName
sibLastName
sibOccupation

how are, for example, a child's birthday and sex are going to be linked to the correct child? And a sib's occupation linked to the correct sib? Now I'm wondering if I need a separate table for clientChildren (and a joining table client2clientChildren) and another one for clientSibs (& client2clientSibs) instead of putting everything in clientFamily. Ugh.

Join Query Problem... Help!
I have two tables:

STORES
-------
id
name

ORDERS
-------
id
total
cost
store_id
date_ordered
I want to select ALL stores and their total sales for a date range. Here's my query:

SELECT
stores.id,
stores.name,
SUM(orders.total),
SUM(orders.cost),
COUNT(orders.id)
FROM
stores
LEFT JOIN
orders ON stores.id = orders.store_id
WHERE
orders.date_ordered > 'xxxx-xx-xx xx:xx:xx'
GROUP BY
stores.id
This works, except that it only selects stores that have orders. If I leave out the WHERE clause, it then selects ALL stores, even without orders, and populates the sums with "null" That is what I want - if the stores don't have any orders, the results should simply return null or 0. However it seems that stores without orders are simply not being returned in the results at all.

Join Query Problem
student
-------
id name
1 name1
2 name2
3 name3

Class
-----
cls_id stu_id marks
1 1 100
1 2 300
5 1 400
5 3 500

I want to show only those student that are not class Ƈ'
So I wrote

select s.id,s.name,c.marks
from student s,class c
where s.id=c.stu_id
and
c.cls_id<>Ƈ'

but it gives

1 name1 400
3 name3 500

but name1 is already in class Ƈ' .
I desire the output only
5 3 500

Join Query Chaos
I cannot get this join to give me what I need. Here is my setup.

I have 3 tables: courses, modules, sessions.

courses has this structure:

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.

select sc.name,
Sum(if(student.SNO is not null, 1, 0)) nmbr,
from school sc
left outer join student
on sc.school_id = student.school_id
group by sc.school_id

this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13.
i could not find a solution.But i suspect from NULL threatment.

SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem:

SELECT * FROM footballers WHERE sname LIKE '%$criteria%'

however, if I want to join two tables and still use the LIKE clause how would the syntax look??

My guess below was incorrect. any help would be appreciated. thank you

WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)