MYSQL :: Query Returns Unexpected Users. Not Using Join.

Unexpected NULLs When Using 'left Join'
I'm trying to select all records from a table, plus the appropriate values from other tables to which my first table has pointers. I quickly discovered the benefits of LEFT JOIN over WHERE clauses but then found that I was getting more NULLs than expected. For example: Code:

Left Join Producing Unexpected Results
The following SQL query is producing undesirable results. What I am trying to do is get the ID of the people listed in the persons table, who are not on a given list, on the listtrack table. The listtrack table has an entry for each Person_ID<->List_ID pairing. I can find out easily who is on a given list, but it's finding who is not that is giving me bother.

SELECT people.PersonID
FROM people LEFT JOIN listtrack ON people.PersonID=listtrack.Person_ID
WHERE (listtrack.List_ID<>4 Or listtrack.List_ID IS NULL)
AND people.Company Like '(none)'

Unexpected Query Result Using IN()
SELECT post_id, post_user, post_subject, post_category, post_date FROM sb_blogposts WHERE '10' IN(post_category)

The above query is only returning results if the 10 is the first ( or only ) category number in the post_category field.

post_category has been defined as a varchar(255) and contains a listing of categories that each post belongs in. All of the posts in this database have their categories set properly, and some posts are in more than one category.

The query example should be returning with every post that has "10" in the list. but for some reason if the category is less than 10, it won't show. It's a bit strange and I don't understand why it's doing that.

JOIN Returns Duplicate Data
I am querying from 2 tables asuch:

t1 = property listings
t2 = property photos

SELECT t1.id, t1.area, t1.address, t1.price, t1.bedrooms, t1.bathrooms, t2.fileid
FROM listings AS t1, album AS t2 WHERE t1.id = t2.propid AND status LIKE 'for sale';

I am getting duplicate records when multiple photos are present, eg:

+-----+----------+-------------------------------------+
| id | price | fileid |
+---+----------+-------------------------------------+
| 1 | 899000 | 4490082059794440261166380 |
| 2 | 995000 | 1072603818590874471784367 |
| 3 | 630000 | 1192880275852882142198181 |
| 3 | 630000 | 7498054893972972542204555 |
+-----+----------+-------------------------------------+

Query Returns Empty Set
i have this query:
Code:

select p.url,s.tipo,pw.url as wrapper from secciones s
join plantillas p on s.plantilla=p.clave
join plantillas pw on p.wrapper=pw.clave
where s.clave=1

it works fine, the problem is that from time to time the second join condition won't match, because by design the field p.wrapper is optional, if that happens the query returns 'empty set' even if the where condition and the first join condition do match, is it possible to modify that query so it needs to match only the first join and the where clause to return the result set (i.e. make the second join optional)?

Self Join (?) To Get Data For Multiple Users From One Table
I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores:

TABLE: feedback

identities_id int(10) <- contains the user id which is stored in another table
feedback int(10) <- contains the feedback score for a specific date and time
date_recorded datetime <- date the feedback was captured
I need to get the feedback scores for let's say two users (I will want up to five but I figure the query will be almost the same) over a thirty day period. Here is the query I have created thus far (which does not return the correct results):

mysql
SELECT user1.feedback AS user1feedback
, user2.feedback AS user2feedback
, UNIX_TIMESTAMP(user1.date_recorded) AS date_recorded
FROM feedback as user1
LEFT JOIN feedback as user2 ON user1.date_recorded = user2.date_recorded
WHERE user1.identities_id = 1
OR user2.identities_id = 2
AND user1.date_recorded > NOW() - INTERVAL 30 DAY
ORDER BY date_recorded ASC

This returns virtually the same data for both users which means I am not distinguishing the two properly.

Use Where In Query So It Returns Rows That Has Less Than 10 Characters In The Word
I want to return rows that has less than 10 characters in the word that is used in the where clause sort of thing.

I can't really explain so I will give you an example, which is obviously wrong.

Quote:

mysql_query("select games from gaming where gamename < 10 characters");

My Query Returns Mysql Error 1267....why?
my query is not working... i am using 2 databases...

Running A 'Distinct' Query Returns Some Results TWICE !?
I've spent hours trying to find out why a perfectly simple query that uses the 'distinct' keyword such as:

Select distinct(field1)
From tablename;

works fine based on eg. field1, but when running it on eg. field2 in the very same table, it returns 2 results (yet, only on some words!). Pls see example of result below:

Eg of a 'faulty' result: .....

Most Active Users Query
I have a site where users can post in different categories (topics, questions, answers, comments). There is a table for each of those types, and each one has a userId field on it.
I'd like to add a "most active users" section that shows the top 10 users with the most entries in topics + questions + answers + comments, but I'm not sure what query to use to get that information.

Effect Of Increased Users On Query Time - Rec Specs
I have just started my first big project and I am having trouble predicting
the system specs that will be required. At present during trials the site
has around 15 users, although this will go to 350 when it is launched.

