MYSQL :: JOIN Query Works With 3.23 But Not 4.0.13

SQL Query Works Only In Win32!
I use MySQL from my php page and from phpMyAdmin.

In Windows Server, following query works fine. In Linux Server instead it returns error.

The SQL Query is:

SELECT DISTINCT a.id, a.container, b.container FROM folder AS a JOIN folder AS b ON a.container=b.id

The PHP error is:

You have an error in your SQL syntax near 'ON a.container=b.id' at line 1

So, I ask: If in Windows this query work fine, where is the error in the Linux version?

Parentheses: Query Works With 4.1.7, But Not With 4.1.11
Since we updated to the newest MySQL version (Windows) some of our queries do not work any more.

It's reproducable by this (dummy) query:

SELECT B.ID from ((SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID);

This works with 4.1.7, but not with 4.1.11, unless the parentheses are removed:

SELECT B.ID from (SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID;

Does anybody know why MySQL changed its behaviour that way?

I know the parentheses in the first query are not necessary, but it would be quite difficult to search through all our queries for unnecessary parentheses.

Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing.
Code:

SELECT Artists.Artist, Albums.Album_Name
FROM Artists, Albums
WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id

GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version.

The query is this:

Query Help. This Works, But I Don't Think It Is Good Practice
comics:
id
date
location

comments:
id
comicid
datetime
header
comment
author

Every comic can have multiple comments. Each comic will have a comment, but there is a period of time where the comic is posted but the first comment is not yet submitted. So for argument's sake we can't assume there is any comment at all for a given comic.

I want the all the comic ids and dates before a certain date along with the most first comment entered relating to that comic.

select comics.date
, comics.id
, comments.header
from comics
left outer
join comments
on comments.comicid = comics.id
where (comics.date>=񟭇-03-01')
group by comics.id
I never defined WHICH header should be returned when many are grouped together. I just know that mysql will return the first one in this circumstance. I seem to recall reading somewhere that different databases handle this sort of thing differently, with some returning a null in that field?

I keep trying to forumate a useful subquery but keep drawing blanks just as I think I have it figured out in my head. Like I forget how the string starts by the time I mentally formulate the end....

SQL Query Works Fine With Phpmyadmin But Gives Me No Results And EOF With ASP
I have a strange problem: when I perform a sql query on my phpmyadmin it works fine and it gives me the right results. If I perform the _same_ query through my asp application I reach immediately the EOF condition without any results!

Works
I have this and it works just fine but I cant help but think there is a much better way of accomplishing the same thing in one sweep verses 6
PHP Code:
mysql_query("update residential set bed='1' where bed like 'ONE%'");
mysql_query("update residential set bed='2' where bed like 'TWO%'");
mysql_query("update residential set bed='3' where bed like 'THREE%'");
mysql_query("update residential set bed='4' where bed like 'FOUR%'");
mysql_query("update residential set bed='5' where bed like 'FIVE%'");
mysql_query("update residential set bed='6' where bed like 'SIX%'");

Works In MYSQL 4.1 But Not In 5.0
MySQL
1054 - Unknown COLUMN 'p.manufacturers_id' IN 'on clause'
SELECT count(*) as total FROM products p, categories c, products_to_categories p2c LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE c.categories_status=1 AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND p.products_status = Ƈ' AND p.products_id = pd.products_id AND pd.language_id = Ƈ'

Here is the query in PHP

PHP
"select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = Ƈ' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069.

I want to run a query to return the three items.

My query: Select * from Inventory where Description Like 'w069'

It doesn't seem to work, returning zero row.

UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days.

Notes:
Server language is ColdFusion
gamedate is type DATE

SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate >= now()
UNION (
(SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate < now()
ORDER BY gamedate desc LIMIT 1)
ORDER BY gamedate asc LIMIT 3

The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".

SSN Changes In Database But Still Works As Key?
I have a ssn field in a table but somthing happens to the data. I use an update to enter the data and set the value to 123-12-1234 for a test but when I look in the table or I get the record from a record set it come out like -1123.

Why is this? But when I run a query and ask it to return all records which have a ssn of 123-12-1234 it returns the correct record. If this is correct then how can I display the ssn number in a java text field.

How Decimal Works
Say that we have an variable that is

var1 = 3
var2 = 3.65

var3 = var1 * var2 '--- Sum = 10.95

Now my Question!
How do I input this variable into MySQL in a COL whit DECIMAL (10,2)?
I only get the result 10.00 in the COL?
It seams that MySQL round every resout I get to .00 at the end.

If I make an varible hard ex.
var3 = "10.95"
that works but if I convert an numeric variable to string it doesnt?!?!?!
ex.
var4 = cstr(var3)

UPDATE Works In MS SQL
I use the following stament work with MSSql, but not MySql, why ?

Update mytable set mycol = 'my_Text' + mycol

Failed, mycol is varchar

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'

MySQL Delete: Works In Php 5 But Not Php 4?
I've developed a small login system using Xampp on my local machine. Everything is running great except when the admin tries to delete something. Apparently I have the wrong syntax for my deletion in mysql.

Here's my original delete statement (from mysql5):
DELETE at.* FROM access_table AS at WHERE at.client_id=2

And here's the error I get from mysql4 (I ran the command in phpmyadmin console):
#1066 - Not unique table/alias: 'at'

So I removed the aliases and re wrote my command as such:
DELETE * FROM access_table WHERE at.client_id=2

And got the following error from php4:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM access_table WHERE at.client_id=2' at line 1

SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing
I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I
did not convert the privilege tables with mysql_fix_privilege_tables.

Suddenly, the mysql started to show all databases to all users (even
though the user had access denied and could not do show tables on
them). So I started to find help and tried the
mysql_fix_privilege_tables. It didn't work properly (more than just
access denied and errors, that are ok) so I did the changes manually
conforming to source code of mysql_fix_privilege tables.

PhpMyAdmin stopped its warning about privilege tables not conforming
to current mysql version. Everything seemed ok except that SHOW
DATABASES still worked.

The users naturally all have Show_db_priv="N" in the tables. Also
trying "safe_show_database", "skip_show_database" in my.cnf and
"--skip-show-database" witch mysqld didn't help.

Exporting MS Works For MySQL
I have a MS Works database and I can't find anything useful on the PHP site for working with this type of database. I did a quick search and it seems that it can be exported for OoenOffice.org and other spreadsheet database programs but none of them give a method. Mostly selling books and MS Access.

If there is a way to access the .MWD or .DBF Microsoft works files with PHP that would be great. If that's not possible, preferably not, I'd rther switch to MySQl easily if possible. I'm still looking but it's not looking good.

Mysql.exe Sort Of Works
I have just installed MySQL (not an easy thing to do). I also have installed PHP. I use Dreamweaver8 for 2 static sites, and eventually will figure out how to use that with PHP/MySQL.
Everything seems to work fine, and I'm working my way through Janet Valade's book, "PHP and MySQL for Dummies." Everything I have tried in the book works perfectly except for the mysql.exe command.
I am in the correct folder and type mysql.exe -u root -p and am prompted for my password. When I enter my password the cursor moves to the next line and just sits there. Nothing happens. Fortunately, the window is not frozen and I can just close it.

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:

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)

Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used).

What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?

Ranking Query (self Join)
I would like to get a rank (row # of ordered results) of an article based on its average rating in relation to all other articles. I have a ratings table that holds all of the individual votes cast by users (ratingID, articleID, userID, rating). So I need to order the articles by AVG(ratings.rating) and then come up with an individual article's row # to find its ranking.

I know this involves joining the ratings table on itself with a greater than or less than operator, but i'm completely lost when it comes to self-joins. I know this is easy, but there is a small trick to it and i'm clueless.

Help With Join Query Mysql 4.0
As I understand subqueries do not work in version 4.0

Here is what i am trying to do;

select a, b from table1 where table1.b != (select distinct c from table2);

Other words:
table1.b and table2.c have values I want to match on

I run
select a, b from table1;

subtract all out using

select distinct c from table2;

and leave me with a and b from table 1 that have no corresponding value of c in table 2

Generating A JOIN Query
I need some help with a MySQL query, probably a JOIN. Here is the situation:

I have two tables. The first one contains customer info.

Table CUSTOMERS
id | name | address

id ... customer ID (MEDIUMINT, autoincrement)
name ... customer name, for example John Doe
address ... customer shipping address

Then I have the second table which contains sale info from all customers....

Query Involving JOIN
I have two tables populated during the use of an application to log
user events and application states. They are named "EventTable" and
"StateTable" and the structures follow:

EventTable:

ID EventTimeStep EventID
-- ------------- ---------
1 5 E1
2 22 E2
3 56 E3

StateTable

ID StateTimeStep StateID
-- ------------- -------
1 1 S1
2 39 S2

I want to perform a query that reports the StateID of the application
at the time that each event was logged to the EventTable. The desired
output is:

ID TimeStep EventID StateID
-- -------- ------- -------
1 5 E1 S1
2 22 E2 S1
3 56 E3 S2

I have tried to create a query with an INNER JOIN where the value for
the StateID output field comes from the last row in the StateTable
WHERE StateTable.TimeStep <= EventTable.TimeStep and where I use a
GROUP_BY EventTable.ID to merge the following rows from the join:

3 56 E3 S1
3 56 E3 S2

However, the closest I can get is a query that gives me the wrong
state when applying the GROUP BY clause

3 56 E3 S1

I also think that the queries I have written is slow and inefficient.
Is there a better way to perform this query or is my database design
fatally flawed?

Table Join Query
I'm trying to join multiple database tables and having a little trouble with it, hope someone can help:
There are 3 tables with columns: id, user, date, time, action
I'm trying to get user, date, time and action from all 3 tables where the user equals "anyuser" and sort the result by date DESC, time DESC.

Join Query By Date
I've these tables:
- PERSON (id,name,age,....)
- EXAM (id,date,note,exam_type,id_user)

One person has 0 or more exams.

I have to do this report:

name, age, date, note, exam_type

This looks easy, but I have to list for each person, his/her LAST EXAM GIVEN. I mean, only the last exam must be shown for each person, and well, if person has no exam yet... to show blank (if possible)

MySQL JOIN Query
I am a recreational MySQL user, and I tend to make some non-optimal queries. In my latest project I made some LEFT JOIN queries that were extremely slow.
I made two changes that made the queries almost instant.
1) indexed the columns that the joins were working on
2) optimized the tables being joined (using phpMyAdmin > Operations)

All I can say is WOW!!! I had no idea that the resulting difference in speed would be so great. After I did step (1) it added a tremendous amount of speed, and after I did step (2) the queries responed almost immediately. So if you are running slow with JOINS, give indexing and optimization a try.

Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing

Left Join Query
I have this query

SELECT user.user_id, email, password, full_name, company_name,
first_login_date, last_login_date, first_draft_date, last_draft_date,
first_submit_date, last_submit_date, rowc.*
FROM user LEFT JOIN rowc ON rowc.user_id = user.user_id
WHERE user.user_type = 'USER' ORDER BY user.email

Which returns all records in user table plus rowc records if there are matches in the user id.

but user.user_id is null when there are no matching recordings in the rowc.
Isn't left join meant to pull all the records in the user table? What am I doing that is wrong here?