MYSQL :: Creating Non-existent Rows In Query With Join

Fetching Non-existent Rows
I've got a table "banneractivity", which stores clicks and views of banners on my website.
The table contains the following fields:
id INT
activitydate DATE
activitytime TIME
action (either "click" or "view")
bannerid INT

I want to retrieve the activity by the hour, to show in some statistics. I've used the following query thus far:

SELECT
id, COUNT('action') AS actionsum, HOUR(activitytime) as theHour
FROM
banneractivity
GROUP BY theHour
This works reasonably well, but I would like to retrieve 24 rows, for every hour of the day, even if there has been no activity in a particular hour. Is this possible?

Another thing I would like; is there a way to distinguish between "click" and "view" and therefore select two different sums from the database?

Query And Non Existent Records
I have a mysql table texts which keeps textlines in different languages.

columns: textId, languageCode, textline

The application adds records for specific languages as:

textId 1
languageCode: EN
textline: "Username"

another record could be:
textId 1
languageCode: DE
textline "Anwendername"

Now at a certain pojnt in the app I have to check whether for a specific textId there are textline filled in for specific languageCode. For example: Is there a textline for records with textIds 120, 124 and 134. And are these records present for EN and FR (French). I can query as:

Select count(*) from texts where textline != '' and textId in (120,124,134);

But what when the records are there in ENglish and German but not in French?

Any ideas how to solve this?

Fatal Error: Cannot Instantiate Non-existent ->
yesterday, it was working fine. But then, the site went down and a few hurs alter, I saw a page saying that the Apache was installed succesfully. But now, appears this fatal error, and I can't view my website and even my admin section.

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

Warning: main(db/mysql.php): failed to open stream: No such file or directory in /home/coopera/public_html/db/db.php on line 53

Warning: main(): Failed opening 'db/mysql.php' for inclusion (include_path='/usr/local/lib/php:/usr/lib/php') in /home/coopera/public_html/db/db.php on line 53

Fatal error: Cannot instantiate non-existent class: sql_db in /home/coopera/public_html/db/db.php on line 86

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:

Join Rows Into One Result
I'm trying to select letters in a word from a table with the ascii representations. I am selecting the letters successfully, in the correct order. The rows returned give me a letter in each row. I would like it to return one row with the joined word.

aka. It is returning
a
r
d
v
a
r
k

I would like
ardvark

This probably has to do with GROUP BY, but I don't know any functions to join characters into a string.

How Can I Use A Join With Possibly Nonexistent Rows?
here's the situation: I'm using mysql to house a database of quotes, complete with ratings by users. So in one query, I need to get everything with a certain rating from the "quotes" table, get the submitter's username from the "users" table and finally check the "ratings" table to see if the user viewing the quotes has already rated it.

The "ratings" table consists of the user id and the quote id. So ideally, I'll be able to to get the first 25 quotes and end up with each in a row something like this:

submitter's id - quote id - quote text - user id - quote id 2

where "user id" and "quote id 2" come from the ratings table and will be NULL if the quote hasn't been rated by the current user, and will be equal to the current user's id and the quote id if it has already been rated.

So far, I think the closest I've come to success was was using this subquery (which would probably be terribly slow even if it did work):

SELECT quotes.*, user.username, ratings.quoteid AS votedid, ratings.userid AS votedby
FROM quoteratings AS ratings
INNER JOIN quotes AS quotes
LEFT JOIN user AS user ON user.userid = quotes.userid
WHERE quotes.quoteid IN (SELECT quoteid FROM quotes WHERE average >= 0 AND approved = 1)

I hope that explanation makes sense, I know there has to be a fairly easy way to do this, but I just can't find it.

Join To Find Rows Not In Second Table
I'm trying to write a SELECT for MySQL 4.0 using a JOIN. I can get it to work in v4.1 using a subquery, but my ISP provides v4.0 only.

I've got 2 tables:
- group: Describes groups that exist, key is group_id
- usergroup: Members of groups - has user_id and group_id

I want to find which groups a user **doesn't** belong to (say user_id=3).

In MySQL 4.1+ I can do this using a subquery:

SELECT group_id FROM group
WHERE group_id NOT IN (
SELECT group_id
FROM group g,usergroup ug
WHERE ug.group_id = g.group_id
AND ug.user_id = 3
)

This query doesn't work in MySQL 4.0, no I need to use JOIN (I think). I've been searching forums and trying things out, but I cannot figure out how to make it work.

