MYSQL :: Selecting Distinct Results In A Join

Join 3 Tables - Distinct Results
I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacturers have reported in the given month.

Selecting DISTINCT Except For One
There's this membership script, I am adding usergroups to it and so I made a new field called groups and everything. Now I want to add it to the admin profile update page. I'd like it to be in a dropdown menu and I already know how to do that, using the distinct function and everything. But I'd like the user's current usergroup to be the default on the dropdown menu, so I know which one they are now.

I was going to retrieve the user's current group but if I get the groups from the whole list as well, it would come out twice wouldn't it?

Selecting Distinct
Can someone please tell me how I can do this:

I have an appointment table which has appointment_id, client_id,
appoinment_time (as unix timestamp). Clients can have multiple
appointments.

I want to select all appointments from the appointment table but only
one appointment (the latest one) for clients that have multiple
appointments.

Selecting Distinct Rows
I have a mysql query which I am trying to select distinct information with.

Here is the query:

Selecting The Inverse Of Distinct
I would like to select the fields that exists more than one time.

Description of my table is:

Create table MYTABLE (CLASS_ID char(2),STUDENT_ID char(2));

I need to select the students that are in more than one class!

Selecting A Distinct Query
I want to: select name, address, city, state, zip from client;

However I want to query a distinct address(some of the people work at the same address, and I only want one letter to go to each address.).

Is it something like:
select name, distinct[address], city, state, zip from client;

I know this is not right, but does anyone know this? Thx.

Selecting Distinct Names
I am attempting to create a list of servers that have distinct usernames, and if there are multiple servers with the same name, show the one with the lowest ID.

Here is my existing SQL but the DISTINCT does not work:

SELECT DISTINCT(name),date_generated,location,pass_validation,ID,time_saved FROM SQL_SVA.servers

Selecting Non Duplicate Records Without Using DISTINCT
how not to display duplicate records without using DISTINCT. Say i have a database:

ID Channel Description
41 Africa News Latest news from africa
42 Technology News Latest Tech News
43 PC News Latest PC related news
44 Africa News Latest news from africa

I run the query 'SELECT DISTINCT * FROM Mytbl' and obviously get the results as shown above with 'Africa News' appearing twice as the primary key ID makes it unique.

How can i return only one of the Africa entries in the results while still having the primary key value returned as well?

Unfortunately with this particular app it is inevitable that duplicate values will occasionally be added to the database but i never want duplicates to be returned from a search query. Can this be done?

Many To Many Select Using DISTINCT Or Not Return The Same Results
I was on MySQL website looking for an answer, but still need assistance please.

I want to do the opposite of the following:

SELECT * FROM myTable WHERE id IN(1,2,3,4,5) ;

I want to use my array(1,2,3,4,5) to exclude items in the result set, not include them.

So, I want to do something like this:

SELECT * FROM myTable WHERE id NOT_IN(1,2,3,4,5) ;

SELECTing Distinct Values Over Multiple Columns
I have a table setup for populating a drop down menu.

CREATE TABLE catagory
( ID char(10) PRIMARY KEY NOT NULL,
cat1 varchar(50),
cat2 varchar(50),
cat3 varchar(50) );

Now each row is for one client to populate so that he can be listed under multple catagorys.

The problem comes in that I need DISTINCT catagories but say the client with ID 00000-0001 has a cat1 of greek, and a cat2 of pizza, where on the next, a client with a different ID has a cat1 of italian, but a cat2 of pizza. How would I word the query so that I can return distinct results from both rows AND columns or is this a problem on the PHP side of things (how i'm coding the page)?

as it stands right now, my query of:
SELECT DISTINCT cat1, cat2, cat3 FROM catagory;
isn't working very well (I get all the results, as if the DISTINCT wasn't there)

Selecting Unique Values From A Table (NOT DISTINCT!)
Lets say we have a table that has the field photo_id and has values of it many times. For example 245 can exist any number of times (1, 5, 8, e.t.c.).

