MYSQL :: Quick Join

Quick Fix 4 MySQL Pro
I will have a few instances like this and since I'm new to MySQL, perhaps a good example will help down the road. All I want to do is display a list of the first and last name of the names in my DB so they appear like so:

John Smith
Suzie Johnson
Etc...

first and last are two separate fields in the DB so I just need to join them together. The code I have now gives me this error:
PHP Notice: Undefined index: first in e:etc...

<?php
// Request the names in db
$result = mysql_query("SELECT artist.first, artist.last FROM artist");
if (!$result) {
echo("No artists posted at this time");
exit();

code...

Quick PhpMyAdmin
I believe I got phpMyAdmin setup to work right, I used HTTP authentication, and I get a log in screen when I goto the url. How do I set up an account for that?

GRANT ALL PRIVILEGES ON acct1_db.* TO 'Brian'@localhost IDENTIFIED BY 'password';

Is that right? I wish to host websites off this computer and have multiple users. Do I need to log in to the controluser account to do it? Or am I way off?

Quick Query
this one shoud be easy, but I just can't think of the best way (other than writing a php script to do it). I have a CMS and need to change one word in the description field for 64 rows. For example, where we now have 64 entries with "Bilbo" in the middle of the data, we need to change it to say "Frodo" instead. (Actually, I need to replace a misspelled javascript toLowerCase() call...darned non-programmers! They used toLowercase() - note the lower case C)

Quick Queries
Is it possible to use a regular expression or similar in a mySQL query, to make 'fuzzy' comparisons, or is LIKE the best we can hope for?

QUICK AND EASY ONE
im fairly new to php/mysql so please bear with me
I am trying to upload a txt.file created from an excel spreadsheet using the following query:

LOAD DATA INFILE 'path/file.txt' INTO TABLE my_table;

im getting a syntax error!
what could be wrong? are there any other attributes i should be adding to this query? i also tried uploading a csv version of the same file and get the same error.

Quick COUNT()
The MySQL documentation says:

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.
How do i get it to INCLUDE null values? I am using COUNT with the DISTINCT bit as well, and i want the NULL rows to count as one as well.

Quick Tip: Switching Field On/off
Ever wanted to change a database field (typically TINYINT(1)) from 1 to 0 or from 0 to 1 depending on the current value?

No need for two queries. I just thought of this 'clever' way to take care of it. Never saw that done this way before, and should have thought earlier about this.

UPDATE articles
SET published = ABS(published - 1)
WHERE id = x

How To Dump (quick) A Table Into Another One.
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

Quick Ones To Speed Up Access
------=_NextPart_000_0032_01C35FC2.62884DE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I am querying from a table containing more than 40,000 records. Earlier =
when the records were 10,000 it was taking 9 sec and now after 1 year =
and 40,000 records its taking 30 sec. Code is the same.=20

I am pretty sure that it has something to do with database only. No body =
can think of spending 30 secs for retrieving values from tables unless =
it's very huge in the sense of millions of records. =20

I don know whether I have to modify my database or do some sort of =
restructuring or reindexing so as to make it fast enough.

Is there some methods or optmization which can be applied to this =
database which hasn't been touched since design to enhance the spped.=20

If there are some quick ones but valued alottttttt please let me know.

Dump (quick) A Table Into Another One
On MySQL, I've got a table I use as 'wild card'. I mean, I store
data into it and, at the end, I dump all the date into 'production'
table.

Until now, I perform it following these steps:
1) I empty 'production' table
2) I make a SELECT of every rows of the 'wild card' table and an
INSERT into 'production' table.
3) I empty 'wild card' table.

But I've got problem: during SELECT+INSERT time, 'production' table
is not full and there's a data lack.

I'm looking for a faster way. Does anybody know it? Thank you very
much.

PS: I was told about 'mysqldump', but I do not know if it's faster
than mine.

Quick Date Subtraction Question
When I do the following, I get the number of days in difference between two dates however for the dates that have expired, I get the number of days back negated, ie -3 for example?

What I actually want is that if there has been an expiration, I want the result for a given row to be 0. Anyone got an idea how to?