Most of the pages execution time should not scale with the increase in users
but there are a few report pages which calculate and order the information
entered by the users.

Measuring the page on my development machine (Duron 1.2, 768MB PC133, WinXP,
running apache 1.3.22, mysql 4.0.15and php4.3.4)...

One page I am currently getting 0.2-0.3 seconds. It uses phplib templates
and it executes 9 queries at present, 1 of which I predict will scale. It
calculates the mean, standard deviation and total of around 90 measures
using results from 15 users.

I do have a few nightmare pages for the regular users which execute 150+
queries (mixture of selects and updates).

Can anyone predict what will happen with 300+ users?

I am thinking max load being 20 regular users logged in with 2 users viewing
reports.

The machine I am currently looking at is P4 2.4, 512DDR with Linux. Will
this be suitable?

Unexpected T_STRING...
I have the following error and don't know why:
Parse error: parse error, unexpected T_STRING in /home/account/public_html/folder/folder/ban_user.php on line 25
Can someone please tell me why and how I can fix it? Thanks in advance.
I am trying to transfer one row of data from one table to another.

Here is the code that I am working with:

PHP

require "config.php";
$username = mysql_real_escape_string($_GET['username']);

// line 25 is below
$transfer = INSERT INTO banned_users (username, password, email, ip, comments, date_entered, articleid, commentid) SELECT username, password, email, ip, comments, date_entered, articleid, commentid FROM users WHERE username = $username or die (mysql_error());

if (mysql_affected_rows() == 1) {
    $q = "DELETE FROM users WHERE username = '$username'";
    $r = mysql_query($q) or die ('Could not query because: '.mysql_error());
    print '<p>The user has been deleted. <a href=ban_a_user.php>Click here</a> to continue.</p>'
} else {
    print "<p>Could not delete the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>";
}
mysql_close();

Unexpected Data
I have a table which is a combination of two sales tables created within two retail outlets. Tot number of records at present stands at just over 96000 records. The date fields of the original tables are not in a format useable in MySQL so I insert an epoch value into the DATED field based on the date field of the original tables. First Example.

Range 1/1/2000 - 31/1/2006
SELECT * FROM `sales` where DATED>=946771200 and DATED<1138752000
total - 69215

Unexpected Parse Error
The code I'm using to retrieve data from my database where a field called past is

<?php
$past = mysql_query("SELECT * FROM stories WHERE past="yes" ORDER BY ID DESC LIMIT 10;",$db);

while($row = mysql_fetch_row($past))
{
echo("<h1>" . $row[1] . "</h1>" . "</br>" . "<p>" . $row[3] . "<p>");
}
?>

Unexpected Syntax Error Using In Operator
I am gettin a syntax error in the following statement

SELECT t.timesheetID
FROM camco.timesheetdetail t
WHERE t.ProjectID IN (SELECT o.ProjId
FROM camco.outsidecontractmngr o
WHERE o.Id =10);

The inner querr works by itself but when married to the outer I get the following message:

"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 'SELECT o.ProjId FROM camco.outsidecontractmngr o
WHERE o.Id =1"

I can see no error. All fields involved are of type integer and do exist in the tables which are also defined(I use MySQL Query browser to enter field names to be sure)

MySQL 5.0 And Jdbc - Unexpected End Of Input Stream
Is there something different going on with JDBC and the alpha version
of MySQL 5.0? When trying to connect I am getting:

** BEGIN NESTED EXCEPTION **

java.io.IOException
MESSAGE: Unexpected end of input stream

The client machine trying to connect to the server *CAN* connect via
the mysql command line client (ie. is has permission to the servers).
I have tried connecting to version 5.0 installs on both Linux and
Solaris with the same result. If I point the driver back to a 3.23.57
install, it works fine as always. Code:

Updating Give Unexpected Math Results
If I do the following I get a ridiculously huge value out of range on a MEDIUM INT (7) field that is SIGNED:
UPDATE some_table, other_table SET some_table.amount = some_table.amount - other_table.quantity_lost WHERE some_table.id = other_table.id

Why is this happening? I am expecting maybe a -3 if the 'quantity_lost' is 5 than the 'amount' is 2 but instead I get a number like 1654681313879138538515468.

Create Users Table For Users To Log On Or ...?
I'm pretty new to MySQL, what I was wondering is, for web systems that you need to login to, do developers generally create a users table and store all their users in there, or do they create users such as the root user in mysql ? If so, how do you add extra fields such as firstname last name etc and how would you go about putting them into groups?

SELECT DISTINCT Unexpected Results With Multiple Columns
SELECT DISTINCT (query) FROM `data` WHERE 1 LIMIT 0 , 30
and it works fine. When I start selecting more than one column, though, it returns all the rows weather the query is distinct or not. Here is the query that doesn't work.

SELECT DISTINCT (query), data. * FROM `data` WHERE 1 LIMIT 0 , 30
It seems like DISTINCT() is looking for a completly distinct row. How can I get it to just look for a single distinct column?

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)

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?

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

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)