I want to select the values that exist in the table ONLY ONCE. Please note that i dont want to select each value once (using DISTINCT), that would return all the values that exist in the table.

For example lets say the table has the following values:

245
563
776
224
563
563
776
776
776
776

The query i want to make should return only the values 245 and 224 as 563 and 776 exist many times in that table.

Select Distinct Results Without Throwing Away Data?
id series_id image date
6 2 1191956895.jpg 2007-10-09
5 3 1191956773.jpg 2007-10-09
4 2 1191451083.jpg 2007-10-03
3 1 1191451026.jpg 2007-10-03
2 1 1191451012.jpg 2007-10-03
1 1 1191450488.jpg 2007-10-03

if you take a close look at the code above you might be able to see that i have these images assoicated with one of 3 series. id like to be able to select the most recent from each one and discard the rest. however a command link distinct on the series_id throws away all the other data around it that i need.

the results would look more like

seires_id image
2 1191956895.jpg
3 1191956773.jpg
1 1191451026.jpg

i imagine this would be much like a forum sorting the most recent topics posted in a forum by finding the most recent posts for each distinct topic.

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

Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value.
example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates).

how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?

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?

SELECT DISTINCT On A Single Field - Full Row Results
I've been trying to figure out how to do this and can't seem to wrap my thoughts around how to do it.

Basically I want to select every row that is distinct based on a single field, like:

Author | Book Name | Book Num
Bob | ThisBook | 1
George | ThatBook | 2
Fred | HisBook | 3
Bob | HerBook | 4

How would I select each author only once, but return the entire row for that unique result? Basically I'd want Bob, George, and Fred to all appear, but Bob to only appear once, not twice.

And this is assuming I can't change the table to be better structured, I don't have direct access to it. I realize how it SHOULD be set up, but unfortunately it is not.

Selecting Multiple Columnn Values Into 1 Distinct/unique List
I have a table with several fields for email (primary email, alternative email, contact email)

Some times, some of these fields will be blank and sometimes the same address will be enter for both primary and contact.

I use this table and these fields to do a mailout but I don't want to mail people twice because there address occurs in more than 1 column.

I need to end up with a list containing all email values from those 3 columns that is unique (no duplicates).

I can do this by checking each one and putting it into a php array but thought there may be a nicer way to do it in the SQL?

Selecting Non Mutual Results
I have a table, such as this:

friends

+--------+----------+
| userid | friendid |
+--------+----------+
| 2 | 7 |
| 2 | 3 |
| 2 | 6 |
| 2 | 8 |
| 2 | 9 |
| 2 | 34 |
| 7 | 2 |
| 34 | 2 |
| 9 | 2 |
| 2 | 47 |
+--------+----------+

and Bill K kindly helped with the code to fine opposite column matches (2
and 7, 7 and 2 for example) but now I want to return the results for a
specific user excluding these opposite column matches. How can I do this?

Selecting Mutual Results
I have a table such as this:

friends

user friend
1 2
1 3
2 4
2 1

how could i select results where user and friend are the same but in
opposite rows (that is, in this example, user 1 and friend 2 (user 2 and
friend 1))?

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

Selecting Results From This Business Week
I have a table which has the column 'date_added'. I need a query which returns all of the results which were added in the current week. By this I dont mean today minus 7 days, I mean monday 00.00am to sunday 11.59pm.

Is there a way of doing this? I've looked into WEEK() and YEARWEEK() but cant think of a way to do this using PHP.

Heres what I've got (probably awfully wrong..

PHP

mysql_query("SELECT * FROM table WHERE date_added BETWEEN WEEK(now(), 0) AND WEEK(??)");

Selecting Differnt Results From The Same Table.
I have am using mysql.

I wish to select from a table x number of result that match the same criteria then continue select that same x number that match the same criteria but differnt from the first criteria.

example table.

x = 2
medicine type
asprin    I
tylenol   I
advil     II
creatin   III
salvation  R
Redemption R
painkiller I

results would look like

asprin      I
tylenol     I
advil       II
creatin     III
Salvation   R
Remdemption R

It needs to go through the table selecting up to X and then continue on to the next possible value. Does anyone have any suggestions?

Outer Join And Distinct
I just started building a website and use MySQL as my backend database. I'm having a little trouble creating an SQL query that will get the correct information out.

Here's my problem:

I have 3 tables:
people: stores the name of people who can vote
votetype: stores the type of votes there are (In favor, Against, Withheld)
decisions: the dicisions that were voted on

Then I have another table that links them all together called votes. Votes has three columns: personID, typeID and decisionID. personID and decisionID are primary key. All are foreign key to obvious tables.

I want to retrieve for a given person the votes he made for all decisions. So: a list of all decisions with the vote he or she made and a NULL if there is no vote.

So far I have this query:
SELECT DISTINCT d.ID, d.name, d.date, v.typeID AS vID, t.name FROM decisions d, votetype t LEFT OUTER JOIN votes v ON v.decisionID = d.ID WHERE (v.personID=1 OR v.personID IS NULL) AND (v.typeID=t.ID OR v.typeID IS NULL) ORDER BY d.date;

For some reason the DISTINCT doesn't work and I get a decision that is not voted for three times (once for each type while v.typeID column is NULL).

How can I fix this so each decision only shows up once?

Jurgen

-------------------------
I've found a solution:

SELECT DISTINCT d.ID, d.name, d.date, v.typeID AS vID, IF(v.typeID IS NULL,NULL,t.name) AS stem FROM decision d, votetype t LEFT OUTER JOIN votes v ON v.decisionID = d.ID WHERE (v.personID=1 OR v.personID IS NULL) AND (v.typeID=t.ID OR v.typeID IS NULL) ORDER BY d.date;

Distinct And Join Query In MySQL
I have a query question, it may be really easy but somehow I cannot get it

What I have is a table with columns, two of which I will name Item1 and Item2,

I would like to get a distict set of pairs Item1-Item2 from this table, more specifically if table looks like this ....

DISTINCT With Multiple Tables And INNER JOIN ?
My query works OK this way, but I need every "kohde_name" only once
(preferred the earliest "hav_date").

I've tried with "DISTINCT kohde_name" in many ways, but always end up with
syntax error...:(
Is there any solution with DISTINCT, or do I need anotjer solution?

my (simplified) query:
SELECT henk_name,henk_number, kohde_name
,kohde_txt,havainto_id,hav_kohdetxt, hav_date
FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id)
INNER JOIN havainto ON hja_havainto_id=havainto_id)
INNER JOIN kohde ON hav_kohde_id=kohde_id
WHERE ...

Getting Two Distinct Fields From One Tbl From A Join Query Of Two Tables
Im trying to get distinct (the same row returned only once) user_id and
forename from a tbl of users (they are always distinct) where user_id
in that tbl equals friend_id in another table friend_id, which 'should'
be unique ie there 'should' only be one row of a 'friend connection',
but in the case of bugs etc this might not be true, what i want to do
is select only the row of tblusers with the user details that are
matched by join condition below, but only ONCE for each, ie not in as
many times as there are the same connection in tblfriends.

For some reason the result i am getting is a repitition of the 2 fields
in tblusers (that i am selecting) for as many rows as there are in
tblfriends that match the join condition.

How can i make it only return a row once?

Selecting AS In A Join
I'm having trouble getting a response the the following query. I have cities in two fields: 1st is listing_city and 2nd is company_city. listing_city contains all the cities that paid ads are in where company_city is where the company is located.
My two tables are as follows:
Table 1: companies
Fields: company_city, company_state, company_category

Table 2: listings
Fields: listing_city, listing_state, listing_category

Here is my query:
SELECT t1.company_city, t3.listing_city FROM companies as t1, companies_level3 as t2, listings as t3 WHERE (t1.company_category='$category' AND t1.company_state='$state') OR (t1.company_id=t2.level3_cid AND t2.level3_category='$category' AND t1.company_state='$state') GROUP BY company_city ORDER BY company_city

How To Join Results ?
Is it possible to join results of the select query ?

So If I get data from two rows (firstname, secondname)Can I somehow join query results like this: firstname.secondname

Example:

QUERY: SELECT firstname,secondname FROM table;

RESULTS:
John.Doe
Mike.Monroe
Kate.Moss

Selecting With JOIN, GROUP BY And MAX
I have a table containing fields unit_id, temperature and date. This
has data for around 20 units, with many 1,000's of records for each.
Further information on each unit is in another table with just 1 entry
per unit.

I need to select the most recent record for each unit together with
its extra data. I cannot see how to do this in a single select. At
present I sort by date DESC and group by unit_id then my perl script
discards all but the 1st record for each unit. Code:

Limit Inner Join Results ?
Is it possible to limit the results of an inner join or sub select ?
For example, let say that I have a table of countries and a talbe of cities:

Sql Returning Too Many Results On JOIN
I have three tables which I'm joining...

AGENTS
========
agent_id
commission
promo_code

AGENT_PAYMENTS
==============
payment_id
agent_id
amount

SALES
========
sale_id
promo_code
status

I need to select all the sales info where an agent's promo code has been used (and the status of the sale is "C" (complete)). THe trouble is, the SQL I've used is doubling, trebling etc the sum of the payments made to an agent - this depends on how many results are returned for the number of sales... eg: 4 sales means the SUM is being multiplied by 4.

here's the SQL Code:

Selecting Data From More Than One Table (not Join)
I have a database with a bunch of tables, including two tables that store messages. These two tables have different names, but are identical in structure.

In my app, a user can run a report that selects messages from both of these tables. This is currently achieved by running two selects and printing out the details.

However I would now like to sort and limit (i.e. show messages 1-25 and have a link to show the next 25 etc).
I can do this in the code by dumping the combined results in an array and then sorting/limiting the output, but this will cause problems if the number of messages gets too big and the array size becomes too large for the memory in the server.
Also it doesn't help any with optimization and won't speed up the query time as I won't be able to use the LIMIT clause in the SQL!

So what I want to know is whether it is possible in SQL to select data from more than one table, given that the two (or more) tables to be joined will be identical in structure? If so, can I perform the normal ORDER BY and LIMIT functions?