// timestamp is datetime creation of record
// expires is date in future, ie 7, 14, 21 or 28 days in that increments
mysql> select day( expired ) - day( now() ) as days from polls;
Here is an example of the data returned,

+------+
| days |
+------+
| 7 |
| 7 |
| -3 |
| 7 |
| 7 |
| 21 |
| 7 |
| 7 |
| 14 |
| 21 |
| 7 |
| 21 |
| 7 |
| -3 |
| 21 |
| -3 |
| 7 |
| 7 |
| 7 |
| 14 |
| -3 |
+------+

Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes.

I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?

Quick Question - Id Mediumint(8) - Does The (8) Matter?
just a quick question really..

Just setting up 1 of my tables and just wondering if it matters if i set my id to mediumint(8) but i only ever get to 6, will this effect anything in anyway?

Same goes for - address varchar(50) and say that no address ever reachs 50?

Quick & Easy MySQL Sync?
Does anyone know of any quick and easy way to sync a local development mysql server to a remote server and back. I'd like to be able to optionally sync either direction?

Quick Mysql Auto_increment Question
i noticed that after adding some test data into my table the autoincrement is working fine. but after i delete them, even though the table is empty again the new data i put in will have primary key thats in auto_increment as if the old data was there.

for example i added in 2 rows. now i have an id 1 and id 2. i delete them and the table has no rows. but now i add another two rows after wards and they get assigned id 3 and id 4. is there anyway for me to make the counting start over??? thanks for any replies or thoughts on this.

Quick Question About Reseting Demo Database?
I would like to set up a cron job to reset my demo database on a regular basis. Is there a way to export an sql file via phpmyadmin, and run it as a whole via the PHP mysql commands? The only way I can see to do it, I have to split up the exported sql from phpmyadmin into individual queries. I'd like to just process the entire .sql file at once, as it would make maintenence much easier.

Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria?

$query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";

LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want.

The tables are:
table services
service_id
service_name

table services_provided
service_id
service_date (date field)
cust_id
service_quantity

I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time

The query:

SELECT service_date, service_name, service_quantity
FROM services
LEFT JOIN services_provided ON services_provided.service_id = services.service_id
WHERE cust_id = $cust_id
AND MONTH(service_date) = 10
AND YEAR(service_date) = 2007
GROUP BY service_id
ORDER BY service_id
(Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.)

I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table.

But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results.

Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.

Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results:

QUERY 1:
SELECT COUNT( o.orderID )
FROM order o
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 161

QUERY 2:
SELECT COUNT( o.orderID )
FROM order o
LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 175

QUERY 3:
SELECT COUNT( o.orderID )
FROM order o, credit_card cc
WHERE o.orderID = cc.orderID
AND DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 157

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's
the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift
and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order.

now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it
does work.
Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there
is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

Quick "WHERE"
Part of a WHERE criteria I'm trying to construct needs to return only those records where a date field in my adkey table contains a date older than a user-specified date (submitted as variable {sRemoveDate} from a web-page form). The following works:
CODEWHERE
(adkey.D_RemoveDate < '{sRemoveDate}' Or
adkey.D_RemoveDate is null)

Joining Data (inner Join / Self Join?)
I am relatively new to php/mysql and I am having a problem figuring out how to do a join. I have a database with a person's name and each person has an ID. I want to be able to add their relatives by typing only their ID.

For example if person 1's descendant was person 37, I want to be able to enter that in the DB and then run a query on person 1's page so that when I have 37 entered as his descendant it will query the DB for his name and print his name but not the ID.

Using LEFT JOIN Instead Of A Equi-JOIN
I have a SQL statement in some code I'm trying to get my head around.. I havent used SQL that much so I assume this is a newbie question: Why would someone use LEFT JOIN if they can simply construct the statement with equi-JOIN? The first statement uses left joins and the 2nd is my reconstruction using equi-JOINs.. so far they produce the same results (however it could be I dont have the right kind of test data) So to summarize my questions: Why do it using LEFT JOINS which I personally find harder to read over the equi-JOIN, 2nd Do they acutally produce the same result everytime?
1st (LEFT JOIN)
-------------------------------------------------------------
SELECT action.action,
summary.gatekeepercl,
branch.branch
FROM summary
LEFT JOIN action ON summary.action=action.id
LEFT JOIN branch ON summary.branch=branch.id
WHERE summary.gatekeepercl IN (506100,506101)
2nd (equi-JOIN)
---------------------------------------------------------------

SELECT action.action,
summary.gatekeepercl,
branch.branch
FROM summary, action, branch
WHERE summary.action=action.id
AND summary.branch=branch.id
AND summary.gatekeepercl IN (506100,506101)

JOIN Syntax (INNER JOIN), Self Join
I want to find the most recent purchase for each customer(see below) and I tried:

SELECT A.*
FROM mytable A INNER JOIN (
SELECT full_name, Max(entered_when) AS entered_when FROM mytable GROUP BY full_name
) M ON A.full_name=M.full_name AND A.entered_when=M.entered_when
;

I got a syntax error:
ERROR 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 'SELECT full_name, Max(entered_when) AS entered_when FROM mytable

My data looks like:
full_name  product_purchased  entered_when
phil          shoes             20050227121012
mary          purse             20020212000000
phil          socks             20021226101423
mary          bra               20020111000000

(I just want to get phil's shoes and mary's purse.)

What should I be doing?

Need Help With Join And Values In The Join
I set up a join using two tables (description and product). I have multiple products that use the same description. I also have different material types for the products. The SQL is "SELECT descripID, subCategory, ProdID, subCat, materialType FROM products, descriptions WHERE subCategory = subCat".

from the description table:
- descripID
- subCategory

from the product table:
- ProdID
- subCat
- materialType

What I want to do is get the descripID and materialType into a new table. There is going to be multiple descripIDs that match multiple materialTypes. I only want one materialType to be with a particular DescripID.

for example:

descripID: 01
materialType: 01

descripID: 01
materialType: 02

descripID: 02
materialType: 01

descripID: 03
materialType: 03

I hope this helps.

Here is some of the output I currently have:

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00024
Prod Material: 01

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00025
Prod Material: 01

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00026
Prod Material: 03

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00027
Prod Material: 02

DescripID: 0024
Desc Subcat: LINSEED OIL
Prod Subcat: LINSEED OIL
ProdID: 00028
Prod Material: 02

To JOIN Or Not To JOIN... Or Am I Missing Something...?
Right, I was always under the impression that it was 'better' to use JOINs, partly because it is 'faster'.

I'm now wondering if that is simply a myth.

Take these two SQL statements:

SELECT DISTINCT p.ProductID, p.Image, p.Price
FROMproducts AS p
RIGHT JOINcategory_links AS c_l
ONc_l.ProductID= p.ProductID
INNER JOINcategories AS c
ONc.CategoryID= c_l.CategoryID
RIGHT JOINbrands AS b
ONp.BrandID= b.BrandID
RIGHT JOINsize_links AS s_l
ONs_l.ProductID= p.ProductID
INNER JOINsizes AS s
ONs.SizeID= s_l.SizeID
RIGHT JOINcolour_links AS co_l
ONco_l.ProductID= p.ProductID
INNER JOINcolours AS co
ONco.ColourID= co_l.ColourID

SELECT DISTINCT p.ProductID, p.Image, p.Price
FROMproducts AS p,
category_links AS c_l,
categories AS c,
brands AS b,
size_links
AS s_l,
sizes AS s,
colour_links AS co_l,
colours AS co
WHEREc_l.ProductID= p.ProductID
ANDc.CategoryID= c_l.CategoryID
ANDp.BrandID= b.BrandID
ANDs_l.ProductID= p.ProductID
ANDs.SizeID= s_l.SizeID
ANDco_l.ProductID= p.ProductID
ANDco.ColourID= co_l.ColourID

The first one uses JOINs and the second simply uses WHERE. As a matter of information, both have additional WHERE details added to refine the search.

I'm using a fast PC, and there are only 14 products in the database (and not all that much data in the other tables). However, I was getting REALLY slow script execution, and I traced it to the SQL query.