Listing Multiple Rows In One Row With JOIN
I have assets and tasks in my table. There are multiple assets assigned to each task. What I want to do is to list the asset followed by all the tasks linked to it in one row.

SELECT * FROM asset
LEFT JOIN task ON (asset.id = task.asset_id)
WHERE asset.id=task.asset_id
GROUP BY asset.name

The above is close, but it will only join the first matching entry from the task table, the result I was hoping to get was something like.

asset1.id asset1.name task1.name task1.status task2.name task2.status

Is there an easy way to do this in SQL?

Creating An A To Z List From Query
I am creating an a to z list - basically a count of all results that
start with the letter "A", "B", "C" .... and so on.

I am pretty poor at SQL so I am sure some brains out there can do
better than I have here. What I have is working, I just want to make
sure that it is optomized.

So let's assume I have some query "$query" that I want to run and get
an A..Z list based on column "$column".

Let's further assume that '$query" produces the following results, and
that $column is equal to "last_name".

last_name
---------------
Anderson
Bitmore
brown
Bogus

My AZlist query would look like this:

select * from
(SELECT count(alist.$column) as a from ($query) as alist where
alist.$column like 'a%' or alist.$column like 'A%' ) as a_result,
(SELECT count(blist.$column) as b from ($query) as blist where
blist.$column like 'b%' or blist.$column like 'B%' ) as b_result,
....
(SELECT count(zlist.$column) as z from ($query) as zlist where
zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result;

And this retuns the following result:
a | b |...| z
--------------
1 | 3 |...| 0

Meaning that $query has 1 result where the first letter in $column is
"A" or "a", 3 results where the first letter is "B" or "b" and 0
results where the first letter is "Z" or "z".

What I am afraid of here is that "$query" is being executed 26 times
(once for each letter of the alphabet) . Is there a way to refine
this, or is MySQL (4.x and 5.x) smart enough to optomize this on its
own?

Creating A Query From A Form
I'm struggling building a SQL query from the output of a form, i.e. the user inputs into a form which in turn decides the query.

I have never done this before and was just wondering if anyone had any links tutorials of something like this!? I have searched but haven't found anything too useful yet.

Basically all I want to do is for the user to pick from a drop down menu how they want a leaderboard displayed, i.e. top 50 results, bottom 50, 50 to 100 results, etc.

Do I just tie a complete SQL statement with the corresponding LIMIT info inside to a variable. The variable being the value of the chosen item in the drop down menu.

Pseudocode as follows:

Creating Complex Query
its a booking form. user selects start date, finish date, and room type.
on my table i have roomInfo(roomNo, type, NoOfBeds);
occupancy(roomNo,DateStart,DateFinish,GuestNumber);
i am trying to do something like:
select roomNo from roomInfo where roomNO = single and roomNO not IN ...
cant do subselect its mysql 4.x version... would i have to do an outerjoin/innerjoin?

Creating An Optimal Query
What I am doing at present is displaying a list of topics such as on a forum main page. That data is in a table say "my_topics" for example
The list I am displaying also references data in another table say "my_images". Now each item in my_topics could reference zero or more items from my_images and they would need to be displayed alongside that topic. So each item in my_images has an id which is an index into the my_topics table.
At present I am dumping the entire my_images table into an array so I can access it without having to do multiple sql queries. I can't use "left join" as far as I know because the relationship between my_topics and my_images is one-to-many rather than one-to-one.Obvious downside is that as my_images table gets larger - dumping it to an array increases the memory usage of the script which eventually leads to it not working.

Remove Duplicate Rows - MySQL4 JOIN
I am looking to remove duplicate rows from a table.
I have limited knowledge of MySQL - so please bear with me.
The following code correctly displays the duplicate rows:

SELECT firmname, address1, custom_2, MIN( selector ) AS min_sel, count( * ) AS issimilar
FROM my_table
GROUP BY firmname, address1, custom_2
HAVING issimilar > 1;
Now I try to run an inner join to show the rows, so that I can then delete them (delete is not included yet)

select bad_rows.*
from my_table as bad_rows
inner join (

SELECT firmname,address1,custom_2,MIN(selector) AS min_sel,count(*) AS issimilar
FROM my_table
GROUP BY firmname,address1,custom_2
HAVING issimilar > 1

) as good_rows on good_rows.firmname = bad_rows.firmname
AND good_rows.address1 = bad_rows.address1
AND good_rows.custom_2 = bad_rows.custom_2
AND good_rows.min_sel <> bad_rows.selector;
The problem is that I can't use a select inside a JOIN in MySQL4.

I don't know enough about MySQL to rewite the above so that it will work in MySQL4 - I know its something like:

SELECT t1.firmname, t2.firmname, count(*) AS issimilar
FROM my_table AS t1 INNER JOIN pmd_listings AS t2
ON t1.firmname = t2.firmname
GROUP BY t1.firmname, t2.firmname
HAVING issimilar > 0
ORDER BY issimilar DESC;
Hoping someone can help. Also have a question on comparing data from other tables - but I need the above to work first.

Getting Number Of Rows From Left Join Table
PHP

SELECT bulletin.id AS bid, bulletin.bulletinname AS bname, bulletin.bulletindesc AS bdesc, bulletin_post.id AS postid, DATE_FORMAT(bulletin_post.postingtime,'%M %d, %Y %l:%i%p') AS postdate, bulletin_post.bulletintitle AS ptitle, member.screenname AS mname, member.id AS posterid FROM bulletin LEFT OUTER JOIN bulletin_post ON bulletin.id = bulletin_post.bulletinid AND bulletin_post.postingtime = (SELECT MAX(postingtime) FROM bulletin_post WHERE bulletinid = bid) LEFT JOIN member ON member.id = bulletin_post.memberid WHERE bulletin.active = 1 ORDER BY bulletin.bulletinname ASC

My question is how would I get the count of rows if any from the bulletin_post table?

Can't Use JOIN And SUM To Roll Up Rows Into Multiple Columns
Let's say I've got the following table "Eats" that contains row after
row of Calories consumed by children eating lunch at the school
cafeteria. The "ID" column is an autoincrement field I added, but it
doesn't seem to be doing me any good.

mysql> SELECT * FROM Eats;
+------+------+------+----------+----+
| dow | who | sex | Calories | ID |
+------+------+------+----------+----+
| Mon | John | Boy | 2600 | 1 |
| Mon | Tom | Boy | 1900 | 2 |
| Mon | Jane | Girl | 1200 | 3 |
| Tue | Tom | Boy | 1600 | 4 |
| Tue | Jane | Girl | 1300 | 5 |
+------+------+------+----------+----+

The output I WANT is a table with total calories broken down by gender
and day, i.e., one (and only one) table with a "Boys" total and a
"Girls" total. In this case I want to see:

+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 1200 |
| Tue | 1600 | 1300 |
+------+------+-------+
It's important to have One table with Two headers, not the other way around.

Not knowing any better I tried a JOIN and came closest with:
mysql> SELECT a.dow AS "Day",
-> SUM(a.Calories) AS "Boys",
-> SUM(b.Calories) AS "Girls"
-> FROM Eats AS a
-> JOIN Eats AS b
-> ON (a.sex="Boy" AND b.sex="Girl" AND a.dow=b.dow) GROUP BY a.dow;

Which produces:
+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 2400 |
| Tue | 1600 | 1300 |
+------+------+-------+

Everything is right except Mon-Girls, which is twice the real value.
I figure this is due to the pairing of Jane-John and Jane-Tom, causing
Jane's meal to get counted twice. But that pairing is necessary to make
sure we count all the boys' Calories too.

Am I overlooking something obvious? Are JOINs and SUMs just a bad idea?
Most importantly, is there some other way to accomplish the task? I've tried
many, many variations on the above code (with and without using the ID field)
and gotten nowhere.

For this simple example I could do SELECTs INTO variables, but that solution
doesn't generalize to hundreds of rows, nor cases where the children are
further broken down by age. Code:

LEFT JOIN Produces Extra Rows
The `suggestions` table contains 2,265 rows. The `ALL_PHS_SCHEDULES` table contains over 22,000 rows. I have done my homework and I read that the Left Join return ALL rows from the first table and the matching ones from the other table. I am expecting exactly 2,265 but the query is returning 2,734 rows.

Quote: SELECT *
FROM `suggestions`
LEFT JOIN `ALL_PHS_SCHEDULES` ON `suggestions`.course_number = `ALL_PHS_SCHEDULES`.course_number
AND `suggestions`.section = `ALL_PHS_SCHEDULES`.section AND `suggestions`.id = `ALL_PHS_SCHEDULES`.id
ORDER BY `ALL_PHS_SCHEDULES`.`course_number` , `ALL_PHS_SCHEDULES`.section ASC

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.

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.

Creating A Rank Column For Query
My problem is how do I create a rank column for a query. Meaning depending on a students score they will have a rank of 4 for example. Also if two students ahve the same score there rank should be the same and the next students rank would be 2 more. I tried using a global variable but dont know how to increment it properly. There must be an easier way of doin this then Im thinking of.....

Problem With Left Join And Count, Returning More Rows Than What It Should
Am having a problem with a query, strangely ...

PHP

SELECT *
FROM table1 AS mt
LEFT JOIN table2 AS pt ON mt.p_id = pt.p_id
WHERE my_field = 'somevalue'

Is returning a much bigger number (12 rows) for me, then what it should.

PHP

SELECT *
FROM table2
WHERE my_field = 'somevalue'

Is returning only 2 rows

Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site

Titles
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And POSTS
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being

PHP

SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
            FROM grps
            RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
            GROUP BY grps_c.catid
            ORDER BY grps_c.title

which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add

PHP

LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')

however adding that removes the rows that have a 'count' or NULL or Zero.

Adding Missing Rows In Table In 1 Select (+ Join) Command.
Hi all! Here's what I need to do :

I have two tables :
A B
a b c d
---- ----
1 z 1 k
2 x 5 l
3 c 6 j

I need a SELECT with JOIN that would give me :
A
a b
----
1 z
2 x
3 c
5 NULL
6 NULL

so I need to add the missing rows from the A.a and B.c JOIN,
how can I do that ?

I can't use a Union 'cause I can't use MySQL version 4.

MyISAM Vs InnoDB While Creating A Table Using Select Query
I have created a table using the select query.

****
for eg: create table table_name1 as select * from table_name2
****

now the created table is by default MyISAM format, what changes should i make to the query so that the created table is InnoDB.

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

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?

SQL Query Not Outputing All Rows.
My query doesn't seem to output all 6 rows in the database only two queries and i'm unsure why. I do know it is the query as when i do
SELECT * FROM case_studies
It outputs all rows.
The query i am usign is:
select programs.program_title,
programs.id, case_studies.id,
case_studies.title, case_studies.author,
case_studies.timestamp from case_studies,
programs WHERE programs.id
= case_studies.id
Any ideas?

Query Help, Comparing Rows
Suppose I have the following data:

+----+----------+-----+-----+-----+-----+-----+-----+-----+
| Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
| 11 | 11:20:00 | F | T | T | T | F | F | F |
| 12 | 11:45:00 | F | T | T | T | F | F | F |
| 14 | 12:10:00 | F | T | T | T | F | F | F |
| 15 | 12:35:00 | F | T | T | T | T | T | F |
| 17 | 13:00:00 | F | T | T | T | T | T | T |
| 18 | 13:25:00 | F | T | T | T | T | T | T |
| 19 | 13:50:00 | F | T | T | T | T | T | T |
| 20 | 11:28:00 | F | T | T | T | T | T | F |
| 21 | 11:53:00 | F | T | T | T | T | T | F |
| 22 | 12:18:00 | F | T | T | T | T | T | F |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.

How Many Rows Were Affected By Query?
How do I retrieve the number of affected rows by an UPDATE query with SQL? The C API exposes mysql_affected_rows() but I can't find documentation of the SQL equivalent...

I'm trying to find if an UPDATE had an effect within a stored procedure, so I can do an INSERT if there's no row to update.

Doing it the other direction to generate an error to act on would be quite wasteful... one INSERT and millions of errors per day.

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

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'

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.

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;'
. ' '

I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work)

$sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";

Query Pulls Out Multiple Rows Even Though Theres Only One
Ive got a query thats selecting info about a product from a table called items and joining on a table called itemimages to get its associated images. The product can have more than one image. If i run the query on an item with 2 images i get 2 results for one item.....when theres only one item.....it seems to duplicate the item for each of its images....

SQL
SELECT items.*, itemimages.* FROM items INNER JOIN itemimages ON (items.itemID = itemimages.itemID) WHERE categoryID = '$category' AND active = 'yes' LIMIT $start, $limit"

Creating Non-existent Rows In Query With Join

I want to make report using PivotTable/CrossTab and I used an application to create it.The problem is, I want to so show NULL value to the temp table that will be the source of my report.I'm using this query:

I want to make report using PivotTable/CrossTab and I used an application to create it.
The problem is, I want to so show NULL value to the temp table that will be the source of my report.

I'm using this query: