MYSQL :: JOIN And Logic

LEFT JOIN Logic
I have several database that I need to link with a left join. I thought and thought on this and I believe the method I am going to use is the best. Basically I have 3 Tables that need to use a LEFT JOIN. A Members Table, A Group Table and a RSVP table.

When a member RSVP it insert a record into the RSVP TAble with MemberID, EventID and Their Status.

I am setting up a form where the Event Planner Can Change Members RSVP status. I made it very simple with 2 Drop Down Boxes. One that has a list of everyone that has RSVPed Yes. The Planner can just select the name click go and the Member will automatically be set to NO on RSVP.

The other Drop Down will be a List of Members names that have RSVPed No,Like, Not Likely or Nothing at all. (If they have no reponse then there won't be a record for them in the RSVP table)

Here is a sample of my non-working code.

PHP

SELECT rsvp.attending, leaguemembers.nick, members.firstname, members.lastname, members.memberid FROM members, leaguemembers LEFT JOIN rsvp USING (memberid) WHERE rsvp.eventid = 'xxx' AND rsvp.memberid = members.memberid AND members.memberid = leaguemembers.memberid ORDER BY members.firstname

This Query Only Returns A Result for people that have RSVP something and not members that have not responsed.

HELP With SQL Logic Please!!!
I'm programming in PHP. I have a mysql table with 4 columns:
int id
int initiator
int target
tinyint ok

With maximum query performance, how can I achieve the following pseudo code in my SQL statements, where I have 2 PHP variables:

if (target == $me) and (initiator == $t) and (ok=0) then ok=1
if (target == $t) and (initiator == $me) and (ok=0) then do nothing
if ((target != $me) and (initiator != $t)) or ((target != $t) and (initiator != $me)) then insert a new row with:
initiator = $me
target = $t
ok = 0

So can this be achieved through minimum SQL queries, for performance purposes?

SQL Logic
I am trying to get a handle on selecting the top 10 entry and exit pages of my apache access_log database. I log the url, host name etc. This is what I have for top 10 popular URL's if its any help:

SELECT `url`, COUNT(`url`) AS `num` FROM `access_log` WHERE `date` = '0000-00-00' GROUP BY `url` ORDER BY `num` DESC LIMIT 0, 10;

Logic
I have an orders table. The orders table has ‘clientid’ as a foreign key, which links back to the client. The orders table has ‘orderid’ as its primary key, and their also exists a field called ‘deliverydate’.
Every client can have multiple orders, so their client id may appear in the orders table multiple times. What I want is a list of the client ids and a single order id for each client. Since clients can have more than one order, I only want to bring back the order with the earliest delivery date.

So far, I have something like this:

Select clientid, orderid, min(deliverydate)
From orders
Group by clientid, orderid
Unfortunately, this brings back a list containing client id’s multiple times, because the min() aggregate function forces me to group by clientid and orderid. I only want the client id to appear once, and with that the orderid of the clients order with the earliest deliverydate.

Query Logic
Getting the hang of writing queries. I have a table that has an index that will have many rows. In a field I have a status that will change based of an action. I would like to query against that event. If item "Z" has a status of "C" and changes to "E". If I query I will pull back two rows but I only want to see the result if it changes from "C" to "E". How can I programatically achieve this?

If/then Logic In Query
Let's say I have a table that is called "first_names". I have two columns: "key" and "name".

Is there a type of query where I can take a name, say, "Andrew," and tell the database to select the key for that name, but if it doesn't exist, insert the name into the table and then select the key for the row it just inserted?

Logic Or Syntax
Porting site from TCL/Illustra to PHP/MySQL. Tried multiple Forum searches but nothing came up that fit my problem. I'm more designer than programmer so I'm not sure if my error is in syntax or logic or 'other'.

What I'm trying to do is pull all the records from a table named glossary (term_id, term, def_1, def_2, timestamp). Some rows only have 1 definition, some have 2. I want to format those rows that have 2 definitions differently than the those entries that only have 1.

The query successfully returns all of the terms but formats all of them according to the ELSE part of the statement (only FOUR rows out of 311 have 2 definitions?

WHERE Clause AND/OR Logic
It the query OK?

SELECT * FROM table WHERE x=1 AND y=1 AND (X=1 OR Y=1 or Z=1)

Help With Model - Logic & Indexes
I'm designing my first db application (accommodation) and I would like some help with a few things.
Trying to learn too much over a very short period time has proven slightly overwhelming.
Any feedback would be appreciated.

Ok, first of all, I'm using MySQL v. 4.0.26 because that's what my hosting provider provides.

1. I'd be great if I could have some feedback on my db model attached.
Have I overlooked something that might lead to complications?

2. I'm using MyISAM tables because I'll definetely need to be able to search for accommodation names, and maybe for keywords in their description too.
Although I need the search capabilities of MyISAM tables I'm trying to figure out whether transactions are more important due to the fact that the accommodation data is split amongst several tables.
Any ideas? Do you think it'd be better if I combined MyISAM tables with InnoDB tables, or is that going to be a problem?

I'm also thinking of adding "record locking" by creating an extra table with the acc_id of the records that are being edited. If another user selects that particular record while it's locked, he will get a "record being edited" message.
Is this the right way to go about this?

3. What is the best way to index these tables?
For most of the "accommodation entry" tables (right side of model) the acc_id FK can be created as a PK.
That will index it automatically (note: I know that MyISAM doesn't support FKs).

What about the following tables though?
acc_entry
acc_entry_facilities
acc_entry_room_fac
acc_entry_sports
The last 3 tables have 2 columns consisting of 2 FKs. I was thinking that I could just declare both of these as PKs.
The 1st table though has multiple FKs and I'm thinking to follow the same method - and creat all of the FKs as PKs.
Please refer to create.sql file attached for this.
Is this correct?

What about the acc_entry_distances column? Would I benefit from a dirrenent kind of index in order to find i.e.
all accommodation 10m from the beach and 100m from town.

Is the following create statement the right one?

/* I will only need to search for the distance
from town, airport, port, sea and beach */

CREATE TABLE acc_entry_distances (
acc_id TINYINT UNSIGNED NOT NULL,
town CHAR(10) NOT NULL,
airport CHAR(10) NOT NULL,
port CHAR(10) NOT NULL,
village CHAR(10) NOT NULL,
sea CHAR(10) NOT NULL,
beach CHAR(10) NOT NULL,
market CHAR(10) NOT NULL,
pharmacy CHAR(10) NULL,
PRIMARY KEY(acc_id),
INDEX town_index (town, airport, port, sea, beach),
INDEX airport_index (airport, port, sea, beach),
INDEX port_index (port, sea, beach),
INDEX sea_index (sea, beach),
INDEX beach_index (beach)
);

I'm including below some untested queries that I will definetely need to perform:

/* get all 5* "Hotel" records (class_id=5 and type_id=1) */

SELECT *
FROM acc_entry as en,
acc_entry_data as da,
acc_entry_data as di
WHERE en.type_id = 1
AND en.class_id = 5
AND en.acc_id = da.acc_id
AND di.acc_id = en.acc_id;

/* get all Hotel records with a swimming pool, a restaurant
and a gym (type_id=1, fac_id=3, fac_id=5, sport_id=4) */

SELECT *
FROM acc_entry as en,
acc_entry_data as da,
acc_entry_distances as di,
acc_entry_facilities as fa,
acc_entry_sports as sp
WHERE en.type_id = 1

AND en.acc_id = fa.acc_id
AND fa.fac_id = 3
AND fa.fac_id = 5

AND en.acc_id = sp.acc_id
AND sp.sport_id = 4

AND en.acc_id = da.acc_id
AND en.acc_id = di.acc_id;

Query Vs Logic Speed
I have a website which uses some fairly large tables ( > 300,000 rows ). As you can imagine accessing these tables can often be very slow. I am attempting speed up my sql and was wondering if, using php, it is faster to perform an sql query or approximately 10 lines of logic?

What I am trying to do is, instead of querying the table thousands of times and performing my calculation on each small chunk, I would like to try to get the whole table at once, sort it by the ID's and just iterate over it and perform the calculations I need.

Would this speed things up? how much if any? and is it worth the effort?

Logic And Efficiency Question
I'm using perl, MySQL and perlmagic to do some work on images from a MySQL database and would like some feedback as to the most efficient way to go about something, I'll do my best to explain. I don't believe this qestion matters much what language or tools I'm using, just the fact that it is a matter of doing a duplicate database query or not.

I have multiple images stored in a database, let's say I need to display 10 of them depending on some variable but some of those images may be duplicates of ones already read in. Should I save copies of the images in memory and check before doing another query to the database, and if I already have the image do not do the query and use that copy, or just go ahead and read it in with another query. Code:

Date Range Logic
I attempting to  returing records that fall within a given date range and any overlapping dates:

For example:
input parameters are:
startDate= 2005-10-31
endDate = 2005-11-30

If I have the following data in my table:

cust dateBeg     dateEnd
------------------------
1    2005-10-27  2005-11-15
2    2005-11-1   2005-11-30
3    2005-11-20  2005-12-20
4    2005-12-1   2005-12-10

I want to retrieve cust 1,2,3, but not 4
So far this logic retrieve all dates that fall in between

(dateBeg >= startDate and
dateEnd <= endDate)

How can I include ovelapping dates?

Import Logic Code
I have been using PHPmyadmin to do daily imports...

1. empty the table...
2. import the text file

I'd like to FTP all my files up and then create a statement that will

do this for several tables...

This will both automate the process and enable to deal with a SSH ftp connection instead of the 'clear' phpmyadmin...

Can anyone point me to a resource for this logic OR a way to grab what PHPmyadmin is doing????

Logic Of Full Text Search
I am trying to understand the logic of full text search in mysql. I'm not using mysql 4. The search work OK, be it thast I get hits on certain words, whilst other words are discarded for some reason or other. Why is that. An example: I search in a text field for the word organisation. I get hits. When I search for the word scenario nothing is found. But I can see the word in the paragraphs by myself?

The Logic Of Full Text Search
I am trying to understand the logic of full text search in mysql. I'm not using mysql 4. The search work OK, be it thast I get hits on certain words, whilst other words are discarded for some reason or other. Why is that. An example: I search in a text field for the word organisation. I get hits. When I search for the word scenario nothing is found. But I can see the word in the paragraphs by myself?

Trouble Understanding Returned Resource Logic
I am unable to access a resource using "mysql_fetch_row". There is a resource present after executing a query, I've verified this. The results of "mysql_num_fields" is 2 (which is correct there are two columns in the table) and the results of "mysql_num_rows" is 0. Now how can that be? No rows yet two fields. With zero rows, my "mysql_fetch_row" function will not work. So, what's up with this? Any comments or ideas?

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:

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.

Inner Join
I am new to this type of query and while I've found a lot of help on the forums, I am simply not getting it to work.

Here is my attempt currently:

Join?
Two tables. First table has columns ID and TEXT. Second table has columns ID1 and ID2.

I would like to select rows from the second table where ID1 and ID2 are both found in the set of IDs returned by a select on the first table.

To be more specific, I'm selecting IDs from the first table using a full text search on the TEXT column. Then I need to identify rows in the second table where ID1 and ID2 are both found in the IDs from the select on the first table.

Join Possible?
Suppose that I have 2 tables. A table "A" with fields "Brand" and "Spec" and the other "B" with fields "ID_Specs" and "Type".

Example:

Table A
Brand - Spec
1 - "yo"
2 - "hello"
3 - 1
3 - 2
6 - "bye

Table B
ID_SPEC - Type
1 - "kiss"
2 - "kiss1"
3 - "kiss2"

If the field "Brand" of the table A is "3", the field "Spec" is an "ID_Spec" link for the table B.

To save a lot of work, I would like to know if I can make some kind of Join or Temporary table with all the normal fields of the table "A" and the linking for the table B

Example

Table C
1 - "yo"
2 - "hello"
3 - "kiss"
3 - "Kiss1"
6 - "bye"

Inner Join
Here is what I need to do- I have 2 tables related by an ID (int) field. I need to return all fields from one table and none from the second but I need to run a comparison against a field on the second table and if the field data equals 'x' then the corresponding record from the first table should not be returned. Seems simple to just say it, lol. I changed the way I was querying the db in the middle of the development so this particular situation wasn't anticipated when I created the db. Code:

Using JOIN
I was under the impression that the following was going to return all records that exist between the 2 dates mentioned below showing NULL values for the "billing_commnets.comment" column when it is blank. However this is only returing records that have values in both tables that match. Any ideas on what I am doing wrong?

SELECT customer_ads.job_title, customer_ads.start_date, customer_ads.client_id, billing_commnets.comment
FROM customer_ads
JOIN billing_commnets
ON (customer_ads.id = billing_commnets.orderID)
WHERE (customer_ads.start_date between '2007-06-02' AND '2007-06-12')

JOIN
I have 5 tables all containing one field in common call it ARCHIVO, these field the data has a format very simple m9999001.001 with all of them I need to take out all data inner joinning with the FIELD ARCHIVO I can do that, but now I have a new table with the same field ARCHIVO but this time the format for ARCHIVO change!!! instead of the usual one that i describe a few lines before, this time the format came like this m9999001.err and to top it all up this turns out to be a very important data now so I need to get all the data from these table inner joinning with all the other ones

This perhaps is useless but I'll put it anyway :
I try a direct approch and did a SELECT well take a look :

SELECT * FROM proyecto.r501 INNER JOIN proyecto.firmas ON proyecto.r501.archivo = proyecto.firmas.archivo;

I not need to tell you that this effort of my did not work after 10 minutes running came up empty ( I already knew)

So the question is how can I make it work?