Running the first one takes an average of 7 seconds. Running the second query takes less than 1 second. It's almost instantaneous in fact.

This kinda tells me NOT to use JOINs... and to stick with WHERE for this. But in that case, I am left confused as to where it is appropriate to use JOINs and where it isn't...? I did a couple of hours of Googling and didn't clear the matter up. All the articles I found pointed towards using JOINs.

Obviously at the end of the day I'm going to use the faster method. Plus, after thinking about it, there's a lot more work being done with the JOINs, is there not...?

I'd love to know what some other people think about this, and whether I'm just totally out on my JOIN usage or if other people are using them in the same situations.

::] krycek [::

Inner Join Or Left Join?
What I am trying to do is this....

English Table:
Number Text Roman
1 One I
2 Two II
3 Three III
4 Four IV

Hindi Table:
Number Text
2 Do
3 Teen
4 Char
5 Panch

Expected Results where number is 2
Text Roman
Two II
Do II

JOIN Within LEFT JOIN
I am using MySQL 3.23.54. I have the following table structure.

FORMS
form_id (PK)
form_name

STAFF ASSIGNMENTS
staff_assignment_id (PK)
form_id (FK)
staff_id (FK)

STAFF
staff_id (PK)
first_name
last_name

For each record in FORMS there may be zero, one or multiple records in STAFF ASSIGNMENTS. I need to perform a left join from FORMS on STAFF_ASSIGNMENTS. When there is a record in STAFF ASSIGNMENTS, I need to perform a join with STAFF to retreive staff name. Here is my attempt at the query.

SELECT forms.form_id, forms.form_name, staff.first_name, staff.last_name
FROM forms LEFT JOIN staff_assignments ON forms.form_id =
staff_assignments.form_id (JOIN staff on staff_assignements.staff_id =
staff.staff_id)

How do I need to write the query?

LEFT SELF Join -- LEFT Join On Same Table
Is it possible to LEFT JOIN a table to itself?

I want to see all records in a table where the year of the sales date is
2003 and where the salesman sold an item to a customer to which he did not
sell that item to in 2002.

The following is an example table (SALES):

+----+-----+------+------------+--------+------+-----+
| ID | SLM | CUST | DATE | ITEM | COST | QTY |
+----+-----+------+------------+--------+------+-----+
| 1 | 1 | AAA | 2002-01-01 | APPLE | 1.00 | 2 |
| 2 | 1 | BBB | 2002-01-01 | APPLE | 1.00 | 2 |
| 3 | 1 | CCC | 2002-01-01 | PEAR | 1.00 | 2 |
| 4 | 1 | AAA | 2002-01-01 | PEAR | 1.00 | 2 |
| 5 | 2 | AAA | 2002-01-01 | APPLE | 1.00 | 2 |
| 6 | 3 | CCC | 2002-01-01 | BANANA | 1.00 | 2 |
| 7 | 1 | AAA | 2003-01-01 | APPLE | 1.00 | 2 |
| 8 | 2 | AAA | 2003-01-01 | APPLE | 1.00 | 2 |
| 9 | 3 | CCC | 2003-01-01 | BANANA | 1.00 | 2 |
| 10 | 1 | AAA | 2003-01-01 | BANANA | 1.00 | 2 |
| 11 | 3 | CCC | 2003-01-01 | APPLE | 1.00 | 2 |
+----+-----+------+------------+--------+------+-----+

What I want to see in my result is that in 2003 Salesman 1 sold bananas to
customer AAA and salesman 3 sold apples to customer CCC. I want to see
these because these salesmen did not sell these customers these items in 2002.

(select Where) Join OR (select Join) Join
which one is better, (select where) join OR (select join) join ?!

I can join two table with select and where, also i can do the same with join keyword. The result is same but which one is better?
I know that joining with join keyword is better for explicit code but what about performance?

Do I Need A Join?
I have a PHP address book that stores birthday information (in addition to standard stuff like name, address, etc.) in a MySQL database. The address book works fine, but I want to add the ability to send email reminders. BUT, and this is what makes things more interesting, I also want to be able to do reminders that are not related to any particular contact. As an FYI, the database currently has two tables: <users> and <contacts>.

I'll try to briefly explain, please bear with me. I want to add a <reminders> table to the database so users can specify an event date and have an email sent to them prior to said date. The reminders can be independent of their <contacts> data (e.g. a user wants a reminder of an upcoming massage therapy appointment) OR the reminder could be for their friend John Doe's birthday (which IS stored in the <contacts> table).

How can I setup a <reminders> table that can stand on its own but also reference birthday info in the <contacts> table? Feel free to point me towards any appropriate tutorials, howto's, etc.

More JOIN Fun (Or Is It?)
Yes, I've read about JOINs, albeit after coding for a couple of years
already using queries like the following:

"SELECT m.LastName, m.FirstName, o.Address FROM members m, offices o WHERE
o.City='$vCity' AND m.Status<>'Retired' AND m.Status<>'Suspended' AND
o.MemberID=m.MemberID ORDER BY LastName,FirstName"

($vCity comes from a drop-down list of city names)

Now... everything works just fine, and has for a long time.... but reading
this NG, and some online articles about JOINs has be wondering/perplexed...

What I am hoping for is someone who knows MySQL and is really bored to
perhaps explain why the above query is NOT a 'real' join (which i don't
think it is), and why that's necessarily BAD.

How can I take that query and turn it into a 'real' join, and more
importantly, why should I? What is 'wrong' with queries like the one
above?

JOIN Using NOT IN
You may recongize this as I posted a similiar problem. Please let me know if there needs to be clarification on this.

Here is the example:

You've got Dan who started Group A and in Group A is

Dan, Rudy and Longneck.

Rudy Starts Group B and has invited

Kristen and Dave.

Now Rudy is their "ref" When Running this Query I would want a list of pople that the Members of Group A have invited but are not in Group A.

So the query would result in Kristen and Dave coming up.

Now lets say that Dave joins Group A. I no longer want him to show up in the results.

Here is the Table Breakdown

PHP

--
-- Table structure for table `GroupUsers`
--

CREATE TABLE `GroupUsers` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `GroupID` int(11) unsigned NOT NULL default Ɔ',
  `UserID` int(11) unsigned NOT NULL default Ɔ',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

--
-- Dumping data for table `GroupUsers`
--

INSERT INTO `GroupUsers` VALUES (1, 1, 1);
INSERT INTO `GroupUsers` VALUES (2, 1, 2);
INSERT INTO `GroupUsers` VALUES (3, 1, 3);
INSERT INTO `GroupUsers` VALUES (4, 1, 4);
INSERT INTO `GroupUsers` VALUES (5, 2, 3);
INSERT INTO `GroupUsers` VALUES (6, 2, 4);
INSERT INTO `GroupUsers` VALUES (7, 2, 5);

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

--
-- Table structure for table `Groups`
--

CREATE TABLE `Groups` (
  `GroupID` int(11) unsigned NOT NULL auto_increment,
  `GroupName` varchar(75) NOT NULL default '',
  PRIMARY KEY  (`GroupID`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

--
-- Dumping data for table `Groups`
--

INSERT INTO `Groups` VALUES (1, 'Group A');
INSERT INTO `Groups` VALUES (2, 'Group B');

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

--
-- Table structure for table `Users`
--

CREATE TABLE `Users` (
  `UserID` int(11) unsigned NOT NULL auto_increment,
  `firstname` varchar(25) NOT NULL default '',
  `ref` int(11) unsigned default NULL,
  PRIMARY KEY  (`UserID`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

--
-- Dumping data for table `Users`
--

INSERT INTO `Users` VALUES (1, 'Dan', NULL);
INSERT INTO `Users` VALUES (2, 'Longneck', 1);
INSERT INTO `Users` VALUES (3, 'Rudy', 1);
INSERT INTO `Users` VALUES (4, 'Kristen', 3);
INSERT INTO `Users` VALUES (5, 'Dave', 3);

Currently

PHP

SELECT DISTINCT U.userid
FROM Users U
INNER
JOIN GroupUsers GU ON GU.UserID = U.UserID
INNER
JOIN GroupUsers GU2 ON GU2.UserID = U.ref
AND GU2.GroupID =1

Brings back Everyone Except Dan when I would like it to Only return Users referred by Group A Users but are not in Group A. This would result in the Query Bringing back Dave only. Please let me know if you need more clarifaction

Self Join?
I have the following table

CREATE TABLE `users` (
`userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 16 ) NOT NULL ,
`approved` TINYINT NOT NULL
) TYPE = MYISAM ;

approved always switches 0/1

My simple Question is:
How can I get the following to work?

SELECT COUNT(u1.userid) as user_approved, COUNT(u2.userid) as user_not_approved FROM users u1, users u2 WHERE u1.approved = 1 AND u2.approved = 0

Help With A Join
i have two tables, events and schedules

i need to output all the events with a current schedule, but some events have multiple schedules and therefore im getting duplicate rows in my results

im using this query currently:

SELECT * FROM events INNER JOIN schedules ON schedules.event_id = events.event_id AND schedules.recursuntil >= NOW() ORDER BY event_title ASC
can anyone help with the right way to do this so that i only get one record from events even if there are three matches in schedules?

Join Where
This query is suppoused to give the debt value "venta" nro 7.

It works perfect, except for the last line, the "WHERE ventas.id = 7"

SELECT ventas.id, ventas.importe_acordado - SUM(pagos.importe) as valor_dinamico
FROM ventas
LEFT OUTER JOIN pagos ON pagos.venta = ventas.id
GROUP BY ventas.id
WHERE ventas.id = 7

Join!
How would I make a JOIN work here? The whole something.somethingeelse is confusing to me.

PHP

<?php

$result8 = mysql_query("SELECT * FROM playlist ORDER BY id DESC LIMIT 5")
or die(mysql_error());
while($row8 = mysql_fetch_array( $result8 )) {
    $game=$row8['game'];
    $system=$row8['platform'];

$result4 = mysql_query("SELECT * FROM games WHERE boximage!='' AND game NOT LIKE '!%' AND game = '$game' AND system='$system' LIMIT 5")
or die(mysql_error());

while($row4 = mysql_fetch_array( $result4 )) {
    $image=$row4['boximage'];
    $game=$row4['game'];
$console=$row4['system'];

             echo "
<td width=颼' align='center' valign='top'>
<a href='http://www.gameanyone.com?p=play&game=$game&cc=$console'>
<img src='images/$image' border=Ɔ' height=飮' width=飆'></a><br>
<a href='http://www.gameanyone.com?p=play&game=$game&cc=$console'>$game</a></td>";

}}

?>

SQL Join
I've been looking at this a while, and now my minds just gone blank. I have three tables, two of which Join correctly, however I cannot seem to join HC_PIM_WebParts_Attributes_Lookup with the HC_PIM_WebParts_Attributes.id column = HC_PIM_WebParts_Attributes_Lookup.AttributeID as this always returns null.

mysql> DESCRIBE HC_PIM_WebParts;
+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| WebPartID | int(11) unsigned | NO | PRI | | auto_increment |
| Name | varchar(50) | YES | | ||
| Description | varchar(255) | YES | | | |
| AttributeLookupID | int(11) unsigned | YES | | ||
| WebPartTemplate | text | YES | | ||
| DataTables| varchar(100) | YES | | ||
| URL | varchar(255) | NO | | ||
+-------------------+------------------+------+-----+---------+----------------+

7 rows in set (0.00 sec)

mysql> DESCRIBE HC_PIM_WebParts_Attributes;

+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| id| int(11) unsigned | NO | PRI | | auto_increment |
| AttributeLookupID | int(10) unsigned | NO | MUL | 0 ||
| AttributeKey | varchar(255) | NO | | ||
| AttributeValue| varchar(255) | YES | | ||
| Description | varchar(255) | YES | | ||
| DataTypeID| int(11) | NO | | ||
| Internal | enum(Ƈ',Ɔ')| YES | | 0 | |
+-------------------+------------------+------+-----+---------+----------------+

7 rows in set (0.00 sec)

mysql> DESCRIBE HC_PIM_WebParts_Attributes_Lookup;............

Help With Join
I'm trying to pull data from 2 tables, all the data from table playerinfo and the row rank from table ranks matching them by name in both tables

PHP

$pi = mysql_query("SELECT * FROM playerinfo  where name.ranks = name.playerinfo");

PHP

CREATE TABLE `ranks` (
  `rank` int(11) NOT NULL default Ɔ',
  `name` varchar(255) NOT NULL default '',
  `kills` int(11) NOT NULL default Ɔ',
  `deaths` int(11) NOT NULL default Ɔ',
  `score` int(11) NOT NULL default Ɔ',
  `mvp` int(11) NOT NULL default Ɔ',
  `games` int(11) NOT NULL default Ɔ',
  `gdi` int(11) NOT NULL default Ɔ',
  `nod` int(11) NOT NULL default Ɔ',
  PRIMARY KEY  (`name`)
) TYPE=MyISAM;

PHP

CREATE TABLE `playerinfo` (
  `score` int(5) NOT NULL default Ɔ',
  `name` varchar(80) NOT NULL default Ɔ',
  `team` varchar(80) NOT NULL default Ɔ',
  `kills` varchar(80) NOT NULL default Ɔ',
  `deaths` varchar(80) NOT NULL default Ɔ',
  `ping` varchar(80) NOT NULL default Ɔ',
  `timeplayed` varchar(80) NOT NULL default Ɔ'
) TYPE=MyISAM;

Odd Join
I am developing a website and it runs perfectly on the test server.
But as soon as I moved it to the developer server I started getting an odd error.
I have included the full query though it does not seem to pertain to the error.
The error I am recieving is.

#1064 - You have an error in your SQL syntax near 'ON news.newsId=authorNews.newsId LIMIT 0, 30' at line 1

gotta love that 1064 error. lol.
I have run the query and it works perfectly on the test system.
I thought maybe the db tables were set up wrong but the column names etc are exactly the same.
But for some reason the server doesn't like the "ON " part of the join.

//This is the section pertaining to the error
SELECT DISTINCT news.newsId, news.headline, news.posted
FROM news
JOIN authorNews ON news.newsId = authorNews.newsId

//This section works perfectly on the server but when the ON is added it fails to recognize
SELECT DISTINCT news.newsId, news.headline, news.posted
FROM news
JOIN authorNews

//This is the original query
SELECT DISTINCT news.newsId, news.headline, news.posted
FROM news JOIN authorNews ON news.newsId=authorNews.newsId
JOIN authors ON authorNews.authorId=authors.authorId
JOIN paperNews ON news.newsId=paperNews.newsId
JOIN paper ON paperNews.paperId=paper.paperId
JOIN sectionNews ON news.newsId=sectionNews.newsId
JOIN sections
ORDER BY news.posted DESC LIMIT 25, 25

JOIN
SELECT * From Schedule;
+---------+--------+-------+------------+-----------+
| ScheduleNo| custNo | EmpNo | Date | Time |
+---------+--------+-------+------------+-----------+
| 2 | 2 | 3 | 2006-01-01 | 1:00:00 |

I want to be able to select ScheduleNo 2, the customers details such as firstname and lastname based on the custno and the empno based on the empno. I also need to select where the date is 2006-01-01. I just want a simple join that works with three 3 or more tables...

Join
I am new to myssql and am using a mysql database with the following
tables

Table Itinerary
itinerary_id int(4)
ship_id int(4)
itinerary_title varchar(10)
itinerary_duration varchar(10)
date_from date
date_to date
from_port_id int(4)
to_port_id int(4)
port_destinations text
details text

Table Country
country_id int(4)
region_id int(4)
country_name varchar(30)

Table Region
region_id int(4)
region_name varchar(30)

Table Port
port_id int(4)
country_id int(4)
port_name varchar(30)
port_desc text
image_path varchar(50)

in my html form, I choose a field which fetches the data from the
region table. From the region table I select the region_id and then
find the corresponding record(country_id) in the country table. I now
use the country_id to find the port_id from the port table. Once I have
this port_id, I can perform the desired query and fetch the data from
the itinerary table. I am able to do this using three queries but that
takes a lot of time.