Join Duplicated Results Issue
I have two tables, one is a catalogue of products and another one with media connected to the catalogue items. the media can be either 'main image' or an 'alternative product view'. I need to retrieve 4 products that are marked as live (c.status = Ƈ') and their 4 corresponding main images (m.category = 'main') with no repeats on the products.

I have the following query:

SELECT DISTINCT c.id, c.category_id, c.title, c.description, m.filename, m.description, m.copyright
FROM catalogue c
LEFT JOIN media m
ON m.parent_id = c.id
WHERE c.status = Ƈ'
AND m.category = 'main'
LIMIT 4
the problem I have is that some products have more than one 'main image' so this query is returning a duplicate product because it has two main images, even if the product only exists once on the catalogue..

Join 3 Tables, Limit Results From 3rd Table
Yet again I have a query about joins and even though I keep reading I cannot find a solution for my problem that is relevant or that I understand.

I have 3 tables (I have shortened the number of fields to those that are relevant):

category:

Quote:

catid

product:

Quote:

prodid
catid
prodname

prodimgs:

Quote:

imgid
prodid
imgname

I can select all products per category and I can select all images per product, but where I am having the problem is selecting all products for one category and at the same time selecting all the images per product and limiting the number of images displayed to one without limiting the products to one.

I have tried various joins and tested a lot, getting different results but none of them are returning what I need.

Select all products for a category:

PHP

SELECT prodid, prodname FROM product WHERE catid='$this->catid'

Works fine.

Select all images per product per category and limit results of image to 1:

PHP

SELECT product.prodid, product.catid, product.prodname, prodimgs.imgname FROM product, prodimgs WHERE product.catid ='$this->catid' AND prodimgs.prodid = product.prodid LIMIT 1

This returns only one product and one image per category despite there being more.

Using the same code as above but removing the limit I get a display of all images per product with the product name repeated over and over.

I know I need to use joins somehow but I have tried various ways and tested the sql but am always getting an error on the joins and I don't know how or where to put the limit so that it is only applied to the prodimgs table.

So what I want is:

1. User click on a category

2. On the next page all product names linked to the category are displayed, but once only.

3. Next to each product name, one image related to that product is to be displayed if an image is stored in the prodimgs table

Combining Results From Querying Two Join Tables
I'm using two queries to pull data from a two join tables and am looking for a way to get them into one result.

The first query is:
select group_concat(x) as genre_id, group_concat(y) as genre_name from (
select genres.id as x, genres.g_name as y from genres where id in (
select genre_id from genres_movies where movie_id = 70000103
)
) as tbl

+---------------------+---------------------------------------------------------+
| genre_id | genre_name |
| 864,131,813,191,321 | Documentary,Indie Documentaries,Political Documentaries |
+---------------------+---------------------------------------------------------+
The second is:
select group_concat(a) as director_id, group_concat(b) as director_name from (
select director.id as a, director.p_name as b from directors where id in (
select director_id from directors_movies where movie_id = 70000103
)
) as tbl2

+----------------------------+------------------------------------+
| director_id | director_name |
| 20006021,20063045,20063046 | Chris Smith,Dan Ollman,Sarah Price |
+----------------------------+------------------------------------+
Does anyone see a way to get something like this?
+---------------------+---------------------------------------------------------+----------------------------+------------------------------------+
| genre_id | genre_name | director_id | director_name |
| 864,131,813,191,321 | Documentary,Indie Documentaries,Political Documentaries | 20006021,20063045,20063046 | Chris Smith,Dan Ollman,Sarah Price |
+---------------------+---------------------------------------------------------+----------------------------+------------------------------------+

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

Join Results From Select Multiple Statment
how I can join results from multiple queries in one result without geting a prodict of the tables

ex:
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...

using the 4.0 sql so no subqueries supported.

SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have;

SELECT DISTINCT `field1`, `field2`, `field3` FROM table1

This returns what I need. There is also another field (field4) which I also want to select, but not distinctly.

Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1`

The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct)

I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me.

How can I display this not-wanted-distinct field in a distinct query?

Select Distinct And Include Non-distinct Columns
I have a publication table that tracks the products assigned to various publications.

I want to select all of the distinct products, based on product_ID, assigned to a specific publication but I also want to return additional columns that do not need to be distinct.

If I use the following select:

Select distinct publication.product_ID, publication_ID.code, publication.region from publication where publication_ID = '12'

I would get a list showing the three columns that I want to see which are product_ID, publication_ID and region for publication 12 but I get too many items as the distinct function means that all three of these columns must be distinct and I only need the product_ID to be distinct.

So how do I find all of the distinct product_ID but also show other columns such as region?

Do I need to do this with some kind of self-join?

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.

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?

DISTINCT Again...
Table `A` has rows `id` (primary key) and `name` both integers.

I want to return all rows from table `A` with all fields, BUT with distinct `name`.

So the query is something like that:

SELECT * FROM `A` WHERE `name` NOT IN(previous results of this query)

or something like that:

SELECT DISTINCT(`name`) * FROM `A`

DISTINCT
I have table `A` with columns `id`, `id2`, `time`, `news`.

`id` is primary key.
`id2` is key but not unique.

I want to retrieve 15 latest rows (ORDER BY time DESC LIMIT 15)
but all of 15 rows must have DISTINCT `id` (and only `id`)

So the query is something like this (it is not working of course)

SELECT
DISTINCT ROW(id) # This is my invention, I want something like that.
id2, time, news FROM news ORDER BY time DESC LIMIT 15