Join Problem

[Victor Subervi]

Hi;
I have this code:

select f.id from Flights f join Planes p where f.plane_id=p.id and
p.in_service=1

mysql describe Flights;
+-+---+--+-+-++
| Field   | Type  | Null | Key | Default | Extra  |
+-+---+--+-+-++
| id  | int(11)   | NO   | PRI | NULL| auto_increment |
| plane_id| int(11)   | NO   | MUL | NULL||
| pilot_id| int(11)   | NO   | MUL | NULL||
| flight_date | date  | NO   | | NULL||
| departure   | time  | NO   | | NULL||
| arrival | time  | NO   | | NULL||
| origination | enum('STT','STX') | YES  | | NULL||
| destination | enum('STT','STX') | YES  | | NULL||
| price   | float(6,2)| NO   | | NULL||
+-+---+--+-+-++

mysql describe Planes;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra  |
+--+-+--+-+-++
| id   | int(11) | NO   | PRI | NULL| auto_increment |
| name | varchar(20) | NO   | | NULL||
| in_service   | tinyint(1)  | NO   | | 1   ||
| capacity | tinyint(2)  | NO   | | NULL||
| total_weight | int(6)  | NO   | | NULL||
+--+-+--+-+-++

My goal is to exclude results in which in_service !=1; however, the filter
isn't working. Please advise.
TIA,
Victor

RE: Join Problem

[Gavin Towey]

What do you mean by not working?  What results do you get?

-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Monday, August 16, 2010 6:59 AM
To: mysql@lists.mysql.com
Subject: Join Problem

Hi;
I have this code:

select f.id from Flights f join Planes p where f.plane_id=p.id and
p.in_service=1

mysql describe Flights;
+-+---+--+-+-++
| Field   | Type  | Null | Key | Default | Extra  |
+-+---+--+-+-++
| id  | int(11)   | NO   | PRI | NULL| auto_increment |
| plane_id| int(11)   | NO   | MUL | NULL||
| pilot_id| int(11)   | NO   | MUL | NULL||
| flight_date | date  | NO   | | NULL||
| departure   | time  | NO   | | NULL||
| arrival | time  | NO   | | NULL||
| origination | enum('STT','STX') | YES  | | NULL||
| destination | enum('STT','STX') | YES  | | NULL||
| price   | float(6,2)| NO   | | NULL||
+-+---+--+-+-++

mysql describe Planes;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra  |
+--+-+--+-+-++
| id   | int(11) | NO   | PRI | NULL| auto_increment |
| name | varchar(20) | NO   | | NULL||
| in_service   | tinyint(1)  | NO   | | 1   ||
| capacity | tinyint(2)  | NO   | | NULL||
| total_weight | int(6)  | NO   | | NULL||
+--+-+--+-+-++

My goal is to exclude results in which in_service !=1; however, the filter
isn't working. Please advise.
TIA,
Victor

This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Join Problem

[andrew.2.moore]

Review your join type.

From: ext Gavin Towey [gto...@ffn.com]
Sent: 16 August 2010 19:36
To: Victor Subervi; mysql@lists.mysql.com
Subject: RE: Join Problem

What do you mean by not working?  What results do you get?

-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Monday, August 16, 2010 6:59 AM
To: mysql@lists.mysql.com
Subject: Join Problem

Hi;
I have this code:

select f.id from Flights f join Planes p where f.plane_id=p.id and
p.in_service=1

mysql describe Flights;
+-+---+--+-+-++
| Field   | Type  | Null | Key | Default | Extra  |
+-+---+--+-+-++
| id  | int(11)   | NO   | PRI | NULL| auto_increment |
| plane_id| int(11)   | NO   | MUL | NULL||
| pilot_id| int(11)   | NO   | MUL | NULL||
| flight_date | date  | NO   | | NULL||
| departure   | time  | NO   | | NULL||
| arrival | time  | NO   | | NULL||
| origination | enum('STT','STX') | YES  | | NULL||
| destination | enum('STT','STX') | YES  | | NULL||
| price   | float(6,2)| NO   | | NULL||
+-+---+--+-+-++

mysql describe Planes;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra  |
+--+-+--+-+-++
| id   | int(11) | NO   | PRI | NULL| auto_increment |
| name | varchar(20) | NO   | | NULL||
| in_service   | tinyint(1)  | NO   | | 1   ||
| capacity | tinyint(2)  | NO   | | NULL||
| total_weight | int(6)  | NO   | | NULL||
+--+-+--+-+-++

My goal is to exclude results in which in_service !=1; however, the filter
isn't working. Please advise.
TIA,
Victor

This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Another Join Problem

[Victor Subervi]

Hi;
I get the following error:

*SQL query:*

SELECT ID, Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

 *MySQL said:*
 #1052 - Column 'ID' in field list is ambiguous

Please note the error is about ambiguity. products has an ID field and so
does categories. If I run the statement taking out the ID from the
select, it runs. So, where is the ambiguity??
TIA,
V

Re: Another Join Problem

[David Giragosian]

On Fri, Oct 2, 2009 at 10:53 AM, Victor Subervi victorsube...@gmail.comwrote:

 Hi;
 I get the following error:

 *SQL query:*

 SELECT ID, Item
 FROM products
 JOIN categories ON categories.ID = products.Category
 LIMIT 0 , 30;

  *MySQL said:*
  #1052 - Column 'ID' in field list is ambiguous

 Please note the error is about ambiguity. products has an ID field and so
 does categories. If I run the statement taking out the ID from the
 select, it runs. So, where is the ambiguity??
 TIA,
 V


Just prefix the ID with either table name like products.ID or categories.ID.

David

Re: Another Join Problem

[John Daisley]

Qualify the column names. EG

SELECT categories.ID, products.Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

Regards

John Daisley



 Hi;
 I get the following error:

 *SQL query:*

 SELECT ID, Item
 FROM products
 JOIN categories ON categories.ID = products.Category
 LIMIT 0 , 30;

  *MySQL said:*
  #1052 - Column 'ID' in field list is ambiguous

 Please note the error is about ambiguity. products has an ID field and
 so
 does categories. If I run the statement taking out the ID from the
 select, it runs. So, where is the ambiguity??
 TIA,
 V



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Another Join Problem

[Mark Goodge]

Victor Subervi wrote:

Hi;
I get the following error:

*SQL query:*

SELECT ID, Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

 *MySQL said:*
 #1052 - Column 'ID' in field list is ambiguous

Please note the error is about ambiguity. products has an ID field and so
does categories. If I run the statement taking out the ID from the
select, it runs. So, where is the ambiguity??


The ambiguity is that the select clause doesn't know which table you're 
referring to, since you're joining two of them that both have an 'ID' 
field. This will work:


SELECT products.ID, Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Another Join Problem

[Shawn Green]

Victor Subervi wrote:

Hi;
I get the following error:

*SQL query:*

SELECT ID, Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

 *MySQL said:*
 #1052 - Column 'ID' in field list is ambiguous

Please note the error is about ambiguity. products has an ID field and so
does categories. If I run the statement taking out the ID from the
select, it runs. So, where is the ambiguity??
TIA,
V



From which table did you want the ID value of? Did you want to know

SELECT categories.ID, item

or

SELECT products.ID, item

for your results? When there is a duplication like this, you must be 
specific as MySQL can do many things but the ability to read the minds 
of its DBA's is not one of them.


Respecfully,
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Another Join Problem

[Victor Subervi]

Thanks
V

On Fri, Oct 2, 2009 at 11:08 AM, Mark Goodge m...@good-stuff.co.uk wrote:

 Victor Subervi wrote:

 Hi;
 I get the following error:

 *SQL query:*

 SELECT ID, Item
 FROM products
 JOIN categories ON categories.ID = products.Category
 LIMIT 0 , 30;

  *MySQL said:*
  #1052 - Column 'ID' in field list is ambiguous

 Please note the error is about ambiguity. products has an ID field and
 so
 does categories. If I run the statement taking out the ID from the
 select, it runs. So, where is the ambiguity??


 The ambiguity is that the select clause doesn't know which table you're
 referring to, since you're joining two of them that both have an 'ID' field.
 This will work:

 SELECT products.ID, Item
 FROM products
 JOIN categories ON categories.ID = products.Category
 LIMIT 0 , 30;

 Mark

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=victorsube...@gmail.com

Natural join problem

[John Meyer]

Two tables:

USERS:
USER_ID (PK)
. . .etc

TWEETS:
TWEET_ID (PK)
USER_ID (FK)

Trying to get the user information and the number of tweets each person has:

SELECT USERS.USER_NAME, COUNT(TWEETS.TWEET_ID) AS 'TWEETCOUNT' FROM 
TWEETS NATURAL JOIN USERS;


But it seems to be just rolling up all the information into one row.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Natural join problem

[Jason Trebilcock]

Methinx you need a GROUP BY in there.  See below.

 -Original Message-
 From: John Meyer [mailto:john.l.me...@gmail.com]
 Sent: Thursday, September 10, 2009 6:48 PM
 To: mysql@lists.mysql.com
 Subject: Natural join problem
 
 Two tables:
 
 USERS:
 USER_ID (PK)
 . . .etc
 
 TWEETS:
 TWEET_ID (PK)
 USER_ID (FK)
 
 Trying to get the user information and the number of tweets each person
 has:
 
 SELECT USERS.USER_NAME, COUNT(TWEETS.TWEET_ID) AS 'TWEETCOUNT' FROM
 TWEETS NATURAL JOIN USERS;
 

select u.user_name, count(t.tweet_id)
from users u, tweets t
where u.user_id = t.user_id
group by u.user_name


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Natural join problem

[John Meyer]

Thanks.  That worked.

Jason Trebilcock wrote:

Methinx you need a GROUP BY in there.  See below.

  

-Original Message-
From: John Meyer [mailto:john.l.me...@gmail.com]
Sent: Thursday, September 10, 2009 6:48 PM
To: mysql@lists.mysql.com
Subject: Natural join problem

Two tables:

USERS:
USER_ID (PK)
. . .etc

TWEETS:
TWEET_ID (PK)
USER_ID (FK)

Trying to get the user information and the number of tweets each person
has:

SELECT USERS.USER_NAME, COUNT(TWEETS.TWEET_ID) AS 'TWEETCOUNT' FROM
TWEETS NATURAL JOIN USERS;




select u.user_name, count(t.tweet_id)
from users u, tweets t
where u.user_id = t.user_id
group by u.user_name


  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

LEFT JOIN problem

[Jerry Schwartz]

I have a table, eo_name_table, that has exactly 860 unique titles in it.
Each record also has a date field, eo_pub_date:

+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| eo_name | varchar(255) |  | PRI | |   |
| eo_pub_date | date | YES  | | NULL|   |
+-+--+--+-+-+---+

I have another table, prod, that has many fields in it but the fields of
interest are prod_num, prod_title, prod_discont, and prod_published. The
other fields are irrelevant Here is the structure of the prod table:

+-+---+--+-+-+--
-+
| Field   | Type  | Null | Key | Default | Extra
|
+-+---+--+-+-+--
-+
| prod_id | varchar(15)   |  | PRI | |
|
| prod_num| mediumint(6) unsigned | YES  | MUL | NULL|
|
| prod_title  | varchar(255)  | YES  | MUL | NULL|
|
| prod_type   | varchar(2)| YES  | | NULL|
|
| prod_vat_pct| decimal(5,2)  | YES  | | NULL|
|
| prod_discont| tinyint(1)| YES  | | NULL|
|
| prod_ready  | tinyint(1)| YES  | | NULL|
|
| pub_id  | varchar(15)   | YES  | MUL | NULL|
|
| prod_published  | date  | YES  | | NULL|
|
| prod_pub_prod_id| varchar(255)  | YES  | MUL | NULL|
|
| prod_pub_acct_id| varchar(2)| YES  | | NULL|
|
| prod_pub_doi| date  | YES  | | NULL|
|
| prod_pub_resp   | date  | YES  | | NULL|
|
| prod_pub_prod_url   | varchar(255)  | YES  | | NULL|
|
| prod_rel_freq   | smallint(3)   | YES  | | NULL|
|
| prod_content_info   | varchar(255)  | YES  | | NULL|
|
| prod_info_type  | varchar(5)| YES  | | NULL|
|
| prod_language   | varchar(50)   | YES  | | NULL|
|
| prod_broch_doc  | varchar(255)  | YES  | | NULL|
|
| prod_samp_doc   | varchar(255)  | YES  | | NULL|
|
| prod_samp_pgs   | varchar(255)  | YES  | | NULL|
|
| prod_exec_summ  | varchar(255)  | YES  | | NULL|
|
| prod_toc_doc| varchar(255)  | YES  | | NULL|
|
| prod_e_title_tag| varchar(255)  | YES  | | NULL|
|
| prod_meta_tags  | varchar(255)  | YES  | | NULL|
|
| prod_keywords   | varchar(255)  | YES  | | NULL|
|
| prod_comments   | text  | YES  | | NULL|
|
| prod_if_sample_pdf  | varchar(255)  | YES  | | NULL|
|
| prod_stop_date  | date  | YES  | | NULL|
|
| prod_hide_web   | tinyint(1)| YES  | | NULL|
|
| prod_changed| tinyint(1)| YES  | | NULL|
|
| prod_export | tinyint(1)| YES  | | NULL|
|
| prod_export_pending | tinyint(1)| YES  | | NULL|
|
| prod_scoop_changed  | tinyint(1)| YES  | | NULL|
|
| prod_on_scoop   | tinyint(1)| YES  | | NULL|
|
| prod_added  | datetime  | YES  | | NULL|
|
| prod_updated| datetime  | YES  | | NULL|
|
+-+---+--+-+-+--
-+

I am trying to break eo_name_table into two sets, based upon matching
eo_name_table.eo_pub_date against prod.prod_published. The first query is

SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS
pub_date,
IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num,
IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, ,
Discontinued) AS discont,
IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title
FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = prod.prod_title

AND eo_name_table.eo_pub_date = prod.prod_published
WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL)
ORDER BY eo_name_table.eo_name;

As expected, this gives me exactly 860 rows in the result because the left
join should give me (at least) one result row for each row in eo_name_table.
Some of these rows, of course, have  values for every field.

Now I want to find the inverse set:

SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS
pub_date,
IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num,
IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, ,
Discontinued) AS discont,
IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title
FROM 

Re: LEFT JOIN problem

[Bill Newton]

Hi Jerry,

I think the problem is that NULL is not less than or greater than your 
prod_published date.  So you probably have eo_pub_date set to NULL in 56 
of your rows.


so for

eo_name_table.eo_pub_date  prod.prod_published

or

eo_name_table.eo_pub_date = prod.prod_published

mysql will rerturn false if  eo_name_table.eo_pub_date is NULL for 
either test.





Jerry Schwartz wrote:

I have a table, eo_name_table, that has exactly 860 unique titles in it.
Each record also has a date field, eo_pub_date:

+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| eo_name | varchar(255) |  | PRI | |   |
| eo_pub_date | date | YES  | | NULL|   |
+-+--+--+-+-+---+

I have another table, prod, that has many fields in it but the fields of
interest are prod_num, prod_title, prod_discont, and prod_published. The
other fields are irrelevant Here is the structure of the prod table:

+-+---+--+-+-+--
-+
| Field   | Type  | Null | Key | Default | Extra
|
+-+---+--+-+-+--
-+
| prod_id | varchar(15)   |  | PRI | |
|
| prod_num| mediumint(6) unsigned | YES  | MUL | NULL|
|
| prod_title  | varchar(255)  | YES  | MUL | NULL|
|
| prod_type   | varchar(2)| YES  | | NULL|
|
| prod_vat_pct| decimal(5,2)  | YES  | | NULL|
|
| prod_discont| tinyint(1)| YES  | | NULL|
|
| prod_ready  | tinyint(1)| YES  | | NULL|
|
| pub_id  | varchar(15)   | YES  | MUL | NULL|
|
| prod_published  | date  | YES  | | NULL|
|
| prod_pub_prod_id| varchar(255)  | YES  | MUL | NULL|
|
| prod_pub_acct_id| varchar(2)| YES  | | NULL|
|
| prod_pub_doi| date  | YES  | | NULL|
|
| prod_pub_resp   | date  | YES  | | NULL|
|
| prod_pub_prod_url   | varchar(255)  | YES  | | NULL|
|
| prod_rel_freq   | smallint(3)   | YES  | | NULL|
|
| prod_content_info   | varchar(255)  | YES  | | NULL|
|
| prod_info_type  | varchar(5)| YES  | | NULL|
|
| prod_language   | varchar(50)   | YES  | | NULL|
|
| prod_broch_doc  | varchar(255)  | YES  | | NULL|
|
| prod_samp_doc   | varchar(255)  | YES  | | NULL|
|
| prod_samp_pgs   | varchar(255)  | YES  | | NULL|
|
| prod_exec_summ  | varchar(255)  | YES  | | NULL|
|
| prod_toc_doc| varchar(255)  | YES  | | NULL|
|
| prod_e_title_tag| varchar(255)  | YES  | | NULL|
|
| prod_meta_tags  | varchar(255)  | YES  | | NULL|
|
| prod_keywords   | varchar(255)  | YES  | | NULL|
|
| prod_comments   | text  | YES  | | NULL|
|
| prod_if_sample_pdf  | varchar(255)  | YES  | | NULL|
|
| prod_stop_date  | date  | YES  | | NULL|
|
| prod_hide_web   | tinyint(1)| YES  | | NULL|
|
| prod_changed| tinyint(1)| YES  | | NULL|
|
| prod_export | tinyint(1)| YES  | | NULL|
|
| prod_export_pending | tinyint(1)| YES  | | NULL|
|
| prod_scoop_changed  | tinyint(1)| YES  | | NULL|
|
| prod_on_scoop   | tinyint(1)| YES  | | NULL|
|
| prod_added  | datetime  | YES  | | NULL|
|
| prod_updated| datetime  | YES  | | NULL|
|
+-+---+--+-+-+--
-+

I am trying to break eo_name_table into two sets, based upon matching
eo_name_table.eo_pub_date against prod.prod_published. The first query is

SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS
pub_date,
IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num,
IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, ,
Discontinued) AS discont,
IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title
FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = prod.prod_title

AND eo_name_table.eo_pub_date = prod.prod_published
WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL)
ORDER BY eo_name_table.eo_name;

As expected, this gives me exactly 860 rows in the result because the left
join should give me (at least) one result row for each row in eo_name_table.
Some of these rows, of course, have 

RE: LEFT JOIN problem

[Jerry Schwartz]

From: Bill Newton [mailto:[EMAIL PROTECTED]
Sent: Monday, April 14, 2008 2:09 PM
To: Jerry Schwartz
Cc: 'Mysql'
Subject: Re: LEFT JOIN problem

Hi Jerry,

I think the problem is that NULL is not less than or greater than your
prod_published date.  So you probably have eo_pub_date set to NULL in 56
of your rows.
[JS] I wish it were that simple. There are no rows in eo_name_table where
eo_pub_date is NULL.


so for

 eo_name_table.eo_pub_date  prod.prod_published

or

eo_name_table.eo_pub_date = prod.prod_published

mysql will rerturn false if  eo_name_table.eo_pub_date is NULL for
either test.
[JS] But it evidently does not. The second one works perfectly, the one
above does not.




Jerry Schwartz wrote:
 I have a table, eo_name_table, that has exactly 860 unique titles in
it.
 Each record also has a date field, eo_pub_date:

 +-+--+--+-+-+---+
 | Field   | Type | Null | Key | Default | Extra |
 +-+--+--+-+-+---+
 | eo_name | varchar(255) |  | PRI | |   |
 | eo_pub_date | date | YES  | | NULL|   |
 +-+--+--+-+-+---+

 I have another table, prod, that has many fields in it but the fields
of
 interest are prod_num, prod_title, prod_discont, and prod_published.
The
 other fields are irrelevant Here is the structure of the prod table:

 +-+---+--+-+-
+--
 -+
 | Field   | Type  | Null | Key | Default |
Extra
 |
 +-+---+--+-+-
+--
 -+
 | prod_id | varchar(15)   |  | PRI | |
 |
 | prod_num| mediumint(6) unsigned | YES  | MUL | NULL|
 |
 | prod_title  | varchar(255)  | YES  | MUL | NULL|
 |
 | prod_type   | varchar(2)| YES  | | NULL|
 |
 | prod_vat_pct| decimal(5,2)  | YES  | | NULL|
 |
 | prod_discont| tinyint(1)| YES  | | NULL|
 |
 | prod_ready  | tinyint(1)| YES  | | NULL|
 |
 | pub_id  | varchar(15)   | YES  | MUL | NULL|
 |
 | prod_published  | date  | YES  | | NULL|
 |
 | prod_pub_prod_id| varchar(255)  | YES  | MUL | NULL|
 |
 | prod_pub_acct_id| varchar(2)| YES  | | NULL|
 |
 | prod_pub_doi| date  | YES  | | NULL|
 |
 | prod_pub_resp   | date  | YES  | | NULL|
 |
 | prod_pub_prod_url   | varchar(255)  | YES  | | NULL|
 |
 | prod_rel_freq   | smallint(3)   | YES  | | NULL|
 |
 | prod_content_info   | varchar(255)  | YES  | | NULL|
 |
 | prod_info_type  | varchar(5)| YES  | | NULL|
 |
 | prod_language   | varchar(50)   | YES  | | NULL|
 |
 | prod_broch_doc  | varchar(255)  | YES  | | NULL|
 |
 | prod_samp_doc   | varchar(255)  | YES  | | NULL|
 |
 | prod_samp_pgs   | varchar(255)  | YES  | | NULL|
 |
 | prod_exec_summ  | varchar(255)  | YES  | | NULL|
 |
 | prod_toc_doc| varchar(255)  | YES  | | NULL|
 |
 | prod_e_title_tag| varchar(255)  | YES  | | NULL|
 |
 | prod_meta_tags  | varchar(255)  | YES  | | NULL|
 |
 | prod_keywords   | varchar(255)  | YES  | | NULL|
 |
 | prod_comments   | text  | YES  | | NULL|
 |
 | prod_if_sample_pdf  | varchar(255)  | YES  | | NULL|
 |
 | prod_stop_date  | date  | YES  | | NULL|
 |
 | prod_hide_web   | tinyint(1)| YES  | | NULL|
 |
 | prod_changed| tinyint(1)| YES  | | NULL|
 |
 | prod_export | tinyint(1)| YES  | | NULL|
 |
 | prod_export_pending | tinyint(1)| YES  | | NULL|
 |
 | prod_scoop_changed  | tinyint(1)| YES  | | NULL|
 |
 | prod_on_scoop   | tinyint(1)| YES  | | NULL|
 |
 | prod_added  | datetime  | YES  | | NULL|
 |
 | prod_updated| datetime  | YES  | | NULL|
 |
 +-+---+--+-+-
+--
 -+

 I am trying to break eo_name_table into two sets, based upon matching
 eo_name_table.eo_pub_date against prod.prod_published. The first query
is

 SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS
 pub_date,
  IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num,
  IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, ,
 Discontinued) AS discont,
  IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS
match_title

RE: LEFT JOIN problem

[Jerry Schwartz]

I've found yet another oddity with this situation. If I leave the date test
off of both JOINs they give the same number of rows, but they give me the
wrong number! Neither one of them gives me 860 rows returned. I must not
understand how a LEFT JOIN works.

By the way, the EXPLAIN for both of my original queries is the same:

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: eo_name_table
 type: ALL
possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 860
Extra: Using filesort
*** 2. row ***
   id: 1
  select_type: SIMPLE
table: prod
 type: ref
possible_keys: prod_title,prod_title_fulltext
  key: prod_title
  key_len: 766
  ref: giiexpr_db.eo_name_table.eo_name
 rows: 1
Extra: Using where
2 rows in set (0.05 sec)

I have a table, eo_name_table, that has exactly 860 unique titles in it.
Each record also has a date field, eo_pub_date:

+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| eo_name | varchar(255) |  | PRI | |   |
| eo_pub_date | date | YES  | | NULL|   |
+-+--+--+-+-+---+

I have another table, prod, that has many fields in it but the fields of
interest are prod_num, prod_title, prod_discont, and prod_published. The
other fields are irrelevant Here is the structure of the prod table:

+-+---+--+-+-+--

-+
| Field   | Type  | Null | Key | Default |
Extra
|
+-+---+--+-+-+--

-+
| prod_id | varchar(15)   |  | PRI | |
|
| prod_num| mediumint(6) unsigned | YES  | MUL | NULL|
|
| prod_title  | varchar(255)  | YES  | MUL | NULL|
|
| prod_type   | varchar(2)| YES  | | NULL|
|
| prod_vat_pct| decimal(5,2)  | YES  | | NULL|
|
| prod_discont| tinyint(1)| YES  | | NULL|
|
| prod_ready  | tinyint(1)| YES  | | NULL|
|
| pub_id  | varchar(15)   | YES  | MUL | NULL|
|
| prod_published  | date  | YES  | | NULL|
|
| prod_pub_prod_id| varchar(255)  | YES  | MUL | NULL|
|
| prod_pub_acct_id| varchar(2)| YES  | | NULL|
|
| prod_pub_doi| date  | YES  | | NULL|
|
| prod_pub_resp   | date  | YES  | | NULL|
|
| prod_pub_prod_url   | varchar(255)  | YES  | | NULL|
|
| prod_rel_freq   | smallint(3)   | YES  | | NULL|
|
| prod_content_info   | varchar(255)  | YES  | | NULL|
|
| prod_info_type  | varchar(5)| YES  | | NULL|
|
| prod_language   | varchar(50)   | YES  | | NULL|
|
| prod_broch_doc  | varchar(255)  | YES  | | NULL|
|
| prod_samp_doc   | varchar(255)  | YES  | | NULL|
|
| prod_samp_pgs   | varchar(255)  | YES  | | NULL|
|
| prod_exec_summ  | varchar(255)  | YES  | | NULL|
|
| prod_toc_doc| varchar(255)  | YES  | | NULL|
|
| prod_e_title_tag| varchar(255)  | YES  | | NULL|
|
| prod_meta_tags  | varchar(255)  | YES  | | NULL|
|
| prod_keywords   | varchar(255)  | YES  | | NULL|
|
| prod_comments   | text  | YES  | | NULL|
|
| prod_if_sample_pdf  | varchar(255)  | YES  | | NULL|
|
| prod_stop_date  | date  | YES  | | NULL|
|
| prod_hide_web   | tinyint(1)| YES  | | NULL|
|
| prod_changed| tinyint(1)| YES  | | NULL|
|
| prod_export | tinyint(1)| YES  | | NULL|
|
| prod_export_pending | tinyint(1)| YES  | | NULL|
|
| prod_scoop_changed  | tinyint(1)| YES  | | NULL|
|
| prod_on_scoop   | tinyint(1)| YES  | | NULL|
|
| prod_added  | datetime  | YES  | | NULL|
|
| prod_updated| datetime  | YES  | | NULL|
|
+-+---+--+-+-+--

-+

I am trying to break eo_name_table into two sets, based upon matching
eo_name_table.eo_pub_date against prod.prod_published. The first query
is

SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS
pub_date,
   IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num,
   

RE: LEFT JOIN problem

[Jerry Schwartz]

As usual, the computer is right and I am wrong. The only reason that one
query was coming out right is that it just happened the WHERE clause was
never failing. It was just luck that my data was just so.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, April 14, 2008 11:26 AM
To: 'Mysql'
Subject: LEFT JOIN problem

I have a table, eo_name_table, that has exactly 860 unique titles in it.
Each record also has a date field, eo_pub_date:

+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| eo_name | varchar(255) |  | PRI | |   |
| eo_pub_date | date | YES  | | NULL|   |
+-+--+--+-+-+---+

I have another table, prod, that has many fields in it but the fields of
interest are prod_num, prod_title, prod_discont, and prod_published. The
other fields are irrelevant Here is the structure of the prod table:

+-+---+--+-+-+--

-+
| Field   | Type  | Null | Key | Default |
Extra
|
+-+---+--+-+-+--

-+
| prod_id | varchar(15)   |  | PRI | |
|
| prod_num| mediumint(6) unsigned | YES  | MUL | NULL|
|
| prod_title  | varchar(255)  | YES  | MUL | NULL|
|
| prod_type   | varchar(2)| YES  | | NULL|
|
| prod_vat_pct| decimal(5,2)  | YES  | | NULL|
|
| prod_discont| tinyint(1)| YES  | | NULL|
|
| prod_ready  | tinyint(1)| YES  | | NULL|
|
| pub_id  | varchar(15)   | YES  | MUL | NULL|
|
| prod_published  | date  | YES  | | NULL|
|
| prod_pub_prod_id| varchar(255)  | YES  | MUL | NULL|
|
| prod_pub_acct_id| varchar(2)| YES  | | NULL|
|
| prod_pub_doi| date  | YES  | | NULL|
|
| prod_pub_resp   | date  | YES  | | NULL|
|
| prod_pub_prod_url   | varchar(255)  | YES  | | NULL|
|
| prod_rel_freq   | smallint(3)   | YES  | | NULL|
|
| prod_content_info   | varchar(255)  | YES  | | NULL|
|
| prod_info_type  | varchar(5)| YES  | | NULL|
|
| prod_language   | varchar(50)   | YES  | | NULL|
|
| prod_broch_doc  | varchar(255)  | YES  | | NULL|
|
| prod_samp_doc   | varchar(255)  | YES  | | NULL|
|
| prod_samp_pgs   | varchar(255)  | YES  | | NULL|
|
| prod_exec_summ  | varchar(255)  | YES  | | NULL|
|
| prod_toc_doc| varchar(255)  | YES  | | NULL|
|
| prod_e_title_tag| varchar(255)  | YES  | | NULL|
|
| prod_meta_tags  | varchar(255)  | YES  | | NULL|
|
| prod_keywords   | varchar(255)  | YES  | | NULL|
|
| prod_comments   | text  | YES  | | NULL|
|
| prod_if_sample_pdf  | varchar(255)  | YES  | | NULL|
|
| prod_stop_date  | date  | YES  | | NULL|
|
| prod_hide_web   | tinyint(1)| YES  | | NULL|
|
| prod_changed| tinyint(1)| YES  | | NULL|
|
| prod_export | tinyint(1)| YES  | | NULL|
|
| prod_export_pending | tinyint(1)| YES  | | NULL|
|
| prod_scoop_changed  | tinyint(1)| YES  | | NULL|
|
| prod_on_scoop   | tinyint(1)| YES  | | NULL|
|
| prod_added  | datetime  | YES  | | NULL|
|
| prod_updated| datetime  | YES  | | NULL|
|
+-+---+--+-+-+--

-+

I am trying to break eo_name_table into two sets, based upon matching
eo_name_table.eo_pub_date against prod.prod_published. The first query
is

SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS
pub_date,
   IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num,
   IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, ,
Discontinued) AS discont,
   IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS
match_title
FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name =
prod.prod_title

   AND eo_name_table.eo_pub_date = prod.prod_published
WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL)
ORDER BY eo_name_table.eo_name;

As expected

Re: left join problem

[Brent Baisley]

Yes, that is the correct behavior of a LEFT JOIN. A left join keeps  
all the record from the original/left table and will link up any  
related data from the right table, but if there is no related data,  
it sets it to NULL. If you want the join to act as a filter, the just  
use regular JOIN.


Brent


On Jan 8, 2008, at 12:24 PM, Wes Hegge wrote:

I am attempting to left join several tables.  This was working at  
one time
but has seemed to stop just recently.  I have checked and installed  
the
lastest version of mysql via Debian Etch apt-get.  I am running  
version

5.0.32.

I have simplified the example down to just 3 tables but the problem  
exists

at this point.

Table 'contacts'  - Does not matter what the fields are, still the  
same
problem.  I am not using TEXT fields though.  Most are int's or  
varchar's

   account_num
   first_name
   last_name

Table 'address'
   account_num
   address_1
   address_2
   city
   state
   zip

Table 'phone'
   account_num
   phone_1
   phone_1_type
   phone_2
   phone_2_type

What I want to do is search all three tables for something, return  
anything

that matches.  So here is the select statement I have been using:
SELECT contacts.account_num, first_name, last_name, address_1,  
city_1,
phone_1 FROM contacts LEFT JOIN (address, phone) ON  
(contacts.account_num =
address.account_num AND contacts.account_num = phone.account_num)  
WHERE

contacts.account_num LIKE '%something%' OR contacts.first_name LIKE
'%something%' OR address.address_1 LIKE '%something%' OR
address.address_2LIKE '%something%' OR
address.city LIKE '%somehting%' OR phone.phone_1 LIKE '%something%' OR
phone.phone_2 LIKE '%something%' ORDER BY last_name;

When I run this query I only get data back from the 'contacts'  
table.  What
I have been able to track down is that if I am missing data from  
any of the
tables that I LEFT JOIN'd then all the data from all the LEFT  
JOIN'd tables
will be NULL.  In other words if I have account data in tables  
'contacts'
and 'address' but nothing in 'phone' then no data from tables  
'address' or
'phone' will be returned.  If I add data to 'phone' then data is  
returned

properly.

Is this correct behavior?  If so, any suggestions on how to solve this
problem would be great.  Realize this is a smaller example of what  
I am
really trying to do.  There are at least 4 tables in the select  
statement at

any one time and could be as many as 6.

Thanks!

--
Wes Hegge

- If the phone rings.  Its not me.
-- Jimmy  Buffet



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

left join problem

[Wes Hegge]

I am attempting to left join several tables.  This was working at one time
but has seemed to stop just recently.  I have checked and installed the
lastest version of mysql via Debian Etch apt-get.  I am running version
5.0.32.

I have simplified the example down to just 3 tables but the problem exists
at this point.

Table 'contacts'  - Does not matter what the fields are, still the same
problem.  I am not using TEXT fields though.  Most are int's or varchar's
   account_num
   first_name
   last_name

Table 'address'
   account_num
   address_1
   address_2
   city
   state
   zip

Table 'phone'
   account_num
   phone_1
   phone_1_type
   phone_2
   phone_2_type

What I want to do is search all three tables for something, return anything
that matches.  So here is the select statement I have been using:
SELECT contacts.account_num, first_name, last_name, address_1, city_1,
phone_1 FROM contacts LEFT JOIN (address, phone) ON (contacts.account_num =
address.account_num AND contacts.account_num = phone.account_num) WHERE
contacts.account_num LIKE '%something%' OR contacts.first_name LIKE
'%something%' OR address.address_1 LIKE '%something%' OR
address.address_2LIKE '%something%' OR
address.city LIKE '%somehting%' OR phone.phone_1 LIKE '%something%' OR
phone.phone_2 LIKE '%something%' ORDER BY last_name;

When I run this query I only get data back from the 'contacts' table.  What
I have been able to track down is that if I am missing data from any of the
tables that I LEFT JOIN'd then all the data from all the LEFT JOIN'd tables
will be NULL.  In other words if I have account data in tables 'contacts'
and 'address' but nothing in 'phone' then no data from tables 'address' or
'phone' will be returned.  If I add data to 'phone' then data is returned
properly.

Is this correct behavior?  If so, any suggestions on how to solve this
problem would be great.  Realize this is a smaller example of what I am
really trying to do.  There are at least 4 tables in the select statement at
any one time and could be as many as 6.

Thanks!

-- 
Wes Hegge

- If the phone rings.  Its not me.
-- Jimmy  Buffet

Left join problem

[Kaj Schermer Didriksen]

Hi
two tables gi_t and gi

gi_t holds 5 records. gi holds ca. 1500.

I need all from gi for each in gi_t ie LEFT JOIN!!!
but this:
SELECT gi.id AS id, overskrift,gidata FROM gi_t LEFT JOIN gi ON  
gi_t.id=gi.gitref WHERE bladref=137

gives the same as
SELECT gi.id AS id, overskrift,gidata FROM gi_t ,gi WHERE  
gi_t.id=gi.gitref AND bladref=137


I want 5 records (3 ok, and 2 NULL) and I get 3. the 3 that has a  
relation in gi


why?
Regards
Kaj Schermer Didriksen

RE: Left join problem

[Jerry Schwartz]

Which table is bladref in?

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


 -Original Message-
 From: Kaj Schermer Didriksen [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, September 05, 2007 10:39 AM
 To: mysql@lists.mysql.com
 Subject: Left join problem

 Hi
 two tables gi_t and gi

 gi_t holds 5 records. gi holds ca. 1500.

 I need all from gi for each in gi_t ie LEFT JOIN!!!
 but this:
 SELECT gi.id AS id, overskrift,gidata FROM gi_t LEFT JOIN gi ON
 gi_t.id=gi.gitref WHERE bladref=137
 gives the same as
 SELECT gi.id AS id, overskrift,gidata FROM gi_t ,gi WHERE
 gi_t.id=gi.gitref AND bladref=137

 I want 5 records (3 ok, and 2 NULL) and I get 3. the 3 that has a
 relation in gi

 why?
 Regards
 Kaj Schermer Didriksen





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Left join problem

[Martijn Tonies]

Hi,

 two tables gi_t and gi
 
 gi_t holds 5 records. gi holds ca. 1500.
 
 I need all from gi for each in gi_t ie LEFT JOIN!!!
 but this:
 SELECT gi.id AS id, overskrift,gidata FROM gi_t LEFT JOIN gi ON  
 gi_t.id=gi.gitref WHERE bladref=137
 gives the same as
 SELECT gi.id AS id, overskrift,gidata FROM gi_t ,gi WHERE  
 gi_t.id=gi.gitref AND bladref=137
 
 I want 5 records (3 ok, and 2 NULL) and I get 3. the 3 that has a  
 relation in gi

Where does bladref come from? As soon as you use
multiple tables in your query, it's good practice to prefix
each column with the table (or alias) it comes from.

Can you give the table metadata and sample data for
your rows?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[5.0] Left Join Problem

[Stephen P. Fracek, Jr.]

I'm trying to upgrade from MySQL 4.1 to MySQL 5.0.

A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss
as to how to proceed.

SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5,
tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id
= tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON
tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND
tab_e.id3 = tab_f.id3 LIMIT 1;

The above query now generates this error:  Unknown column 'tab_a.id in 'on
clause'.  Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON
(tab_b.id = tab_a.id) does not work.

However, splitting the original query apart and grouping the Left Joins in
one query and the regular joins in another query does NOT generates any
errors:

SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT
JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id =
tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value'
LIMIT 1; - this is ok

AND

SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e,
db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 =
tab_f.id3 LIMIT 1; - this is ok

Why doesn't the original query work in MySQL 5.0?  What do I need to do to
make it work?

TIA

Stephen P. Fracek, Jr.
[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [5.0] Left Join Problem

[Paul DuBois]

At 12:47 PM -0500 7/21/06, Stephen P. Fracek, Jr. wrote:

I'm trying to upgrade from MySQL 4.1 to MySQL 5.0.

A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss
as to how to proceed.

SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5,
tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id
= tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON
tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND
tab_e.id3 = tab_f.id3 LIMIT 1;

The above query now generates this error:  Unknown column 'tab_a.id in 'on
clause'.  Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON
(tab_b.id = tab_a.id) does not work.

However, splitting the original query apart and grouping the Left Joins in
one query and the regular joins in another query does NOT generates any
errors:

SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT
JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id =
tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value'
LIMIT 1; - this is ok

AND

SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e,
db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 =
tab_f.id3 LIMIT 1; - this is ok

Why doesn't the original query work in MySQL 5.0?  What do I need to do to
make it work?


The information you need is here:

http://dev.mysql.com/doc/refman/5.0/en/join.html

See particularly Join Processing Changes in MySQL 5.0.12

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [5.0] Left Join Problem

[Gerald L. Clark]

Stephen P. Fracek, Jr. wrote:

I'm trying to upgrade from MySQL 4.1 to MySQL 5.0.

A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss
as to how to proceed.

SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5,
tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id
= tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON
tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND
tab_e.id3 = tab_f.id3 LIMIT 1;

The above query now generates this error:  Unknown column 'tab_a.id in 'on
clause'.  Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON
(tab_b.id = tab_a.id) does not work.

However, splitting the original query apart and grouping the Left Joins in
one query and the regular joins in another query does NOT generates any
errors:

SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT
JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id =
tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value'
LIMIT 1; - this is ok

AND

SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e,
db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 =
tab_f.id3 LIMIT 1; - this is ok

Why doesn't the original query work in MySQL 5.0?  What do I need to do to
make it work?

TIA

Stephen P. Fracek, Jr.
[EMAIL PROTECTED]



It is a join precedence issue. Use INNER Join instead of a comma.

SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, 
tab_f.item6

FROM db.tab_a
INNER JOIN db.tab_e ON tab_a.id2 = tab_e.id2
INNER JOIN db.tab_f ON tab_e.id3 = tab_f.id3
LEFT  JOIN db.tab_b ON tab_b.id  = tab_a.id
LEFT  JOIN db.tab_c ON tab_c.id = tab_a.id
LEFT  JOIN db.tab_d ON tab_d.id = tab_a.id
WHERE tab_a.id = 'value'
LIMIT 1;
--
Gerald L. Clark
Supplier Systems Corporation

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [5.0] Left Join Problem

[Stephen P. Fracek, Jr.]

On 2006-07-21 1:01 PM, Gerald L. Clark [EMAIL PROTECTED]
wrote:


 It is a join precedence issue. Use INNER Join instead of a comma.


Thanks Gerald.  

Paul DuBois' polite suggestion to read the manual helped.  Upon re-reading
the section about the change in precedence with
the comma operator and the join, I realized there was a
simple fix and that I had misinterpreted the section on the first read.

The revised query works.

Stephen P. Fracek, Jr.
[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

LEFT JOIN problem

[Guy Brom]

I have the following tables:

languages
language_id int,
language_title varchar(80)

objects
object_id int
object_language_id int
object_title varchar(100)

I want to select ALL available languages, and match the translated object 
(if it is exists) for a specific object_id. If it does not exists for that 
specific language_id, I want to have NULL.


Because I'm using mysql 4.0, no subqueries allowed. Is there any way to 
rewrite the following?


SELECT
language_id,
language_title,
(SELECT object_title FROM objects WHERE object_id = ? AND object_language = 
l.id) AS object_title

FROM
languages l

Thanks! 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LEFT JOIN problem

[Pooly]

2005/11/5, Guy Brom [EMAIL PROTECTED]:
 I have the following tables:

 languages
  language_id int,
  language_title varchar(80)

 objects
  object_id int
  object_language_id int
  object_title varchar(100)

 I want to select ALL available languages, and match the translated object
 (if it is exists) for a specific object_id. If it does not exists for that
 specific language_id, I want to have NULL.


Have a try with :
Select language_id,language_title,object_id,object_title
FROM languages
LEFT JOIN objects ON objects.object_language_id=languages.language_id

--
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LEFT JOIN problem

[Rhino]

Have a look at the reply I just wrote for Gobi, entitled Re: Help with an
SQL query. You'll find an explanation there for how to use temporary tables
and joins instead of subqueries in MySQL versions that don't support
subqueries.

Rhino

- Original Message - 
From: Guy Brom [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, November 05, 2005 10:28 AM
Subject: LEFT JOIN problem


 I have the following tables:

 languages
  language_id int,
  language_title varchar(80)

 objects
  object_id int
  object_language_id int
  object_title varchar(100)

 I want to select ALL available languages, and match the translated object
 (if it is exists) for a specific object_id. If it does not exists for that
 specific language_id, I want to have NULL.

 Because I'm using mysql 4.0, no subqueries allowed. Is there any way to
 rewrite the following?

 SELECT
  language_id,
  language_title,
  (SELECT object_title FROM objects WHERE object_id = ? AND object_language
=
 l.id) AS object_title
 FROM
  languages l

 Thanks!


 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


 -- 
 No virus found in this incoming message.
 Checked by AVG Free Edition.
 Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005





-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 03/11/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

classic outer join problem

[Hank]

I have a table of school class assignments with the following fields -
the first four fields are the primary key:

Year (int) 
Term (enum, Spring, Fall,Winter)
ClassID (int)
SectionID (int)
Attachement (varchar 225)

The attachment field is a pointer to a file in the OS of an uploaded
file, in the format like this:  
/uploads/2003/Fall/330/1/conversions.doc

When an old class item is imported into a new class item for a new
year/term/class/section (new record), the attachment field is copied
over, and the OS file stays where it is.. the attachment field value
is then a pointer the to the OS file for a previous year/term (i.e.
the OS file is not copied to the new location filesystem structure).

I've been trying to construct a self-joining query to list all the
attachments which are NOT referenced by some future
year/term/class/section.   The desired query result is a list of files
I can DELETE - i.e. files not imported or being pointed to by any
other class_item record in a different year/term.

Keep in mind that pointer to files in the same Year/Term (but
different class/section) are NOT to be deleted.

The system currently has MySQL version 4.0.1, so I can't use
subqueries (i.e. NOT IN (...)).

Any suggestions would be greatly appreciated. thanks.
-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: classic outer join problem

[SGreen]

Hank [EMAIL PROTECTED] wrote on 05/24/2005 05:02:47 PM:

 I have a table of school class assignments with the following fields -
 the first four fields are the primary key:
 
 Year (int) 
 Term (enum, Spring, Fall,Winter)
 ClassID (int)
 SectionID (int)
 Attachement (varchar 225)
 
 The attachment field is a pointer to a file in the OS of an uploaded
 file, in the format like this: 
 /uploads/2003/Fall/330/1/conversions.doc
 
 When an old class item is imported into a new class item for a new
 year/term/class/section (new record), the attachment field is copied
 over, and the OS file stays where it is.. the attachment field value
 is then a pointer the to the OS file for a previous year/term (i.e.
 the OS file is not copied to the new location filesystem structure).
 
 I've been trying to construct a self-joining query to list all the
 attachments which are NOT referenced by some future
 year/term/class/section.   The desired query result is a list of files
 I can DELETE - i.e. files not imported or being pointed to by any
 other class_item record in a different year/term.
 
 Keep in mind that pointer to files in the same Year/Term (but
 different class/section) are NOT to be deleted.
 
 The system currently has MySQL version 4.0.1, so I can't use
 subqueries (i.e. NOT IN (...)).
 
 Any suggestions would be greatly appreciated. thanks.
 -Hank
 
 -- 
It's almost trivial to detect what is NOT in a list, if you have a list to 
compare to. Do you have a table listing all of the files in the catalog 
area on your disk? If you do then we can detect which files are no longer 
in use and those will be the ones you can delete.

Something else you may be able to do is to run a query returning the 
MAX(year) for each attachment

SELECT attachment, max(year)
FROM catalogtable
GROUP BY attachment;

Then eliminate all of the attachments that are too old.

Just some ideas.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: classic outer join problem

[mfatene]

Hi Hank,
I think your problem is to find all the files under /uploads by a command like :

cd /uploads
ls -lR | grep .doc files.txt

then load the files.txt into a temporary table TEMPtable that you create for
this issue (see http://dev.mysql.com/doc/mysql/en/load-data.html), after
truncating it.

When data is loaded, you can then delete by :

delete from Your_table where attachement not in (select attachement from
TEMPtable);
commit;

to rewrite a not in, see
http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html

AND look at use of tempfalg at the bottom of the page.



Mathias

Selon Hank [EMAIL PROTECTED]:

 I have a table of school class assignments with the following fields -
 the first four fields are the primary key:

 Year (int)
 Term (enum, Spring, Fall,Winter)
 ClassID (int)
 SectionID (int)
 Attachement (varchar 225)

 The attachment field is a pointer to a file in the OS of an uploaded
 file, in the format like this:
 /uploads/2003/Fall/330/1/conversions.doc

 When an old class item is imported into a new class item for a new
 year/term/class/section (new record), the attachment field is copied
 over, and the OS file stays where it is.. the attachment field value
 is then a pointer the to the OS file for a previous year/term (i.e.
 the OS file is not copied to the new location filesystem structure).

 I've been trying to construct a self-joining query to list all the
 attachments which are NOT referenced by some future
 year/term/class/section.   The desired query result is a list of files
 I can DELETE - i.e. files not imported or being pointed to by any
 other class_item record in a different year/term.

 Keep in mind that pointer to files in the same Year/Term (but
 different class/section) are NOT to be deleted.

 The system currently has MySQL version 4.0.1, so I can't use
 subqueries (i.e. NOT IN (...)).

 Any suggestions would be greatly appreciated. thanks.
 -Hank

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

JOIN Problem

[Albert Padley]

I have the following 2 tables:
CREATE TABLE `division_info` (
  `id` int(11) NOT NULL auto_increment,
  `division` varchar(50) NOT NULL default '',
  `spots` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `division` (`division`),
  KEY `spots` (`spots`)
) TYPE=MyISAM
CREATE TABLE `team_info` (
  `id` int(14) NOT NULL auto_increment,
  `division` varchar(50) NOT NULL default '',
  `application` varchar(9) NOT NULL default 'No',
  PRIMARY KEY  (`id`),
  KEY `division` (`division`),
) TYPE=MyISAM
I'm running the following query:
SELECT division_info.division AS 'division', COUNT(team_info.division) 
AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN 
team_info ON division_info.division = team_info.division WHERE 
application='ACCEPTED' GROUP BY division_info.division

This query runs fine. However, it only returns divisions where there is 
at least 1 ACCEPTED team. I also need to show divisions where there are 
spots but not teams have yet been ACCEPTED.

A little direction would be appreciated.
Thanks.
Al Padley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: JOIN Problem

[Michael Dykman]

On Thu, 2005-02-17 at 12:08, Albert Padley wrote:
 I have the following 2 tables:
 
 CREATE TABLE `division_info` (
`id` int(11) NOT NULL auto_increment,
`division` varchar(50) NOT NULL default '',
`spots` int(11) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `division` (`division`),
KEY `spots` (`spots`)
 ) TYPE=MyISAM
 
 CREATE TABLE `team_info` (
`id` int(14) NOT NULL auto_increment,
`division` varchar(50) NOT NULL default '',
`application` varchar(9) NOT NULL default 'No',
PRIMARY KEY  (`id`),
KEY `division` (`division`),
 ) TYPE=MyISAM
 
 I'm running the following query:
 
 SELECT division_info.division AS 'division', COUNT(team_info.division) 
 AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN 
 team_info ON division_info.division = team_info.division WHERE 
 application='ACCEPTED' GROUP BY division_info.division
 
 This query runs fine. However, it only returns divisions where there is 
 at least 1 ACCEPTED team. I also need to show divisions where there are 
 spots but not teams have yet been ACCEPTED.
 
 A little direction would be appreciated.
 
 Thanks.
 
 Al Padley

SELECT division_info.division AS 'division', COUNT(team_info.division) 
AS 'count', division_info.spots as 'spots' FROM division_info
LEFT == OUTER == JOIN 
team_info ON division_info.division = team_info.division WHERE 
application='ACCEPTED' GROUP BY division_info.division

-- 
 - michael dykman
 - [EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: JOIN Problem

[mel list_php]

Would you mind giving me some additional explanation about outer join?
In the mysql reference book I just found one line saying left outer join 
syntax exists only for compatibility with odbc.
thanks!

From: Michael Dykman [EMAIL PROTECTED]
To: Albert Padley [EMAIL PROTECTED]
CC: \MySQL List\ mysql@lists.mysql.com
Subject: Re: JOIN Problem
Date: Thu, 17 Feb 2005 12:20:44 -0500
On Thu, 2005-02-17 at 12:08, Albert Padley wrote:
 I have the following 2 tables:

 CREATE TABLE `division_info` (
`id` int(11) NOT NULL auto_increment,
`division` varchar(50) NOT NULL default '',
`spots` int(11) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `division` (`division`),
KEY `spots` (`spots`)
 ) TYPE=MyISAM

 CREATE TABLE `team_info` (
`id` int(14) NOT NULL auto_increment,
`division` varchar(50) NOT NULL default '',
`application` varchar(9) NOT NULL default 'No',
PRIMARY KEY  (`id`),
KEY `division` (`division`),
 ) TYPE=MyISAM

 I'm running the following query:

 SELECT division_info.division AS 'division', COUNT(team_info.division)
 AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN
 team_info ON division_info.division = team_info.division WHERE
 application='ACCEPTED' GROUP BY division_info.division

 This query runs fine. However, it only returns divisions where there is
 at least 1 ACCEPTED team. I also need to show divisions where there are
 spots but not teams have yet been ACCEPTED.

 A little direction would be appreciated.

 Thanks.

 Al Padley
SELECT division_info.division AS 'division', COUNT(team_info.division)
AS 'count', division_info.spots as 'spots' FROM division_info
LEFT == OUTER == JOIN
team_info ON division_info.division = team_info.division WHERE
application='ACCEPTED' GROUP BY division_info.division
--
 - michael dykman
 - [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: JOIN Problem

[SGreen]

Albert Padley [EMAIL PROTECTED] wrote on 02/17/2005 12:08:31 PM:

 I have the following 2 tables:
 
 CREATE TABLE `division_info` (
`id` int(11) NOT NULL auto_increment,
`division` varchar(50) NOT NULL default '',
`spots` int(11) NOT NULL default '0',
PRIMARY KEY  (`id`),
KEY `division` (`division`),
KEY `spots` (`spots`)
 ) TYPE=MyISAM
 
 CREATE TABLE `team_info` (
`id` int(14) NOT NULL auto_increment,
`division` varchar(50) NOT NULL default '',
`application` varchar(9) NOT NULL default 'No',
PRIMARY KEY  (`id`),
KEY `division` (`division`),
 ) TYPE=MyISAM
 
 I'm running the following query:
 
 SELECT division_info.division AS 'division', COUNT(team_info.division) 
 AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN 
 team_info ON division_info.division = team_info.division WHERE 
 application='ACCEPTED' GROUP BY division_info.division
 
 This query runs fine. However, it only returns divisions where there is 
 at least 1 ACCEPTED team. I also need to show divisions where there are 
 spots but not teams have yet been ACCEPTED.
 
 A little direction would be appreciated.
 
 Thanks.
 
 Al Padley
 
 

You are very, very close. You used the LEFT JOIN (correct choice) but you 
eliminated all of the rows from your division table without any accepted 
teams when you said WHERE application='ACCEPTED'. That's why you aren't 
getting a good count across all of your divisions.

What I think you were trying to do was to tell how many teams have 
accepted within a division, across all divisions. That means you want to 
count 'ACCEPTED' teams but not teams that do not exist or teams that 
have some other application status, right?

I have reworked your query a bit and I think I answered the question you 
had and I also tried to demonstrate how to get at some other information 
at the same time.

SELECT d.division AS 'division'
, d.spots as 'spots'
, COUNT(t.division) AS 'total_team_count'
, SUM(IF(t.application = 'ACCEPTED',1,0)) as 'teams_accepted'
, SUM(IF(t.application  'ACCEPTED',1,0)) as 'teams_not_accepted'
FROM division_info d
LEFT JOIN team_info t
ON d.division = t.division 
GROUP BY d.division, d.spots

Using the aggregating functions like COUNT() and SUM() in this way, we are 
building a crosstab query (also called a pivot table). There are many 
other articles in this thread's archive that can help you understand how 
to build those types of queries with MySQL. 

By eliminating your WHERE clause and moving your condition into a 
SUM(IF()), we allowed all of the rows from the RIGHT side of the LEFT JOIN 
(even those with all null values) to appear in the results and thanks to 
the IF() we only count (by adding up the 1's) those rows with the values 
we want to find.

HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: JOIN Problem

[Michael Dykman]

The idea of the OUTER JOIN is that it find at least one row for the
joined table, even if the conddtion for that tables fails.. the
resulting row will have all nulls except for the connecting fields.

it guarantees that every row of division_info which is returned by the
first part of the query is represented in the final data set even if
there is no correcponding team_info which matches the join condition.

I'm didn't notice what version of MySQL you are running and I'm not 100%
sure this is supported under MySQL 3.23 (for example) but it certainly
works on my 4.1..  the outer join has been part of ANSI-SQL syntax for
at least 10 years I think.


On Thu, 2005-02-17 at 12:30, mel list_php wrote:
 Would you mind giving me some additional explanation about outer join?
 In the mysql reference book I just found one line saying left outer join 
 syntax exists only for compatibility with odbc.
 thanks!
 
 From: Michael Dykman [EMAIL PROTECTED]
 To: Albert Padley [EMAIL PROTECTED]
 CC: \MySQL List\ mysql@lists.mysql.com
 Subject: Re: JOIN Problem
 Date: Thu, 17 Feb 2005 12:20:44 -0500
 
 On Thu, 2005-02-17 at 12:08, Albert Padley wrote:
   I have the following 2 tables:
  
   CREATE TABLE `division_info` (
  `id` int(11) NOT NULL auto_increment,
  `division` varchar(50) NOT NULL default '',
  `spots` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `division` (`division`),
  KEY `spots` (`spots`)
   ) TYPE=MyISAM
  
   CREATE TABLE `team_info` (
  `id` int(14) NOT NULL auto_increment,
  `division` varchar(50) NOT NULL default '',
  `application` varchar(9) NOT NULL default 'No',
  PRIMARY KEY  (`id`),
  KEY `division` (`division`),
   ) TYPE=MyISAM
  
   I'm running the following query:
  
   SELECT division_info.division AS 'division', COUNT(team_info.division)
   AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN
   team_info ON division_info.division = team_info.division WHERE
   application='ACCEPTED' GROUP BY division_info.division
  
   This query runs fine. However, it only returns divisions where there is
   at least 1 ACCEPTED team. I also need to show divisions where there are
   spots but not teams have yet been ACCEPTED.
  
   A little direction would be appreciated.
  
   Thanks.
  
   Al Padley
 
 SELECT division_info.division AS 'division', COUNT(team_info.division)
 AS 'count', division_info.spots as 'spots' FROM division_info
  LEFT == OUTER == JOIN
 team_info ON division_info.division = team_info.division WHERE
 application='ACCEPTED' GROUP BY division_info.division
 
 --
   - michael dykman
   - [EMAIL PROTECTED]
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 _
 Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
 http://toolbar.msn.co.uk/
-- 
 - michael dykman
 - [EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: JOIN Problem

[SGreen]

Michael Dykman [EMAIL PROTECTED] wrote on 02/17/2005 12:20:44 PM:

 On Thu, 2005-02-17 at 12:08, Albert Padley wrote:
  I have the following 2 tables:
  
  CREATE TABLE `division_info` (
 `id` int(11) NOT NULL auto_increment,
 `division` varchar(50) NOT NULL default '',
 `spots` int(11) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `division` (`division`),
 KEY `spots` (`spots`)
  ) TYPE=MyISAM
  
  CREATE TABLE `team_info` (
 `id` int(14) NOT NULL auto_increment,
 `division` varchar(50) NOT NULL default '',
 `application` varchar(9) NOT NULL default 'No',
 PRIMARY KEY  (`id`),
 KEY `division` (`division`),
  ) TYPE=MyISAM
  
  I'm running the following query:
  
  SELECT division_info.division AS 'division', COUNT(team_info.division) 

  AS 'count', division_info.spots as 'spots' FROM division_info LEFT 
JOIN 
  team_info ON division_info.division = team_info.division WHERE 
  application='ACCEPTED' GROUP BY division_info.division
  
  This query runs fine. However, it only returns divisions where there 
is 
  at least 1 ACCEPTED team. I also need to show divisions where there 
are 
  spots but not teams have yet been ACCEPTED.
  
  A little direction would be appreciated.
  
  Thanks.
  
  Al Padley
 
 SELECT division_info.division AS 'division', COUNT(team_info.division) 
 AS 'count', division_info.spots as 'spots' FROM division_info
LEFT == OUTER == JOIN 
 team_info ON division_info.division = team_info.division WHERE 
 application='ACCEPTED' GROUP BY division_info.division
 
 -- 
  - michael dykman
  - [EMAIL PROTECTED]
 

begin flame
Michael,

Please be so kind as to explain WHY you thought your answer was DIFFERENT 
than the originally posted query? The OUTER keyword is optional in MySQL. 
That means that LEFT JOIN and LEFT OUTER JOIN are parsed as the same 
token. 

Look at the problem again, remembering that the team_info table is the 
OUTER table of the JOIN, and see if you can spot the problem. I'll give 
you another hint, it's a SQL logic issue, not a SQL grammar issue.
end flame

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: JOIN Problem

[Albert Padley]

On Feb 17, 2005, at 10:34 AM, [EMAIL PROTECTED] wrote:

Albert Padley [EMAIL PROTECTED] wrote on 02/17/2005 12:08:31 PM:
  I have the following 2 tables:
 
  CREATE TABLE `division_info` (
     `id` int(11) NOT NULL auto_increment,
     `division` varchar(50) NOT NULL default '',
     `spots` int(11) NOT NULL default '0',
     PRIMARY KEY  (`id`),
     KEY `division` (`division`),
     KEY `spots` (`spots`)
  ) TYPE=MyISAM
 
  CREATE TABLE `team_info` (
     `id` int(14) NOT NULL auto_increment,
     `division` varchar(50) NOT NULL default '',
     `application` varchar(9) NOT NULL default 'No',
     PRIMARY KEY  (`id`),
     KEY `division` (`division`),
  ) TYPE=MyISAM
 
  I'm running the following query:
 
  SELECT division_info.division AS 'division', 
COUNT(team_info.division)
  AS 'count', division_info.spots as 'spots' FROM division_info LEFT 
JOIN
  team_info ON division_info.division = team_info.division WHERE
  application='ACCEPTED' GROUP BY division_info.division
 
  This query runs fine. However, it only returns divisions where 
there is
  at least 1 ACCEPTED team. I also need to show divisions where there 
are
  spots but not teams have yet been ACCEPTED.
 
  A little direction would be appreciated.
 
  Thanks.
 
  Al Padley
 
 

You are very, very close. You used the LEFT JOIN (correct choice) but 
you eliminated all of the rows from your division table without any 
accepted teams when you said WHERE application='ACCEPTED'. That's why 
you aren't getting a good count across all of your divisions.

What I think you were trying to do was to tell how many teams have 
accepted within a division, across all divisions. That means you want 
to count 'ACCEPTED' teams but not teams that do not exist or teams 
that have some other application status, right?

I have reworked your query a bit and I think I answered the question 
you had and I also tried to demonstrate how to get at some other 
information at the same time.

SELECT d.division AS 'division'
        , d.spots as 'spots'
        , COUNT(t.division) AS 'total_team_count'
        , SUM(IF(t.application = 'ACCEPTED',1,0)) as 'teams_accepted'
        , SUM(IF(t.application  'ACCEPTED',1,0)) as 
'teams_not_accepted'
FROM division_info d
LEFT JOIN team_info t
        ON d.division = t.division
 GROUP BY d.division, d.spots

Using the aggregating functions like COUNT() and SUM() in this way, we 
are building a crosstab query (also called a pivot table). There are 
many other articles in this thread's archive that can help you 
understand how to build those types of queries with MySQL.

By eliminating your WHERE clause and moving your condition into a 
SUM(IF()), we allowed all of the rows from the RIGHT side of the LEFT 
JOIN (even those with all null values) to appear in the results and 
thanks to the IF() we only count (by adding up the 1's) those rows 
with the values we want to find.

HTH,
Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine
Shawn,
Thanks. This was just right. Once again, you have gone beyond the 
initial question and not only provided the correct answer, but an 
explanation that helps me better understand the why behind the query. 
Much appreciated. Thanks again.

Al Padley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 4.0.18 - 4.1.7 LEFT JOIN problem

[Sergei Golubchik]

Hi!

On Nov 22, Santino wrote:
 At 15:23 +0100 22-11-2004, Sergei Golubchik wrote:
 Hi!
 
 On Nov 22, Dilipan Sebastiampillai wrote:
  I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query
  using LEFT JOIN gives me different result.
  The result from 4.0.18 does s real LEFT JOIN but 4.1.7  differs
  sometimes and I don't want that.
 
  I think it comes from an 'optimization'  how can I make a query
  without using the LEFT JOIN optimization of MySQL 4.1.7 ?
 
 What is the query ?
 Can you provide a repeatable test case ?
 
 I have a query that works fine on 4.0.20 but doesn't work in 4.1.7.

Thanks for the test case.
I could repeat the bug using the 4.1.7 distribution, but not the latest
4.1.8 tree - it means that the bug was apparently fixed since 4.1.7
release.

 ==CUT
 CREATE TABLE AULE (
   AUL_ID int(11) NOT NULL auto_increment,
   PRIMARY KEY  (AUL_ID)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 CREATE TABLE OCCUPAZIONI (
   OCC_ID int(11) NOT NULL auto_increment,
   OCC_ID_AUL int(11) NOT NULL,
   OCC_DATA date,
   PRIMARY KEY  (OCC_ID)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 INSERT INTO AULE VALUES (1);
 INSERT INTO AULE VALUES (2);
 INSERT INTO AULE VALUES (3);
 
 INSERT INTO OCCUPAZIONI VALUES (1, 1, '2004-11-10');
 
 select Before index;
 
 select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on 
 OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
 where OCC_ID is null;
 
 alter table OCCUPAZIONI
   add KEY OCC_ID_AUL (OCC_ID_AUL);
 
 select After Index;
 
 select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on 
 OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
 where OCC_ID is null;
 ==CUT
 
 Bug #6307
 I noticed that when I create a table with 2 index (primary + key) the
 Cardinality of the primary key is 0 but the Cardinality of the key is
 null.  If I insert a record in the table the Cardinality of the
 primary key is 1 but the Cardinality of the key is null.  If I do an
 analyze TABLE ... the  Cardinality of both index is OK and the query
 works but with a truncate table ...  the Cardinality of the key is
 null.  If I create the table without the second index, add some
 records and add the second index the cardinality is null.

 At the moment I found that if I define a composite primary key ( old
 PRIMARY+KEY) the query works without the analyze.

I am not sure I understand :(
What do you mean query works ?

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   ___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 4.0.18 - 4.1.7 LEFT JOIN problem

[Dilipan Sebastiampillai]

Hi!
On Nov 22, Dilipan Sebastiampillai wrote:
 

I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query 
using LEFT JOIN gives me different result.
The result from 4.0.18 does s real LEFT JOIN but 4.1.7  differs 
sometimes and I don't want that.

I think it comes from an 'optimization'  how can I make a query 
without using the LEFT JOIN optimization of MySQL 4.1.7 ?
   

What is the query ?
Can you provide a repeatable test case ?
Regards,
Sergei
 

the answer is amazingly wrong !
have a look :
mysql SELECT hosts.name,hosts.hostId, tries.hostId, tries.status FROM 
hosts  LEFT JOIN tries ON tries.hostId=hosts.hostId AND tries.status 
IN('running','waitkill','preemption')  LIMIT 20;
+-+++-+
| name| hostId | hostId | status  |
+-+++-+
| chimp13 |   1530 |   1393 | running |
| chimp13 |   1530 |   1485 | running |
| chimp13 |   1530 |   1418 | running |
| chimp13 |   1530 |   1499 | running |
| chimp13 |   1530 |   1499 | running |
| chimp13 |   1530 |   1438 | running |
| chimp13 |   1530 |   1514 | running |
| chimp13 |   1530 |   1491 | running |
| chimp13 |   1530 |   1587 | running |
| chimp13 |   1530 |   1471 | running |
| chimp13 |   1530 |   1471 | running |
| chimp13 |   1530 |   1416 | running |
| chimp13 |   1530 |   1477 | running |
| chimp13 |   1530 |   1416 | running |
| chimp13 |   1530 |   1477 | running |
| chimp13 |   1530 |   1493 | running |
| chimp13 |   1530 |   1520 | running |
| chimp13 |   1530 |   1518 | running |
| chimp13 |   1530 |   1502 | running |
| chimp13 |   1530 |   1598 | running |
+-+++-+
20 rows in set (0.00 sec)

mysql
. the hostId are not the same althought i do a LEFT JOIN on them ...
but sometimes the answer is ok ...
here is my.cnf for a  Xeon 2.40Ghz  * 2
6 Gig of RAM
   # This is for a large system with memory of 1G-2G where the system
   runs mainly
   # MySQL.
   # The following options will be passed to all MySQL clients
   [client]
   #password= your_password
   port= 3306
   socket= /var/lib/mysql/mysql.sock
   # Here follows entries for some specific programs
   # The MySQL server
   [mysqld]
   port= 3306
   socket= /var/lib/mysql/mysql.sock
   skip-locking
   skip-external-locking
   skip-grant-table  # added by dlp
   log-slow-queries
   log-error
   key_buffer = 512M  # same that key_buffer_size ? dlp
   max_allowed_packet = 1M
   table_cache = 512
   sort_buffer_size = 2M
   read_buffer_size = 100M  # ori=2M
   read_rnd_buffer_size = 100M  # ori= 8M
   myisam_sort_buffer_size = 64M
   thread_cache = 1024 # ori =8
   query_cache_size = 100M  # ori = 32M
   # Try number of CPU's*2 for thread_concurrency
   thread_concurrency = 4
   max_connections = 1000  # dlp
   key_buffer_size = 512M  # dlp
   server-id= 1
   # Point the following paths to different dedicated disks
   tmpdir= /tmp/
   #log-update = /path-to-dedicated-directory/hostname

   innodb_data_home_dir = /var/lib/mysql/innodb/
   innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
   innodb_log_group_home_dir = /var/lib/mysql/innodb/
   innodb_log_arch_dir = /var/lib/mysql/innodb/
   # You can set .._buffer_pool_size up to 50 - 80 %
   # of RAM but beware of setting memory usage too high
   innodb_buffer_pool_size = 1G
   innodb_additional_mem_pool_size = 20M
   # Set .._log_file_size to 25 % of buffer pool size
   innodb_log_file_size = 100M
   innodb_log_buffer_size = 8M
   innodb_flush_log_at_trx_commit = 1
   innodb_lock_wait_timeout = 50
   innodb_file_per_table=off
   innodb_table_locks=off # dlp
   [mysqldump]
   quick
   max_allowed_packet = 16M
   [mysql]
   no-auto-rehash
   # Remove the next comment character if you are not familiar with SQL
   #safe-updates
   [isamchk]
   key_buffer = 512M
   sort_buffer_size = 512M
   read_buffer = 2M
   write_buffer = 2M
   [myisamchk]
   key_buffer = 512M
   sort_buffer_size = 512M
   read_buffer = 2M
   write_buffer = 2M
   [mysqlhotcopy]
   interactive-timeout

--
Dilipan 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 4.0.18 - 4.1.7 LEFT JOIN problem

[Sergei Golubchik]

Hi!

On Nov 23, Dilipan Sebastiampillai wrote:
 
 
 Hi!
 
 On Nov 22, Dilipan Sebastiampillai wrote:
  
 
 I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query 
 using LEFT JOIN gives me different result.
 The result from 4.0.18 does s real LEFT JOIN but 4.1.7  differs 
 sometimes and I don't want that.
 
 I think it comes from an 'optimization'  how can I make a query 
 without using the LEFT JOIN optimization of MySQL 4.1.7 ?
 
 What is the query ?
 Can you provide a repeatable test case ?
 
 the answer is amazingly wrong !
 have a look :
 
 mysql SELECT hosts.name,hosts.hostId, tries.hostId, tries.status FROM 
 hosts  LEFT JOIN tries ON tries.hostId=hosts.hostId AND tries.status 
 IN('running','waitkill','preemption')  LIMIT 20;
 +-+++-+
 | name| hostId | hostId | status  |
 +-+++-+
 | chimp13 |   1530 |   1393 | running |
 | chimp13 |   1530 |   1485 | running |
 | chimp13 |   1530 |   1418 | running |
 | chimp13 |   1530 |   1499 | running |

I agree that it doesn't look right.
But the query alone is not enough for me to repeat the bug.
I need also both tables hosts and tries.
If they are big, you may try to remove unrelated rows, or create a
completely independent test case. Actually you can even upload big
tables if you don't want to spend time on a test case.

But only with a repeatable test case you can make sure that the bug
won't be present in 4.1.8.

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   ___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: 4.0.18 - 4.1.7 LEFT JOIN problem

[Donny Simonton]

Move this:
tries.status IN('running','waitkill','preemption'

to a where clause and remove it from the join.

Never actually tried to do a IN in a join before.  I personally don't think
it should work.

Donny
 -Original Message-
 From: Sergei Golubchik [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, November 23, 2004 2:13 PM
 To: Dilipan Sebastiampillai
 Cc: [EMAIL PROTECTED]
 Subject: Re: 4.0.18 - 4.1.7 LEFT JOIN problem
 
 Hi!
 
 On Nov 23, Dilipan Sebastiampillai wrote:
  
  
  Hi!
  
  On Nov 22, Dilipan Sebastiampillai wrote:
  
  
  I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql
 query
  using LEFT JOIN gives me different result.
  The result from 4.0.18 does s real LEFT JOIN but 4.1.7  differs
  sometimes and I don't want that.
  
  I think it comes from an 'optimization'  how can I make a query
  without using the LEFT JOIN optimization of MySQL 4.1.7 ?
  
  What is the query ?
  Can you provide a repeatable test case ?
  
  the answer is amazingly wrong !
  have a look :
 
  mysql SELECT hosts.name,hosts.hostId, tries.hostId, tries.status FROM
  hosts  LEFT JOIN tries ON tries.hostId=hosts.hostId AND tries.status
  IN('running','waitkill','preemption')  LIMIT 20;
  +-+++-+
  | name| hostId | hostId | status  |
  +-+++-+
  | chimp13 |   1530 |   1393 | running |
  | chimp13 |   1530 |   1485 | running |
  | chimp13 |   1530 |   1418 | running |
  | chimp13 |   1530 |   1499 | running |
 
 I agree that it doesn't look right.
 But the query alone is not enough for me to repeat the bug.
 I need also both tables hosts and tries.
 If they are big, you may try to remove unrelated rows, or create a
 completely independent test case. Actually you can even upload big
 tables if you don't want to spend time on a test case.
 
 But only with a repeatable test case you can make sure that the bug
 won't be present in 4.1.8.
 
 Regards,
 Sergei
 
 --
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
 /_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
___/  www.mysql.com
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

4.0.18 - 4.1.7 LEFT JOIN problem

[Dilipan Sebastiampillai]

I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query 
using LEFT JOIN gives me different result.
The result from 4.0.18 does s real LEFT JOIN but 4.1.7  differs 
sometimes and I don't want that.

I think it comes from an 'optimization'  how can I make a query 
without using the LEFT JOIN optimization of MySQL 4.1.7 ?

thanks in advance
--
Dilipan 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 4.0.18 - 4.1.7 LEFT JOIN problem

[Sergei Golubchik]

Hi!

On Nov 22, Dilipan Sebastiampillai wrote:
 I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query 
 using LEFT JOIN gives me different result.
 The result from 4.0.18 does s real LEFT JOIN but 4.1.7  differs 
 sometimes and I don't want that.
 
 I think it comes from an 'optimization'  how can I make a query 
 without using the LEFT JOIN optimization of MySQL 4.1.7 ?

What is the query ?
Can you provide a repeatable test case ?

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   ___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 4.0.18 - 4.1.7 LEFT JOIN problem

[Santino]

At 15:23 +0100 22-11-2004, Sergei Golubchik wrote:
Hi!
On Nov 22, Dilipan Sebastiampillai wrote:
 I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query
 using LEFT JOIN gives me different result.
 The result from 4.0.18 does s real LEFT JOIN but 4.1.7  differs
 sometimes and I don't want that.
 I think it comes from an 'optimization'  how can I make a query
 without using the LEFT JOIN optimization of MySQL 4.1.7 ?
What is the query ?
Can you provide a repeatable test case ?
Hello,
I have a query that works fine on 4.0.20 but doesn't work in 4.1.7.
I want to search all the rows of table AULE that don't have a record 
in table OCCUPAZIONI so the query is:
select AUL_ID, OCC_ID from
  AULE
 left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
  where OCC_ID is null;

After some tests I find that an index changes the results.
This is a sql command file that reproduce the problem on my Linux 
Fedora core 1 with MySql 4.1.7 :

mysql select version();
++
| version()  |
++
| 4.1.7-standard |
++
1 row in set (0.00 sec)
==CUT
DROP DATABASE IF EXISTS bug;
create database bug;
use bug;
CREATE TABLE AULE (
  AUL_ID int(11) NOT NULL auto_increment,
  PRIMARY KEY  (AUL_ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE OCCUPAZIONI (
  OCC_ID int(11) NOT NULL auto_increment,
  OCC_ID_AUL int(11) NOT NULL,
  OCC_DATA date,
  PRIMARY KEY  (OCC_ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO AULE VALUES (1);
INSERT INTO AULE VALUES (2);
INSERT INTO AULE VALUES (3);
INSERT INTO OCCUPAZIONI VALUES (1, 1, '2004-11-10');
select Before index;
select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on 
OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
where OCC_ID is null;

alter table OCCUPAZIONI
add KEY OCC_ID_AUL (OCC_ID_AUL);
select After Index;
select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on 
OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
where OCC_ID is null;
==CUT

Results:
[EMAIL PROTECTED] bugs]# mysql  v.sql
Before index
Before index
AUL_ID  OCC_ID
2   NULL
3   NULL
After Index
After Index
[EMAIL PROTECTED] bugs]#
Bug #6307
I noticed that when I create a table with 2 index (primary + key) the
Cardinality of the primary
key is 0 but the Cardinality of the key is null.
If I insert a record in the table the Cardinality of the primary key is 1 but
the Cardinality of the
key is null.
If I do an analyze TABLE ... the  Cardinality of both index is OK and the query
works but with a
truncate table ...  the Cardinality of the key is null.
If I create the table without the second index, add some records and add the
second index the
cardinality is null.
At the moment I found that if I define a composite primary key ( old
PRIMARY+KEY) the query
works without the analyze.
Santino

Re: 4.0.18 - 4.1.7 LEFT JOIN problem

[Jeff Smelser]

On Monday 22 November 2004 04:15 pm, Santino wrote:

I can confirm it anyway: I think we have a bug.. Unless someone else see 
something, might want to take this to bugs.mysql.com

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 578 to server version: 4.1.7-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql DROP DATABASE IF EXISTS bug;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql create database bug;
Query OK, 1 row affected (0.29 sec)

mysql
mysql use bug;
Database changed
mysql
mysql CREATE TABLE AULE (
-    AUL_ID int(11) NOT NULL auto_increment,
-    PRIMARY KEY  (AUL_ID)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.11 sec)

mysql
mysql CREATE TABLE OCCUPAZIONI (
-    OCC_ID int(11) NOT NULL auto_increment,
-    OCC_ID_AUL int(11) NOT NULL,
-    OCC_DATA date,
-    PRIMARY KEY  (OCC_ID)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql
mysql INSERT INTO AULE VALUES (1);
Query OK, 1 row affected (0.31 sec)

mysql INSERT INTO AULE VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO AULE VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql
mysql INSERT INTO OCCUPAZIONI VALUES (1, 1, '2004-11-10');
Query OK, 1 row affected (0.05 sec)

mysql select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on
- OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
- where OCC_ID is null;
+++
| AUL_ID | OCC_ID |
+++
|  2 |   NULL |
|  3 |   NULL |
+++
2 rows in set (0.06 sec)

mysql alter table OCCUPAZIONI
- add KEY OCC_ID_AUL (OCC_ID_AUL);
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on
- OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
- where OCC_ID is null;
Empty set (0.03 sec)

mysql 


pgpwBjAhIxG9H.pgp
Description: PGP signature

Repost: Order by RAND + join problem

[Gerske, Sebastian]

Hello list,

im having a hughe problem with the RAND() function

first of all im using mysql 3.23 so subquerys are impossible.


Im having three tables which are joined by ID's now i want to select
a single row random out of the join set whats the best way to do it?

My Table structure is:

table1
---
| id | content   |
| 1  | apple  |
---

relation_table

| id |  id_table1 |  id_table2 |
| 1  |  1 |  1 |
| 2  |  1 |  2 |


table2
-
| id | content |
| 1  | bear |
| 2  | ape  |
-

The result should be somehting like:

Query1 (randomly generated):
--
| table1.id | table1.content | table2.id | table2.content |
| 1| apple  | 1| bear   |
-
or
Query2 (randomly generated):
--
| table1.id | table1.content | table2.id | table2.content |
| 1| apple  | 2| ape |
-

and so on ..

thanks for comments / suggestions / solutions



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

join problem: indexed columns being ignored

[Jim Page - EMF Systems Ltd]

Afternoon all. I wonder if any experts on the join optimiser could help me
out. I have read the docs, including the material on how mysql optimises
joins, and have redesigned my schema somewhat to try and simplify the joins,
and that has worked to some extent. But I am still way off the performance I
should be getting on my problem queries.

My Question: Why don't my queries use the entire index I have created on the
spam(t1) table? Is there any way to force mysql to use my index as I intend?
What factors influence the choice of index/column in this case?

Basics first: I am using MySQL 4.0.18 (mysql.com official build) on a Dell
1750 dual Xeon running slackware 9.1, kernel 2.6.3, loads of memory, loads
of disk space. My my.cnf is pasted in at the end. All tables in this query
are innodb.

My problem: I have 2 problem queries which involve a join on 3 tables. I
have created an index on each table, and all columns required for the join
are indexed. All boolean operations are AND. All conditions are x=y or
x=const. On 2 of the tables, only 1 column is used - no problem there; on
the 'problem' table (spam(t1)) 3 columns are used, and I have created a
multi-column index (SP_RID_STA) which includes all 3 relevant columns. The
problem: according to 'EXPLAIN' mysql is not using the entire index. I have
two sample queries below; the first counts the items in the table, and uses
2/3 columns), and and the second selects the first 10 items (uses 2/3
colums). I am judging the index usage by the 'key_len' value in the explain
output. The first column should be '1', the second '8' and the third '1'. I
am therefore expecting '10' in the key_len result for both queries.

Currently the script which runs these queries times out. I need it really to
take of the order of a couple of seconds. I don't see why this should not be
possible with the right indexes, judging by what I have been abloe to
achieve with similar scripts on the same data.

Thanks in advance
Jim
[EMAIL PROTECTED]


Details:

spam (t1) contains 5317996 rows
recip (t2) conatins 9340685 rows
mailin (t3) contains 6464183 rows

Query 1:

explain SELECT COUNT(*) as NumRecords FROM spam t1, recip t2, mailin t3
WHERE SpamFilter='y' AND t1.RecipID = t2.RecipID AND t2.MailInID =
t3.MailInID AND t1.RecipID  34035098 AND t1.Status=present  ;
+---++-++-+-
++--+
| table | type   | possible_keys   | key| key_len | ref
| rows   | Extra|
+---++-++-+-
++--+
| t1| range  | SP_RID_STA,RecipID  | SP_RID_STA |   9 | NULL
| 467038 | Using where; Using index |
| t2| eq_ref | PRIMARY,MailInID| PRIMARY|   8 |
t1.RecipID  |  1 |  |
| t3| eq_ref | PRIMARY,MID_AID,MID_DID | PRIMARY|   8 |
t2.MailInID |  1 | Using index  |
+---++-++-+-
++--+

== this query takes about 12 seconds to execute ==

Query 2:

explain SELECT t1.*,t3.* FROM spam t1, recip t2, mailin t3 WHERE
SpamFilter='y' AND t1.RecipID = t2.RecipID AND t2.MailInID = t3.MailInID AND
t1.Status=present AND t1.RecipID  34035098 ;
+---++-++-+-
++-+
| table | type   | possible_keys   | key| key_len | ref
| rows   | Extra   |
+---++-++-+-
++-+
| t1| ref| SP_RID_STA,RecipID  | SP_RID_STA |   1 | const
| 294033 | Using where |
| t2| eq_ref | PRIMARY,MailInID| PRIMARY|   8 |
t1.RecipID  |  1 | |
| t3| eq_ref | PRIMARY,MID_AID,MID_DID | PRIMARY|   8 |
t2.MailInID |  1 | |
+---++-++-+-
++-+

== this query takes 90+ seconds to execute ==

Schema:

 CREATE TABLE `spam` (
  `SpamID` bigint(20) NOT NULL auto_increment,
  `RecipID` bigint(20) NOT NULL default '0',
  `Reason` text,
  `TermDate` datetime default NULL,
  `SpamFilter` enum('n','y') NOT NULL default 'n',
  `ImageFilter` enum('n','y') NOT NULL default 'n',
  `Status` enum('present','released','deleted') NOT NULL default 'present',
  PRIMARY KEY  (`SpamID`),
  KEY `ImageFilter` (`ImageFilter`),
  KEY `SP_RID_STA` (`SpamFilter`,`RecipID`,`Status`),
  KEY `RecipID` (`RecipID`)
) TYPE=InnoDB

CREATE TABLE `recip` (
  `RecipID` bigint(20) NOT NULL auto_increment,
  `Date` datetime NOT NULL default '-00-00 00:00:00',
  `AccountID` int(11) NOT NULL default '0',
  `DomainID` int(11) NOT NULL default '0',
  `EndUserID` int(11) NOT NULL default '-1',
  `Recipient` text NOT NULL,
  `MailInID` bigint(20) NOT NULL 

Re: join problem: indexed columns being ignored

[Jim Page - EMF Systems Ltd]

Thanks for the suggestion, but according to explain we are in worse shape
than before. In both cases the multi-column index is ignored.  I am going to
try fiddling with the index col order to see if this helps.

Here is what it comes up with as you suggested:

Query1:

explain SELECT COUNT(*) as NumRecords FROM spam t1 INNER JOIN recip t2 ON
t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t3.MailInID WHERE t1.RecipID
 34035098 AND t1.Status=present;
+---++---+-+-++-
+--+
| table | type   | possible_keys | key | key_len | ref| rows
| Extra|
+---++---+-+-++-
+--+
| t1| range  | RecipID   | RecipID |   8 | NULL   |  488474
| Using where  |
| t2| eq_ref | PRIMARY   | PRIMARY |   8 | t1.RecipID |   1
| Using index  |
| t3| index  | NULL  | PRIMARY |   8 | NULL   | 5572118
| Using where; Using index |
+---++---+-+-++-
+--+

Not sure if the t1 result is better or worse but the t3 line is bad news.

Query2:

explain SELECT t1.*, t3.* FROM spam t1 INNER JOIN recip t2 ON t1.RecipID =
t2.RecipID INNER JOIN mailin t3 ON t2.MailInID = t3.MailInID WHERE
t1.Status=present AND t1.RecipID  34035098;
+---++-+-+-+
-++-+
| table | type   | possible_keys   | key | key_len | ref
| rows   | Extra   |
+---++-+-+-+
-++-+
| t1| range  | RecipID | RecipID |   8 | NULL
| 402604 | Using where |
| t2| eq_ref | PRIMARY,MailInID| PRIMARY |   8 | t1.RecipID
|  1 | |
| t3| eq_ref | PRIMARY,MID_AID,MID_DID | PRIMARY |   8 | t2.MailInID
|  1 | |
+---++-+-+-+
-++-+

Sort of about the same.

 Did you try:
 SELECT COUNT(*) as NumRecords FROM spam t1 INNER JOIN recip t2 0N
 t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t3.MailInID WHERE
 t1.RecipID  34035098 AND t1.Status=present;

 re-written as:
 SELECT t1.*, t3.* FROM spam t1 INNER JOIN recip t2 ON t1.RecipID =
t2.RecipID INNER JOIN mailin t3 ON t2.MailInID = t3.MailInID WHERE
t1.Status=present AND t1.RecipID  34035098;


(This email has been scanned for viruses by www.emf-systems.com)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

LEFT JOIN problem

[Rory McKinley]

Hi List

I am currently running a query that reads records from one table (Table 1) links these 
records to an id value in another table (Table 2)  and returns the result. The tables 
are as follows, with sample data:

Table 1in DB 1: 

line_number | category_name | category_value | line_type
1  | Rent |  100.00|  13
2  | Usage  |50.00|  13
3  | Services   |75.00|  13 

Table 2 in DB 2:

parameter_ID | parameter_trigger | parameter_value
1   |   1| Rent
1   |   2| Usage

The returned records will be line_number, category_value from Table 1 and 
Parameter_Trigger from Table 2 (Table 1 and Table 2 are joined on table 1.Category 
Name = Table 2.Parameter_Value). As you can see from the sample the third record in 
table 1 does not have a matching entry in Table 2. In this case, I want the returned 
record to still show the line_number and category_value, except in place of the 
parameter_trigger the field should be set to zero (so that I can see that I have a 
category name that I haven't accounted for). So I am using the following query:

SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0)
FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON a.category_name = b.parameter_value
WHERE a.line_type = 13 AND b.parameter_ID = 13

The only problem is that the query only returns the first two records and not the 
third record - contrary to my expectations. I am using MySQL 4.0.15-standard together 
with PHPMyAdmin 2.5.3. 

Does anybody know what I am doing wrong?



Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world, 
those who understand binary and those who don't (Unknown)

Re: LEFT JOIN problem

[Roger Baklund]

* Rory McKinley
[...]
 SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0)
 FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON
 a.category_name = b.parameter_value
 WHERE a.line_type = 13 AND b.parameter_ID = 13

 The only problem is that the query only returns the first two
 records and not the third record - contrary to my expectations. I
 am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3.

 Does anybody know what I am doing wrong?

You have a criteria on the B table in the WHERE clause. Move it to the ON
clause:

SELECT ... AS b ON
a.category_name = b.parameter_value AND
b.parameter_ID = 13
  WHERE a.line_type = 13

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[SOLVED]Re: LEFT JOIN problem

[Nobody]

Thanks Roger

That fixed the problem.I was under the impression that I only had to put
the linked columns n the ON and everything else in the WHERElive an
learn I guess.


Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message - 
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Rory McKinley [EMAIL PROTECTED]
Sent: Thursday, November 20, 2003 4:23 PM
Subject: Re: LEFT JOIN problem


 * Rory McKinley
 [...]
  SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0)
  FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON
  a.category_name = b.parameter_value
  WHERE a.line_type = 13 AND b.parameter_ID = 13
 
  The only problem is that the query only returns the first two
  records and not the third record - contrary to my expectations. I
  am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3.
 
  Does anybody know what I am doing wrong?

 You have a criteria on the B table in the WHERE clause. Move it to the ON
 clause:

 SELECT ... AS b ON
 a.category_name = b.parameter_value AND
 b.parameter_ID = 13
   WHERE a.line_type = 13

 --
 Roger


 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [SOLVED]Re: LEFT JOIN problem

[Martijn Tonies]

 Thanks Roger

 That fixed the problem.I was under the impression that I only had to
put
 the linked columns n the ON and everything else in the WHERElive an
 learn I guess.

Ehm, I'm not completely sure, but isn't this invalid behaviour? Can you post
the DDL and INSERT INTO data for these tables?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com

  * Rory McKinley
  [...]
   SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0)
   FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON
   a.category_name = b.parameter_value
   WHERE a.line_type = 13 AND b.parameter_ID = 13
  
   The only problem is that the query only returns the first two
   records and not the third record - contrary to my expectations. I
   am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3.
  
   Does anybody know what I am doing wrong?
 
  You have a criteria on the B table in the WHERE clause. Move it to the
ON
  clause:
 
  SELECT ... AS b ON
  a.category_name = b.parameter_value AND
  b.parameter_ID = 13
WHERE a.line_type = 13
 
  --
  Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [SOLVED]Re: LEFT JOIN problem

[Roger Baklund]

* Martijn Tonies
 * Rory McKinley
  Thanks Roger
 
  That fixed the problem.I was under the impression that I only had to
  put the linked columns n the ON and everything else in the WHERElive
  an learn I guess.

 Ehm, I'm not completely sure, but isn't this invalid behaviour?

Huh? What would be invalid about it?

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [SOLVED]Re: LEFT JOIN problem

[Martijn Tonies]

 * Martijn Tonies
  * Rory McKinley
   Thanks Roger
  
   That fixed the problem.I was under the impression that I only had
to
   put the linked columns n the ON and everything else in the
WHERElive
   an learn I guess.
 
  Ehm, I'm not completely sure, but isn't this invalid behaviour?

 Huh? What would be invalid about it?

That moving parts of the WHERE clause to the JOIN clause returns
a different result set. But as I said: I don't have the full story ... oh
wait,
I found the first message in my mailbox somewhere.

I guess OUTER is optional, and INNER is default - right?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [SOLVED]Re: LEFT JOIN problem

[Martijn Tonies]

  * Martijn Tonies
   * Rory McKinley
Thanks Roger
   
That fixed the problem.I was under the impression that I only
had
 to
put the linked columns n the ON and everything else in the
 WHERElive
an learn I guess.
  
   Ehm, I'm not completely sure, but isn't this invalid behaviour?
 
  Huh? What would be invalid about it?

 That moving parts of the WHERE clause to the JOIN clause returns
 a different result set. But as I said: I don't have the full story ... oh
 wait,
 I found the first message in my mailbox somewhere.

 I guess OUTER is optional, and INNER is default - right?

Right... been doing some more checking. It makes totally sense - I
misread the first report.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [SOLVED]Re: LEFT JOIN problem

[Roger Baklund]

* Martijn Tonies 
 * me
  Huh? What would be invalid about it?
 
 That moving parts of the WHERE clause to the JOIN clause returns
 a different result set. But as I said: I don't have the full story ... oh
 wait, I found the first message in my mailbox somewhere.
 
 I guess OUTER is optional, and INNER is default - right?

This was a LEFT JOIN... :)

-- 
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Slow query join problem

[Allen]

I have two tables.  One table is 13 million rows the other is about 250,000.  I am trying to join the two to find users.  The tables are not indexed and I know that will effect the speed, but the join never completes.  I let it run for about 10 hours and the process was stuck in the sending state, which I don't really understand.  What is taking so long?  The join size should be no greater than 250,000 which is the size of the second table.  Yes???  

I am using the default join_buffer size, which seems to low.  Might this be the problem?

--
Allen Brost


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow query join problem

[Dan Nelson]

In the last episode (Sep 18), Allen said:
 I have two tables.  One table is 13 million rows the other is about 
 250,000.  I am trying to join the two to find users.  The tables are not 
 indexed and I know that will effect the speed, but the join never 
 completes.  I let it run for about 10 hours and the process was stuck in 
 the sending state, which I don't really understand.  What is taking so 
 long?  The join size should be no greater than 250,000 which is the size of 
 the second table.  Yes???  
 I am using the default join_buffer size, which seems to low.  Might this be 
 the problem?

You _have_ to add indexes.  Mysql is currently scanning the entire 250k
table for each record in the big table to find matching records.  You
can verify this by running EXPLAIN SELECT .. etc on your query and
multiplying all the rows values together.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow query join problem

[Joseph Bueno]

Allen wrote:
I have two tables.  One table is 13 million rows the other is about 
250,000.  I am trying to join the two to find users.  The tables are not 
indexed and I know that will effect the speed, but the join never 
completes.  I let it run for about 10 hours and the process was stuck in 
the sending state, which I don't really understand.  What is taking so 
long?  The join size should be no greater than 250,000 which is the size 
of the second table.  Yes??? 
I am using the default join_buffer size, which seems to low.  Might this 
be the problem?


Well, running a join on tables without indexes is like running a simple
select on a table that is the cartesian product of both tables.
In your case, you are trying to run a select on a table with:
13million x 250,000 = 3,250,000,000,000 rows !
I am afraid that you won't get any result soon, even with a big join
buffer :(
Regards,
Joseph Bueno
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slow query join problem

[Allen]

Yes, you are correct.  I was thinking that wouldn't be the case, but it makes sense now.  I added indexes and then the query returned in a few seconds.  Definitely have to have indexes.

Thanks!

Dan Nelson wrote:

In the last episode (Sep 18), Allen said:

I have two tables.  One table is 13 million rows the other is about 
250,000.  I am trying to join the two to find users.  The tables are not 
indexed and I know that will effect the speed, but the join never 
completes.  I let it run for about 10 hours and the process was stuck in 
the sending state, which I don't really understand.  What is taking so 
long?  The join size should be no greater than 250,000 which is the size of 
the second table.  Yes???  
I am using the default join_buffer size, which seems to low.  Might this be 
the problem?


You _have_ to add indexes.  Mysql is currently scanning the entire 250k
table for each record in the big table to find matching records.  You
can verify this by running EXPLAIN SELECT .. etc on your query and
multiplying all the rows values together.
--
Allen Brost
Motorola - DMTS 
[EMAIL PROTECTED]
Work: (847)-435-2019
Cell: (847)-878-7784

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT query with OUTER JOIN - problem

[Jim Winstead]

On Mon, Jun 02, 2003 at 12:48:38PM +0200, Dejan Milenkovic wrote:
 I have two tables, one is containing data about courses and the second one
 is containing data about course start date.
 Is it possible to list all courses with one query which should also return
 earliest  scheduled start dates for courses (which are in the second table).
 I tried something like this:
 SELECT c_d.*, MIN(c_s.start) as start FROM course_data c_d RIGHT OUTER JOIN
 course_start c_s ON c_s.courseid=c_d.courseid WHERE c_s.startNOW() GROUP BY
 c_s.courseid
 But this return only courses that have start date if I replace ON condition
 with 1=1 I get list of all courses but with the same date, if I remove ON
 condition MySQL return error, I also tried replacing ON
 c_s.courseid=c_d.courseid with
 USING (courseid) but that didn't help.
 Any help is appriciated.

 Here are the table definitions and test data.
 CREATE TABLE course_data (
   courseid mediumint(9) NOT NULL auto_increment,
   data varchar(255) NOT NULL default '',
   PRIMARY KEY  (courseid)
 ) TYPE=MyISAM;
 INSERT INTO course_data VALUES (1, 'Test data');
 INSERT INTO course_data VALUES (2, 'Also test data');
 
 CREATE TABLE course_start (
   courseid  mediumint(9) NOT NULL,
   start date NOT NULL default '-00-00'
 ) TYPE=MyISAM;
 INSERT INTO course_start VALUES (1, '2004-12-12');
 
 So I need quey that would return both courses where the start column for
 course 1 would be '2004-12-1 and NULL for course 2.

You don't want a RIGHT OUTER JOIN -- you want a LEFT [OUTER] JOIN,
because you are trying to get a result for each row from the left-most
table. But you also need to explicitly select those results where the
start is NULL. So your query would be:

  SELECT c_d.*, MIN(c_s.start) AS start 
   FROM course_data c_d
   LEFT JOIN course_start c_s USING (courseid)
   WHERE c_s.start  NOW()
  OR c_s.start IS NULL
   GROUP BY c_s.courseid;

This returns:

+--+++
| courseid | data   | start  |
+--+++
|2 | Also test data | NULL   |
|1 | Test data  | 2004-12-12 |
+--+++

Here's an article from SQL-Guru.com that explains the basic join types:

  http://www.sql-guru.com/sql101/basicjoins.html

I hope that helps.

Jim Winstead
MySQL AB

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SELECT query with OUTER JOIN - problem

[Dejan Milenkovic]

Hi,
I have two tables, one is containing data about courses and the second one
is containing data about course start date.
Is it possible to list all courses with one query which should also return
earliest  scheduled start dates for courses (which are in the second table).
I tried something like this:
SELECT c_d.*, MIN(c_s.start) as start FROM course_data c_d RIGHT OUTER JOIN
course_start c_s ON c_s.courseid=c_d.courseid WHERE c_s.startNOW() GROUP BY
c_s.courseid
But this return only courses that have start date if I replace ON condition
with 1=1 I get list of all courses but with the same date, if I remove ON
condition MySQL return error, I also tried replacing ON
c_s.courseid=c_d.courseid with
USING (courseid) but that didn't help.
Any help is appriciated.

Here are the table definitions and test data.
CREATE TABLE course_data (
  courseid mediumint(9) NOT NULL auto_increment,
  data varchar(255) NOT NULL default '',
  PRIMARY KEY  (courseid)
) TYPE=MyISAM;
INSERT INTO course_data VALUES (1, 'Test data');
INSERT INTO course_data VALUES (2, 'Also test data');

CREATE TABLE course_start (
  courseid  mediumint(9) NOT NULL,
  start date NOT NULL default '-00-00'
) TYPE=MyISAM;
INSERT INTO course_start VALUES (1, '2004-12-12');

So I need quey that would return both courses where the start column for
course 1 would be '2004-12-1 and NULL for course 2.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Join problem

[Susan Ator]

O. I've got a headache trying to understand joins. I'm definitely NOT a
database guru.

Why in the world doesn't this work?


SELECT dacspriv_name
FROM dacspriv
WHERE dacspriv_id not in (SELECT dacspriv_id
FROM dacs_access JOIN users
ON dacs_access.user_id=users.user_id
WHERE users.username='sator')

Susan Ator 
Online Services Engineer 
National Public Radio 
Distribution Division 
[EMAIL PROTECTED] 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Join problem

[William R. Mussatto]

 O. I've got a headache trying to understand joins. I'm definitely
 NOT a database guru.

 Why in the world doesn't this work?


 SELECT dacspriv_name
 FROM dacspriv
 WHERE dacspriv_id not in (SELECT dacspriv_id
 FROM dacs_access JOIN users
 ON dacs_access.user_id=users.user_id
 WHERE users.username='sator')

 Susan Ator
 Online Services Engineer
 National Public Radio
 Distribution Division
 [EMAIL PROTECTED]
Short answer is mysql does not do sub-selects (i.e., a select inside of a
select). The join part is not this issue.

William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Join problem

[Ryan McDougall]

 Short answer is mysql does not do sub-selects (i.e., a select inside of a
 select). The join part is not this issue.

Wouldn't this depend on the version... I thought the newest versions, 4.x+,
supported sub-selects.

Ryan

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Join problem

[Susan Ator]

Well, I'm running 3.23.54 on Red Hat 7.3. Given this, how in the world do I
accomplish the following:

I have these tables:
dacspriv - with dacspriv_id,dacspriv_name,short_name
users - with user_id,username
dacs_access - with dacsaccess_id,dacspriv_id,user_id

I need to be able to return a list of dacspriv.short_name where user.user_id
IS NOT in dacs_access but ONLY for that user_id (I have over 1700 users with
multiple mappings in dacs_access).



susan


-Original Message-
From: Ryan McDougall [mailto:[EMAIL PROTECTED]
Sent: Friday, May 30, 2003 12:11 PM
To: mysql
Subject: Re: Join problem


 Short answer is mysql does not do sub-selects (i.e., a select inside of a
 select). The join part is not this issue.

Wouldn't this depend on the version... I thought the newest versions, 4.x+,
supported sub-selects.

Ryan

__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Join problem

[William R. Mussatto]

 Short answer is mysql does not do sub-selects (i.e., a select inside
 of a select). The join part is not this issue.

 Wouldn't this depend on the version... I thought the newest versions,
 4.x+, supported sub-selects.

 Ryan

 __
 Do you Yahoo!?
 Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
 http://calendar.yahoo.com

I sit corrected, 4.1x but its alpha from the manual:

Subqueries are supported in MySQL version 4.1.

I run a production IPP so we run debian with is very far BACK from the
bleeding edge.

William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Join problem

[William R. Mussatto]

 Well, I'm running 3.23.54 on Red Hat 7.3. Given this, how in the world
 do I accomplish the following:

   I have these tables:
   dacspriv - with dacspriv_id,dacspriv_name,short_name
   users - with user_id,username
   dacs_access - with dacsaccess_id,dacspriv_id,user_id

 I need to be able to return a list of dacspriv.short_name where
 user.user_id IS NOT in dacs_access but ONLY for that user_id (I have
 over 1700 users with multiple mappings in dacs_access).



 susan


 -Original Message-
 From: Ryan McDougall [mailto:[EMAIL PROTECTED]
 Sent: Friday, May 30, 2003 12:11 PM
 To: mysql
 Subject: Re: Join problem


 Short answer is mysql does not do sub-selects (i.e., a select inside
 of a select). The join part is not this issue.

 Wouldn't this depend on the version... I thought the newest versions,
 4.x+, supported sub-selects.

 Ryan

Ok let's see:

select dacspriv_name,short_name from dacspriv,users
LEFT JOIN dacs_access on dacs_access.user_id = users.user_id where
dacs_access.user_id is NULL and users.user_id = WHATEVER

The key is dacs_access.user_id is NULL   While I haven't tried it with
your data, I've used this in the past.  For speed recommend user_id's in
all tables be indexed.  Obviously replace 'WHATEVER' with the userID
value.


William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Join problem

[Susan Ator]

Well, it's not blowing up on me and returning a jillion records. Trouble is,
it's also not returning any records at all.

Thanks for the suggestion, though. I'll keep plugging away at it.

susan

-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: Friday, May 30, 2003 3:42 PM
To: [EMAIL PROTECTED]
Cc: Susan Ator
Subject: RE: Join problem


 Well, I'm running 3.23.54 on Red Hat 7.3. Given this, how in the world
 do I accomplish the following:

   I have these tables:
   dacspriv - with dacspriv_id,dacspriv_name,short_name
   users - with user_id,username
   dacs_access - with dacsaccess_id,dacspriv_id,user_id

 I need to be able to return a list of dacspriv.short_name where
 user.user_id IS NOT in dacs_access but ONLY for that user_id (I have
 over 1700 users with multiple mappings in dacs_access).



 susan


 -Original Message-
 From: Ryan McDougall [mailto:[EMAIL PROTECTED]
 Sent: Friday, May 30, 2003 12:11 PM
 To: mysql
 Subject: Re: Join problem


 Short answer is mysql does not do sub-selects (i.e., a select inside
 of a select). The join part is not this issue.

 Wouldn't this depend on the version... I thought the newest versions,
 4.x+, supported sub-selects.

 Ryan

Ok let's see:

select dacspriv_name,short_name from dacspriv,users
LEFT JOIN dacs_access on dacs_access.user_id = users.user_id where
dacs_access.user_id is NULL and users.user_id = WHATEVER

The key is dacs_access.user_id is NULL   While I haven't tried it with
your data, I've used this in the past.  For speed recommend user_id's in
all tables be indexed.  Obviously replace 'WHATEVER' with the userID
value.


William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

JOIN-Problem

[Sorin Marti]

Hi all,

I posted my question a short time ago and didn't receive any answers. So 
I am trying describe it better.

I want to select For every user (from table t_user), every project (from 
t_project) and from every project I want to select every subproject 
which has the id of the project (t_subproject.subproject_id). That works 
so far.

my query :
--
SELECT u.login, p.name, sp.name
FROM t_user u
LEFT JOIN t_project p ON 1
LEFT JOIN t_subproject sp ON p.id = sp.project_id
that gives out something like this:
+---+---+---+
| login | name  | name  |
+---+---+---+
| amg   | Railcalc  | calculation   |
| amg   | Bogie Tools   | Support   |
| amg   | Bogie Tools   | Andi  |
| amg   | Bogie Tools   | Bremsweg  |
| amg   | Klima Tools   | HVAC I|
| amg   | Klima Tools   | HVAC II   |
| amg   | Klima Tools   | Silent Fan|
| amg   | CoC Support   | Ascalp|
| amg   | CoC Support   | Fahrtemp  |
| amg   | CoC Support   | Support   |
[...]

In this example Railcalc has only one subproject,the Bogie Tools-project 
has 3 subprojects and so on.

Now I've got a table t_time which has following rows:

minutes (INT)
owner (which is the same as the login eg.: amg)
subproject_id (which is the same as the id in the table t_subproject)
If there are entries which are:

60  / amg / 5
120 / amg / 5
(in this example '5' is the id of the calculation-subproject)

Then the result should be:

+---+---+---+---+
| login | name  | name  | minutes   |
+---+---+---+---+
| amg   | Railcalc  | calculation   | 180   |
That means I need the SUM of the minutes which belong to a subproject 
and a certain user.

Could anyone PLEASE help me?

   Thanks
  Sorin
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: JOIN-Problem

[Stefan Hinz]

Sorin,

 If there are entries which are:
 60  / amg / 5
 120 / amg / 5
 (in this example '5' is the id of the calculation-subproject)
 Then the result should be:

 +---+---+---+---+
 | login | name  | name  | minutes   |
 +---+---+---+---+
 | amg   | Railcalc  | calculation   | 180   |


 That means I need the SUM of the minutes which belong to a subproject 
 and a certain user.

Okay, here for another wild guess:

SELECT u.login, p.name, sp.name, SUM(t.time)
FROM t_user u
LEFT JOIN t_project p ON 1
LEFT JOIN t_subproject sp ON p.id = sp.project_id
LEFT JOIN t_time t ON ?? = ??
WHERE u.login = 'amg'
GROUP BY p.name

Where I'm not sure if you will need the WHERE clause at all.

Regards,
--
  Stefan Hinz [EMAIL PROTECTED]
  iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: JOIN-Problem

[Sorin Marti]

Stefan Hinz wrote:

Okay, here for another wild guess:

SELECT u.login, p.name, sp.name, SUM(t.time)
FROM t_user u
LEFT JOIN t_project p ON 1
LEFT JOIN t_subproject sp ON p.id = sp.project_id
LEFT JOIN t_time t ON ?? = ??
WHERE u.login = 'amg'
GROUP BY p.name
Where I'm not sure if you will need the WHERE clause at all.

Hi Stefan,

thanks for your answer,

This query doesn't work for me.

It gives back a Sum of time but this is wrong.

Example:
+---+--+-+--+
| login | name | name| SUM  |
+---+--+-+--+
| amg   | Administration   | database minimum|  120 |
| amg   | Allgem. Verbesserung | database upgrade|0 |
| amg   | Ausbildung   | Intern  | 2512 |
| amg   | Ausfallkurve IPM | NULL|0 |
| amg   | Betreuung / Besprech | database minimum|0 |
| amg   | Bezahlte Absenzen| hvjm|0 |
| amg   | Bogie Tools  | Support |  360 |
| amg   | CCP  | CCP |0 |


Mistake 1: There are no time entries for subproject database minimum at all.
Mistake 2: There are time entries for the subproject 'Intern' but thy 
dont belong to 'amg'

and so on...

I really don't get it ...   :-(

Thanks anyway
  Sorin


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re[2]: JOIN-Problem

[Stefan Hinz]

Sorin,

 Okay, here for another wild guess:
 SELECT u.login, p.name, sp.name, SUM(t.time)
 FROM t_user u
 LEFT JOIN t_project p ON 1
 LEFT JOIN t_subproject sp ON p.id = sp.project_id
 LEFT JOIN t_time t ON ?? = ??
 WHERE u.login = 'amg'
 GROUP BY p.name
 Where I'm not sure if you will need the WHERE clause at all.

 This query doesn't work for me.
 It gives back a Sum of time but this is wrong.
 Example:
 +---+--+-+--+
 | login | name | name| SUM  |
 +---+--+-+--+
 | amg   | Administration   | database minimum|  120 |
 | amg   | Allgem. Verbesserung | database upgrade|0 |
 | amg   | Ausbildung   | Intern  | 2512 |
 | amg   | Ausfallkurve IPM | NULL|0 |
 | amg   | Betreuung / Besprech | database minimum|0 |
 | amg   | Bezahlte Absenzen| hvjm|0 |
 | amg   | Bogie Tools  | Support |  360 |
 | amg   | CCP  | CCP |0 |

This doesn't look like an actual output (should be SUM(t.time)). Could
you provide the actual output mysql gives you?

 Mistake 1: There are no time entries for subproject database minimum at all.

Yes, but you have entries for Administration, summing up to 120
minutes. To sum up the times for database mininum etc., you would
have to GROUP BY sp.name.

 Mistake 2: There are time entries for the subproject 'Intern' but thy 
 dont belong to 'amg'

This seems to be rather a database design case than a query case.
You're thinking of Administration as of a project, and of database
minimum as a subproject of Administration. That is not true, as you
can see in the table above. database minimum can be a subproject
of Betreuung/Besprechung as well. So, most probably, there's a
logical break in your database design.

I guess the table design should be as follows:

TABLE user: Has amg etc.
TABLE task: Has Administration etc.
TABLE project: Has database minimum etc.

Those are your base tables (entities). Furthermore, you need a
lookup table like this:

TABLE user_task_project with columns: user_id, task_id, project_id
(the three together form your PRIMARY KEY for that table)

Now, whenever a user does anything (like Administration of database
minimum), insert an entry into that lookup table like this:

INSERT INTO user_task_project VALUES (10,121,42)

Where 10 would be user_id of amg, 121 would be the task_id of
Administration, and 42 would be the project_id of database
minimum.

Or, in other words: As long as you don't have new users, new tasks, or
new projects, you don't touch those base tables at all. For all work
that is done, you only need to insert values into the lookup table.

Hope that helps!

Regards,
--
  Stefan Hinz [EMAIL PROTECTED]
  iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

INNER JOIN problem

[hschassen]

I hope someone can help. I have a table that has serveral records with
the same name but unique datestamps.  I need to show only the records
with the latest datestamp associated with them.  I am enclosing the sql
that I am working with in hopes that someone can see the problem that I
can't.  I crashes at the GROUP BY line.
 
$sql = 
 
SELECT 
P1.siname, P1.imin, P1.siti, P1.sito, P1.sireason, P1.id, P1.date,
P2.siname, MAX(P2.id) AS latest_time
 
FROM $table_name AS P1
 
INNER JOIN $table_name AS P2
 
GROUP BY P2.siname AS P3
 
ON P1.id = P3.latest_time
 
WHERE P1.imin=0
 
;

Any help will be appreciated.

Thanx,
 
Hank Schassen, ACI
CAD Manager
Aria/Jackson Architects
Tel: 206.323.8800
Fax: 206.323.8518
www.araijackson.com
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Temp table / join problem, just keeps saying copy to tmp...

[JStanczak]

Hi all. I'm having trouble this a process that just keeps running. To view
this process I'm using the msql SHOW PROCESSLIST command. Here's the
message I'm seeing:

  38 | bb50 | localhost | bb50 | Query   | 197  | Copying to tmp table |
select  a.pk1,a.sos_id_pk2, a.name, count(q.pk1)
from assessment a  left jo |

This process starts up and locks the assessment table. Pretty soon other
processes that need to access that table start to lock. Here's my setting:

mysql show variables;
++---+
| Variable_name  | Value
|
++---+
| back_log   | 5
|
| connect_timeout| 5
|
| basedir| /usr2/local/blackboard/mysql/
|
| datadir| /usr2/local/blackboard/mysql/data/
|
| delayed_insert_limit   | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| join_buffer| 10481664
|
| flush_time | 0
|
| key_buffer | 65007616
|
| language   |
/usr2/local/blackboard/mysql/share/mysql/english/ |
| log| OFF
|
| log_update | OFF
|
| long_query_time| 10
|
| low_priority_updates   | OFF
|
| max_allowed_packet | 1047552
|
| max_connections| 500
|
| max_connect_errors | 10
|
| max_delayed_insert_threads | 20
|
| max_join_size  | 4294967295
|
| max_sort_length| 1024
|
| max_write_lock_count   | 4294967295
|
| net_buffer_length  | 16384
|
| pid_file   |
/usr2/local/blackboard/mysql/data/bb5mysql.pid|
| port   | 3307
|
| protocol_version   | 10
|
| record_buffer  | 520192
|
| skip_locking   | ON
|
| skip_networking| OFF
|
| socket | /tmp/bb50_mysql.sock
|
| sort_buffer| 15728632
|
| table_cache| 65535
|
| thread_stack   | 131072
|
| tmp_table_size | 20971512
|
| tmpdir | /tmp/
|
| version| 3.22.32
|
| wait_timeout   | 31536000
|
++---+
37 rows in set (0.00 sec)

Can somone tell me what the settings should be to fix this problem? I've
ran this process for about 400 sec and it never failed. I have lots of ram
3GB and lots of HD space. Let me know if you need my config file.


Thank You,

Justin A. Stanczak
Web Manager
Shake Learning Resource Center
Vincennes University
(812)888-5813



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysql join problem (bug?)

[DL Neil]

Haksun,
Confirming that my system-combination (below) works as expected and does NOT
exhibit the XP 'bug' you are experiencing.
My system's output is 1 2 1 2 + 1 4 1 2 + 1 2 1 4 + 1 4 1 4.
Regards,
=dn


 DL,

 Thanks for the reply.
 Can you give more details please?

 If I understand you correctly, you did not reproduce the errors I get.
 In other words, your output was exactly the same as

   f1  f2  f1  f2
  
   1 2 1 2
  
   1 4 1 2
  
   1 2 1 4
  
   1 4 1 4
  


 If that is the case, is this a Win-XP specific bug?
 Can someone running versions other than MySQL 4.0.5-beta on XP verify if
 it is a bug please?

 Thanks!


 Haksun

  -Original Message-
  From: DL Neil [mailto:[EMAIL PROTECTED]]
  Sent: Sunday, December 01, 2002 3:39 PM
  To: Haksun Li; [EMAIL PROTECTED]
  Subject: Re: mysql join problem (bug?)
 
  Hi Haksun,
  Cross-join works perfectly on 3.23.53-max-nt under Win2000 SP2, both
 from
  command line and MySQL Control Center 0.8.6-alpha.
  Regards,
  =dn
 
 
   Hi all,
  
  
  
   I found this join problem using mysql 3.23.53-max-nt (on WinXP).
  
   It works fine on MySQL 3.23.52-log (on Linux).
  
  
  
   Could someone please confirm that it is a bug? Or enlighten me on
   writing the correct sql?
  
  
  
   I have a table and data as shown in the script below.
  
  
  
  
  
   CREATE TABLE test (
  
 f1 int(11) NOT NULL default '0',
  
 f2 int(11) NOT NULL default '0'
  
   ) TYPE=MyISAM;
  
  
  
   #
  
   # Dumping data for table `test`
  
   #
  
  
  
   INSERT INTO test VALUES (1, 2);
  
   INSERT INTO test VALUES (1, 4);
  
  
  
  
  
   Then I do a join with this query:
  
  
  
   SELECT t1.*, t2.* from test as t1, test as t2 where t1.f1 = t2.f1
  
  
  
   Unfortuantley, I got this output.
  
  
  
  
  
   f1  f2  f1  f2
  
   1 2 1 2
  
   1 2 1 2
  
   1 4 1 4
  
   1 4 1 4
  
  
  
  
  
  
  
   I would expect the output like this (which I get on Linux using
 MySQL
   3.23.52-log). I also get this using other DB (e.g. Access).
  
  
  
   f1  f2  f1  f2
  
   1 2 1 2
  
   1 4 1 2
  
   1 2 1 4
  
   1 4 1 4
  
  
  
  
  
   Does anyone else experience the same problem?
  
  
  
   Thanks.
  
  
  
   Haksun
  
  
  
  




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysql join problem (bug?)

[DL Neil]

Hi Haksun,
Cross-join works perfectly on 3.23.53-max-nt under Win2000 SP2, both from
command line and MySQL Control Center 0.8.6-alpha.
Regards,
=dn


 Hi all,



 I found this join problem using mysql 3.23.53-max-nt (on WinXP).

 It works fine on MySQL 3.23.52-log (on Linux).



 Could someone please confirm that it is a bug? Or enlighten me on
 writing the correct sql?



 I have a table and data as shown in the script below.





 CREATE TABLE test (

   f1 int(11) NOT NULL default '0',

   f2 int(11) NOT NULL default '0'

 ) TYPE=MyISAM;



 #

 # Dumping data for table `test`

 #



 INSERT INTO test VALUES (1, 2);

 INSERT INTO test VALUES (1, 4);





 Then I do a join with this query:



 SELECT t1.*, t2.* from test as t1, test as t2 where t1.f1 = t2.f1



 Unfortuantley, I got this output.





 f1  f2  f1  f2

 1 2 1 2

 1 2 1 2

 1 4 1 4

 1 4 1 4







 I would expect the output like this (which I get on Linux using MySQL
 3.23.52-log). I also get this using other DB (e.g. Access).



 f1  f2  f1  f2

 1 2 1 2

 1 4 1 2

 1 2 1 4

 1 4 1 4





 Does anyone else experience the same problem?



 Thanks.



 Haksun






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: mysql join problem (bug?)

[Haksun Li]

DL,

Thanks for the reply.
Can you give more details please?

If I understand you correctly, you did not reproduce the errors I get.
In other words, your output was exactly the same as 

  f1  f2  f1  f2
 
  1 2 1 2
 
  1 4 1 2
 
  1 2 1 4
 
  1 4 1 4
 


If that is the case, is this a Win-XP specific bug?
Can someone running versions other than MySQL 4.0.5-beta on XP verify if
it is a bug please?

Thanks!


Haksun

 -Original Message-
 From: DL Neil [mailto:[EMAIL PROTECTED]]
 Sent: Sunday, December 01, 2002 3:39 PM
 To: Haksun Li; [EMAIL PROTECTED]
 Subject: Re: mysql join problem (bug?)
 
 Hi Haksun,
 Cross-join works perfectly on 3.23.53-max-nt under Win2000 SP2, both
from
 command line and MySQL Control Center 0.8.6-alpha.
 Regards,
 =dn
 
 
  Hi all,
 
 
 
  I found this join problem using mysql 3.23.53-max-nt (on WinXP).
 
  It works fine on MySQL 3.23.52-log (on Linux).
 
 
 
  Could someone please confirm that it is a bug? Or enlighten me on
  writing the correct sql?
 
 
 
  I have a table and data as shown in the script below.
 
 
 
 
 
  CREATE TABLE test (
 
f1 int(11) NOT NULL default '0',
 
f2 int(11) NOT NULL default '0'
 
  ) TYPE=MyISAM;
 
 
 
  #
 
  # Dumping data for table `test`
 
  #
 
 
 
  INSERT INTO test VALUES (1, 2);
 
  INSERT INTO test VALUES (1, 4);
 
 
 
 
 
  Then I do a join with this query:
 
 
 
  SELECT t1.*, t2.* from test as t1, test as t2 where t1.f1 = t2.f1
 
 
 
  Unfortuantley, I got this output.
 
 
 
 
 
  f1  f2  f1  f2
 
  1 2 1 2
 
  1 2 1 2
 
  1 4 1 4
 
  1 4 1 4
 
 
 
 
 
 
 
  I would expect the output like this (which I get on Linux using
MySQL
  3.23.52-log). I also get this using other DB (e.g. Access).
 
 
 
  f1  f2  f1  f2
 
  1 2 1 2
 
  1 4 1 2
 
  1 2 1 4
 
  1 4 1 4
 
 
 
 
 
  Does anyone else experience the same problem?
 
 
 
  Thanks.
 
 
 
  Haksun
 
 
 
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysql join problem (bug?)

[Haksun Li]

Hi all,

 

I found this join problem using mysql 3.23.53-max-nt (on WinXP).

It works fine on MySQL 3.23.52-log (on Linux).

 

Could someone please confirm that it is a bug? Or enlighten me on
writing the correct sql?

 

I have a table and data as shown in the script below.

 

 

CREATE TABLE test (

  f1 int(11) NOT NULL default '0',

  f2 int(11) NOT NULL default '0'

) TYPE=MyISAM;

 

#

# Dumping data for table `test`

#

 

INSERT INTO test VALUES (1, 2);

INSERT INTO test VALUES (1, 4);

 

 

Then I do a join with this query:

 

SELECT t1.*, t2.* from test as t1, test as t2 where t1.f1 = t2.f1

 

Unfortuantley, I got this output.

 

 

f1  f2  f1  f2  

1 2 1 2 

1 2 1 2 

1 4 1 4 

1 4 1 4 

 

 

 

I would expect the output like this (which I get on Linux using MySQL
3.23.52-log). I also get this using other DB (e.g. Access).

 

f1  f2  f1  f2  

1 2 1 2 

1 4 1 2 

1 2 1 4 

1 4 1 4 

 

 

Does anyone else experience the same problem?

 

Thanks.

 

Haksun

 

MySQL Left Join Problem

[Mark Colvin]

I am having some problems seeing posts to the list so if anyone has already
received this mail I apologise. Anyway, I have a two table query where I
want to return records from the first table and if they have corresponding
details in my second table I wish to return those as well. A sample sql
statement I am using is as follows:

select decheader.code, decheader.height, decheader.width,
decplacement.position, decplacement.product from decheader left join
decplacement on decheader.code = decplacement.code where (decheader.code =
'7' and decplacement.product = 'OVRLCKBDG') or (decheader.code = '6'
and decplacement.product = '1029106') or (decheader.code = '5' and
decplacement.product = '1029103') or (decheader.code = '5' and
decplacement.product = '1029104') or (decheader.code = '5' and
decplacement.product = '1029105') or (decheader.code = '5' and
decplacement.product = '1029106') or (decheader.code = '4' and
decplacement.product = '1029104') or (decheader.code = '4' and
decplacement.product = '1029105')

In the above example, all of the codes exist in the decheader table but only
code 7 has a corresponding product in the decplacement table. I would
wish to return all the decheader details for the above codes and the
decplacement details for code 7 as well. This statement actually only
gives me the details for code 0007 and nothing else. I should add that for
each record in the decheader table, there may be one, none or many records
in the decplacement table but I will only be interested in a specific one. I
hope this makes some sort of sense!!



This e-mail is intended for the recipient only and
may contain confidential information. If you are
not the intended recipient then you should reply
to the sender and take no further ation based
upon the content of the message.
Internet e-mails are not necessarily secure and
CCM Limited does not accept any responsibility
for changes made to this message. 
Although checks have been made to ensure this
message and any attchments are free from viruses
the recipient should ensure that this is the case.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

possible left join problem?

[Matt MacDougall]

Hi,

I've got two tables, links and categories.

links ...


  id int(11) NOT NULL auto_increment,
  name text,
  note text,
  link text,
  category1 int(11) NOT NULL default '0',
  category2 int(11) NOT NULL default '0',
  category3 int(11) NOT NULL default '0',
  date_added timestamp(14) NOT NULL,
  rating int(11) NOT NULL default '0',
  how_many_votes int(11) NOT NULL default '0',
  display enum('yes','no') NOT NULL default 'no',
  PRIMARY KEY  (id)

categories ...


  id int(11) NOT NULL auto_increment,
  name text,
  description text,
  display enum('yes','no') NOT NULL default 'yes',
  PRIMARY KEY  (id)

Any one link could belong to as many as 3 categories.  Each link also has a
numerical rating.  What I'm trying to do is find the highest rated link in
each category so I can display the link.  From the mysql docs I've decided
to create a temporary table to figure this out but the sql below isn't
working.  Does anyone see any glaring errors here?  Or do you have thoughts
on how to do this easier?


CREATE TEMPORARY TABLE tmplinks (
id int(11),
name text,
link text,
category int(11) NOT NULL default '0',
rating int(11) NOT NULL default '0',
how_many_votes int(11) NOT NULL default '0');

LOCK TABLES links READ, categories READ, tmplinks WRITE;

INSERT INTO tmplinks SELECT id, name, link, MAX(category1) AS category,
rating, how_many_votes FROM links GROUP BY category1;

INSERT INTO tmplinks SELECT id, name, link, MAX(category2) AS category,
rating, how_many_votes FROM links GROUP BY category2;

INSERT INTO tmplinks SELECT id, name, link, MAX(category3) AS category,
rating, how_many_votes FROM links GROUP BY category3;

SELECT categories.id, categories.name, links.id, links.name, links.link,
links.category1, links.category2, links.category3, links.rating,
links.how_many_votes, tmplinks.category, tmplinks.rating
FROM categories, tmplinks
LEFT JOIN links ON categories.id = links.category1 OR categories.id =
links.category2 OR categories.id = links.category3
WHERE (links.category1=tmplinks.category OR
links.category2=tmplinks.category OR links.category3=tmplinks.category) AND
links.rating=tmplinks.rating AND links.display=yes GROUP BY
categories.name;

UNLOCK TABLES;

DROP TABLE tmplinks;


Thanks much!

-Matt MacDougall


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Join problem in MYSQL

[Egor Egorov]

Defryn,
Monday, July 08, 2002, 5:09:51 AM, you wrote:

D Can anyone have a look at my join.
D It returns errors

D Select O.name, O.amount , P.Productname, P.price
D From Orders AS O
D JOIN Products AS P ON O.product=P.Productid
D Where O.cluster= 'ANP';


D It works fine when I use

D Select O.Name, O.amount, P.productname , P.price
D From Orders as O, Products as P
D Where O.product=P.productid
D AND O.cluster = 'ANP';


D When I use the first example  with INNER JOIN it works as well.
D The book I use as study guide is a few years old so maybe the syntax has changed ??

Yeah, your JOIN syntax incorrect, you can't use ON in simple JOIN
clause. Take a look at:
  http://www.mysql.com/doc/J/O/JOIN.html





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Join problem in MYSQL

[Defryn, Guy]

sql,query


Can anyone have a look at my join.
It returns errors

Select O.name, O.amount , P.Productname, P.price
From Orders AS O
JOIN Products AS P ON O.product=P.Productid
Where O.cluster= 'ANP';


It works fine when I use

Select O.Name, O.amount, P.productname , P.price
From Orders as O, Products as P 
Where O.product=P.productid
AND O.cluster = 'ANP';


When I use the first example  with INNER JOIN it works as well.
The book I use as study guide is a few years old so maybe the syntax has changed ??


Any ideas



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysql...category--unlimited sub category level--self join problem...

[open source devel company]

hello to all mysql list subscribers ,

I have a problem which I think can be solved by SELF
JOIN but I can't figure out how to do it 

here is table definition

CREATE TABLE first (
  firstcolumn int(11) default NULL,
  secondcolumn int(11) default NULL
) TYPE=MyISAM;


INSERT INTO first VALUES (0,1);
INSERT INTO first VALUES (0,2);
INSERT INTO first VALUES (0,3);
INSERT INTO first VALUES (2,4);
INSERT INTO first VALUES (2,5);
INSERT INTO first VALUES (2,6);
INSERT INTO first VALUES (5,7);
INSERT INTO first VALUES (5,8);
INSERT INTO first VALUES (5,9);

Now I use this as a table to track unlimited sub
category branches

like taking the above as example 1 , 2 , 3 are MAIN
categories

and the category subcategory tree will be

2--4, 5, 6

5-7 , 8 , 9

Now I want to delete the following rows using one or
two sql statements

0,2

2,4
2,5
2,6
5,7
5,8
5,9

is it possible ?

I can delete upto the second level using the statement

DELETE from  first where firstcolumn=2 OR
secondcolumn=2

so only the rows

0,2

2,4
2,5
2,6


gets deleted but NOT

5,7
5,8
5,9

 

can you please help me formulate a SQL query which
does that ?

also if the table has a next level of data like

8 , 10

8, 11

8,12

those also must be deleted...

since 10 , 11, 12 are subcategories of 8 , which in
turn is a subcategory of 5 which in turn is a
subcategory of 2 ..and 2 is the main category

so if 2 is deleted all the below TREE must be deleted
too

ok that's it for a DELETE statement

now I want to write a SELECT statement which retrieves
all sub categories , sub sub categories , sub sub sub
categories and so on ...GIVEN a main category ID

so for example based on the above table  I want to
retrieve 4 ,5 ,6 , 7 ,8 ,9 IF GIVEN 2 as the main cat
id

also last but NOT the least ..for managing unlimited
sub category branches is the above database design the
best method ?


Thanks to all

please help


chris



__
Do You Yahoo!?
Yahoo! Health - your guide to health and wellness
http://health.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Join problem, Please help.

[IvanLatysh]

Hi.
I am running MySQL 3.23.44-Max

I have 2 tables.

Table A
+---++
 IDName
+---++
  1 string 1
  2 string 2
+---++

Table B

+---++
 IDName
+---++
  2 string 2
  3 string 3
+---++

And I need result
 +---++
 IDName
+---++
  1 string 1
  2 string 2
  3 string 3
+---++
How I could get (my version of mySQL haven't UNION statement)

Sincerely yours, Ivan Latysh.
[EMAIL PROTECTED]
http://ivan.yourmail.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

LEFT JOIN problem.

[Jon Barker]

Hi All, 
 I have a curious problem with a left join:-

create table foo (a int);
insert into foo values(1);
insert into foo values(2);

create table bar1 (a int, b datetime not null);
insert into bar1 values(1, '25-12-2001');

create table bar2 (a int, b datetime);
insert into bar2 values(1, '25-12-2001');

create table bar3 (a int, b char(20) not null);
insert into bar3 values(1, 'Wensleydale');

select * from foo left outer join bar1 on foo.a=bar1.a;
select * from foo left outer join bar1 on foo.a=bar1.a where b is null;
select * from foo left outer join bar2 on foo.a=bar2.a where b is null;
select * from foo left outer join bar3 on foo.a=bar3.a where b is null;

drop table foo;
drop table bar1;
drop table bar2;
drop table bar3;

The selects produce these results:-

select * from foo left outer join bar1 on foo.a=bar1.a
--
a   a   b
1   1   2025-12-20 01:00:00
2   NULLNULL
--

select * from foo left outer join bar1 on foo.a=bar1.a where b is null
--
--

select * from foo left outer join bar2 on foo.a=bar2.a where b is null
--
a   a   b
2   NULLNULL
--

select * from foo left outer join bar3 on foo.a=bar3.a where b is null
--
a   a   b
2   NULLNULL
--

The problem is that surely the second select should return some 
rows, is this a bug or am I doing something dumb?

I've tried this on 
mysql  Ver 11.15 Distrib 3.23.41, for pc-linux-gnu (i686) (AMD 1 CPU)
and
mysql  Ver 11.15 Distrib 3.23.39, for pc-linux-gnu (i686) (Intel SMP 2 CPU)

Thanks for any assistance,
Jon Barker



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Left Join problem - Please Help

[Miretsky, Anya]

Whenever I try to issue a query which has a left join for two tables and a
where clause for a column in the second table that is a varchar the query
runs indefinitely. If I change the where clause to search on a numeric type
column the query runs and returns expected values.

Does anyone know what I am doing wrong? This is my first time using Mysql,
so if this isn't the right list to post on, please tell me where I can post
beginner mysql questions.

Thanks for your help in advance.

Anya Miretsky
Computer Technology Dept.
Brooklyn Botanic Garden
1000 Washington Avenue
Brooklyn, NY  11225

(718)623-7265
[EMAIL PROTECTED] 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Left Join problem - Please Help

[Rick Emery]

Show us your table structure, some table values and the SELECT statement
that works and the one that doesn't

-Original Message-
From: Miretsky, Anya [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 12:13 PM
To: '[EMAIL PROTECTED]'
Subject: Left Join problem - Please Help


Whenever I try to issue a query which has a left join for two tables and a
where clause for a column in the second table that is a varchar the query
runs indefinitely. If I change the where clause to search on a numeric type
column the query runs and returns expected values.

Does anyone know what I am doing wrong? This is my first time using Mysql,
so if this isn't the right list to post on, please tell me where I can post
beginner mysql questions.

Thanks for your help in advance.

Anya Miretsky
Computer Technology Dept.
Brooklyn Botanic Garden
1000 Washington Avenue
Brooklyn, NY  11225

(718)623-7265
[EMAIL PROTECTED] 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Left Join problem - Please Help

[Rick Emery]

I don't think it's hanging.  I think it's going through the process of
determining joint criteria for 11,900 table entries multiplied by 88,000
other table entries, then doing a text search on the result, which is much
slower than an integer search.

I'm posting this back to the mysql list to see what the experts think.

-Original Message-
From: Miretsky, Anya [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 2:17 PM
To: 'Rick Emery'
Subject: RE: Left Join problem - Please Help


Biblio has 11,901 and keyword has 87,971, also I indexed the keyword column
in the keyword table. Is the fact that I have the same name for a column as
for the table possibly a problem?

-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]] 
Sent: Friday, February 15, 2002 3:17 PM
To: 'Miretsky, Anya'
Subject: RE: Left Join problem - Please Help


How many records are in these tables?

-Original Message-
From: Miretsky, Anya [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 1:51 PM
To: 'Rick Emery'
Subject: RE: Left Join problem - Please Help


Table structure would be: 

Biblio table with the following columns:
 
p_biblio int unsigned not null primary key,
authors varchar(255),
title  varchar(255) ,
citation  varchar(255)


Keyword table with the following columns:

p_keyword int unsigned not null primary key,
fk_biblio int unsigned not null,
Keyword varchar(255)


Sql statement that works:
Select distinct biblio.* from biblio left join keyword on
biblio.p_biblio=keyword.fk_biblio where fk_biblio3;

Sql statement that doesn't work - msql hangs executing this: Select distinct
biblio.* from biblio left join keyword on biblio.p_biblio=keyword.fk_biblio
where keyword=SOMESTRING;


-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]] 
Sent: Friday, February 15, 2002 2:10 PM
To: 'Miretsky, Anya'; '[EMAIL PROTECTED]'
Subject: RE: Left Join problem - Please Help


Show us your table structure, some table values and the SELECT statement
that works and the one that doesn't

-Original Message-
From: Miretsky, Anya [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 12:13 PM
To: '[EMAIL PROTECTED]'
Subject: Left Join problem - Please Help


Whenever I try to issue a query which has a left join for two tables and a
where clause for a column in the second table that is a varchar the query
runs indefinitely. If I change the where clause to search on a numeric type
column the query runs and returns expected values.

Does anyone know what I am doing wrong? This is my first time using Mysql,
so if this isn't the right list to post on, please tell me where I can post
beginner mysql questions.

Thanks for your help in advance.

Anya Miretsky
Computer Technology Dept.
Brooklyn Botanic Garden
1000 Washington Avenue
Brooklyn, NY  11225

(718)623-7265
[EMAIL PROTECTED] 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Left Join problem - Please Help

[Rick Emery]

possibly

-Original Message-
From: Miretsky, Anya [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 2:32 PM
To: 'Rick Emery'
Subject: RE: Left Join problem - Please Help


When I do this without specifying a left join with just :select biblio.*
from biblio,keyword where p_biblio=fk_biblio and keyword=somestring; it
works fairly fast, is it the left join that's making it so slow?

-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]] 
Sent: Friday, February 15, 2002 3:29 PM
To: 'Miretsky, Anya'
Cc: '[EMAIL PROTECTED]'
Subject: RE: Left Join problem - Please Help


I don't think it's hanging.  I think it's going through the process of
determining joint criteria for 11,900 table entries multiplied by 88,000
other table entries, then doing a text search on the result, which is much
slower than an integer search.

I'm posting this back to the mysql list to see what the experts think.

-Original Message-
From: Miretsky, Anya [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 2:17 PM
To: 'Rick Emery'
Subject: RE: Left Join problem - Please Help


Biblio has 11,901 and keyword has 87,971, also I indexed the keyword column
in the keyword table. Is the fact that I have the same name for a column as
for the table possibly a problem?

-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]] 
Sent: Friday, February 15, 2002 3:17 PM
To: 'Miretsky, Anya'
Subject: RE: Left Join problem - Please Help


How many records are in these tables?

-Original Message-
From: Miretsky, Anya [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 1:51 PM
To: 'Rick Emery'
Subject: RE: Left Join problem - Please Help


Table structure would be: 

Biblio table with the following columns:
 
p_biblio int unsigned not null primary key,
authors varchar(255),
title  varchar(255) ,
citation  varchar(255)


Keyword table with the following columns:

p_keyword int unsigned not null primary key,
fk_biblio int unsigned not null,
Keyword varchar(255)


Sql statement that works:
Select distinct biblio.* from biblio left join keyword on
biblio.p_biblio=keyword.fk_biblio where fk_biblio3;

Sql statement that doesn't work - msql hangs executing this: Select distinct
biblio.* from biblio left join keyword on biblio.p_biblio=keyword.fk_biblio
where keyword=SOMESTRING;


-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]] 
Sent: Friday, February 15, 2002 2:10 PM
To: 'Miretsky, Anya'; '[EMAIL PROTECTED]'
Subject: RE: Left Join problem - Please Help


Show us your table structure, some table values and the SELECT statement
that works and the one that doesn't

-Original Message-
From: Miretsky, Anya [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 15, 2002 12:13 PM
To: '[EMAIL PROTECTED]'
Subject: Left Join problem - Please Help


Whenever I try to issue a query which has a left join for two tables and a
where clause for a column in the second table that is a varchar the query
runs indefinitely. If I change the where clause to search on a numeric type
column the query runs and returns expected values.

Does anyone know what I am doing wrong? This is my first time using Mysql,
so if this isn't the right list to post on, please tell me where I can post
beginner mysql questions.

Thanks for your help in advance.

Anya Miretsky
Computer Technology Dept.
Brooklyn Botanic Garden
1000 Washington Avenue
Brooklyn, NY  11225

(718)623-7265
[EMAIL PROTECTED] 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Left Join problem - Please Help

[Roger Baklund]

* Miretsky, Anya
 Select distinct
 biblio.* from biblio left join keyword on
 biblio.p_biblio=keyword.fk_biblio
 where keyword=SOMESTRING;

Try swapping the tables:

Select distinct
biblio.* from keyword left join biblio on
biblio.p_biblio=keyword.fk_biblio
where keyword=SOMESTRING;

The left join is used when there may exist rows in the left table which may
not have a related row in the right table, but you still want to include the
fields from the left table. The fields from the right table are all NULL in
the result row in this case.

In this case you probably don't need the left join:

Select distinct
  biblio.*
from
  keyword, biblio
where
  biblio.p_biblio=keyword.fk_biblio and
  keyword=SOMESTRING;

The above statements does not select anything from the keyword table, but
the where clause use the keyword table only, not the biblio table...

In your case you read all the biblio rows (11,901), and for each of them you
do a lookup on the related keywords matching SOMESTRING... this is rarely
what you want, the exception beeing when there are very very many keywords
and very few biblio rows. It is faster to use the index for all mathing
keywords, and then do the lookup to find the related biblio row.

By simply swapping the table names, you select FROM the keyword table, and
then join with the matching biblio rows.

--
Roger
query


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Join Problem

[Dave Butler]

I am struggling with a join query using MySQL 3.23.31 under AIX 4.3.3. Here 
are the tables involved:

CREATE TABLE sched_acct_cat (
  sched_acct varchar(8) NOT NULL default '',
  linenum int(11) unsigned NOT NULL default '0',
  acct varchar(8) default NULL,
  label varchar(40) default NULL,
  indent smallint(11) unsigned default NULL,
  linetype varchar(8) default NULL,
  pageafter tinyint(3) unsigned default NULL,
  PRIMARY KEY (sched_acct,linenum)
) TYPE=MyISAM;


CREATE TABLE fd (
  acct varchar(8) NOT NULL default '',
  entity varchar(8) NOT NULL default '',
  month varchar(8) NOT NULL default '',
  dataview varchar(8) NOT NULL default '',
  amount double(14,2) default NULL,
  PRIMARY KEY (acct,entity,month,dataview),
  KEY acct(acct,entity,month,dataview)
) TYPE=MyISAM;


The first table is the structure I am trying to achieve in the problem 
query. Note line 5 has a NULL where there is no Account.

Here is sample output from the first table:

select CAT.linenum, CAT.acct
from sched_acct_cat CAT
where CAT.sched_acct = 'INC_STMT'
order by CAT.linenum;

1,A8010
2,A8020
3,A8100
4,A8200
5,
6,AT135
7,A8385
... etc

Here is the problem query. It is pulling in a column of FD data.

select CAT.linenum, CAT.acct, FD.amount
from sched_acct_cat CAT LEFT JOIN fd FD
ON CAT.acct = FD.acct
where CAT.sched_acct = 'INC_STMT'
AND FD.entity='FMCI'
AND FD.dataview='ACTUAL.Y'
AND FD.month='OCT01'
order by CAT.linenum;

Here is the output. Line 5 is missing because of the NULL. Line 4 is missing 
because Account A8200 is 0 for FMCI because it is missing from the fd table.

1,A8010,9
2,A8020, 9
3,A8100, 9
6,AT135, 9
7,A8385, 9
8,A8600, 9
9,A8800, 9
10,A8900, 9
12,AT140, 9
... etc.

I thought the 'LEFT JOIN' clause would keep all the lines and simply leave 
NULLs where it could not provide data. The books I looked at seem to imply 
this.

Thanks for any help on this.

Dave




_
Join the world’s largest e-mail service with MSN Hotmail. 
http://www.hotmail.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Join Problem

[Gerald Clark]

Dave Butler wrote:

 I am struggling with a join query using MySQL 3.23.31 under AIX 4.3.3. 
 Here  are the tables involved:
 
 select CAT.linenum, CAT.acct, FD.amount
 from sched_acct_cat CAT LEFT JOIN fd FD
 ON CAT.acct = FD.acct
 where CAT.sched_acct = 'INC_STMT'
 AND FD.entity='FMCI'
 AND FD.dataview='ACTUAL.Y'
 AND FD.month='OCT01'
 order by CAT.linenum;
 
 Here is the output. Line 5 is missing because of the NULL. Line 4 is 
 missing  because Account A8200 is 0 for FMCI because it is missing 
 from the fd table.
 
 1,A8010,9
 2,A8020, 9
 3,A8100, 9
 6,AT135, 9
 7,A8385, 9
 8,A8600, 9
 9,A8800, 9
 10,A8900, 9
 12,AT140, 9
  etc.
 
 I thought the 'LEFT JOIN' clause would keep all the lines and simply 
 leave  NULLs where it could not provide data. The books I looked at 
 seem to imply  this.
 
 Thanks for any help on this.
 
 Dave
 
Except, FD.entity  will also be NULL, which will cause the AND to fail.


 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Query and join problem with null values

[Michaël Delorme]

Hi guys

I have a problem on a SQL query : I got no records selected. Below
is a sample of my 2 tables :


A table identifying people, containing their adress therefore a ZIP
code. However for some people I don't have their adress, so no ZIP code :

Table People
+--+--+
| PeopleID | zip  | ...
+--+--+
| 1| 1|
| 2| Null |
| 3| Null |
| 4| 3|
| ... |
+--+--+


 Another table describe ZIP codes and cities :

Table City
+-+--+
| zip | city | ...
+-+--+
| 1   | aaa  |
| 2   | bbb  |
| 3   | ccc  |
| 4   | ddd  |
| 5   | eee  |
|... |
+-+--+

Here is my query :

SELECT
  people.peopleId,
  people.zip,
  city.city

FROM 
  people, 
  city

WHERE
  people.zip = city.zip 
  and people.peopleId = 2;

I don't get any selection... wheras I expect :
+--+--+--+
| PeopleID | zip  | city |
+--+--+--+
| 2| Null | Null |
+--+--+--+


Where am I wrong ?
Thanks in advance
Michael


Here are the table definitions :

CREATE TABLE `people` (
  `peopleId` tinyint(1) unsigned NOT NULL auto_increment,
  `zip` varchar(255) default NULL,
  PRIMARY KEY  (`peopleId`)
) 

CREATE TABLE `city` (
  `zip` tinyint(1) unsigned NOT NULL auto_increment,
  `city` varchar(255) default NULL,
  PRIMARY KEY  (`zip`)
)


__
Michael Delorme
GIS Manager
French Mediterranean Botanical Conservancy
mailto:[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query and join problem with null values

[rick herbel]

Michael

do the first join
people.zip = city.zip

yo get 2 rows from people 1 and 3

notice there is no peopleid 2 from first join so correct answer is to retun
null

Rick

-Original Message-
From: Michaël Delorme [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 07, 2001 11:00 AM
To: [EMAIL PROTECTED]
Subject: Query and join problem with null values


Hi guys

I have a problem on a SQL query : I got no records selected. Below
is a sample of my 2 tables :


A table identifying people, containing their adress therefore a ZIP
code. However for some people I don't have their adress, so no ZIP code :

Table People
+--+--+
| PeopleID | zip  | ...
+--+--+
| 1| 1|
| 2| Null |
| 3| Null |
| 4| 3|
| ... |
+--+--+


 Another table describe ZIP codes and cities :

Table City
+-+--+
| zip | city | ...
+-+--+
| 1   | aaa  |
| 2   | bbb  |
| 3   | ccc  |
| 4   | ddd  |
| 5   | eee  |
|... |
+-+--+

Here is my query :

SELECT
  people.peopleId,
  people.zip,
  city.city

FROM
  people,
  city

WHERE
  people.zip = city.zip
  and people.peopleId = 2;

I don't get any selection... wheras I expect :
+--+--+--+
| PeopleID | zip  | city |
+--+--+--+
| 2| Null | Null |
+--+--+--+


Where am I wrong ?
Thanks in advance
Michael


Here are the table definitions :

CREATE TABLE `people` (
  `peopleId` tinyint(1) unsigned NOT NULL auto_increment,
  `zip` varchar(255) default NULL,
  PRIMARY KEY  (`peopleId`)
)

CREATE TABLE `city` (
  `zip` tinyint(1) unsigned NOT NULL auto_increment,
  `city` varchar(255) default NULL,
  PRIMARY KEY  (`zip`)
)


__
Michael Delorme
GIS Manager
French Mediterranean Botanical Conservancy
mailto:[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query and join problem with null values

[Michael Delorme]

Thanks for your answer. It's quite logical indeed...

I'm new to SQL !

Does it mean that I can't query on several tables at once if some keys have
a NULL value or is there another way ?

What I want is one line for my peopleId 2, built with informations from
several tables... Or in other words I want to get this virtual table and
then I want to be able to scroll through every people to display each
people information (sorry if my english is not clear) :

+--+--+--+
| PeopleID | zip  | city |
+--+--+--+
| 1| 1| aaa  |
+--+--+--+
| 2| Null | Null |
+--+--+--+
| 3| Null | Null |
+--+--+--+
| 4| 3| ccc  |
+--+--+--+



Thanks anyway
Michael


A 11:05 07/12/2001 -0800, vous avez écrit :
Michael

do the first join
people.zip = city.zip

yo get 2 rows from people 1 and 3

notice there is no peopleid 2 from first join so correct answer is to retun
null

Rick

-Original Message-
From: Michaël Delorme [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 07, 2001 11:00 AM
To: [EMAIL PROTECTED]
Subject: Query and join problem with null values


Hi guys

I have a problem on a SQL query : I got no records selected. Below
is a sample of my 2 tables :


A table identifying people, containing their adress therefore a ZIP
code. However for some people I don't have their adress, so no ZIP code :

Table People
+--+--+
| PeopleID | zip  | ...
+--+--+
| 1| 1|
| 2| Null |
| 3| Null |
| 4| 3|
| ... |
+--+--+


 Another table describe ZIP codes and cities :

Table City
+-+--+
| zip | city | ...
+-+--+
| 1   | aaa  |
| 2   | bbb  |
| 3   | ccc  |
| 4   | ddd  |
| 5   | eee  |
|... |
+-+--+

Here is my query :

SELECT
  people.peopleId,
  people.zip,
  city.city

FROM
  people,
  city

WHERE
  people.zip = city.zip
  and people.peopleId = 2;

I don't get any selection... wheras I expect :
+--+--+--+
| PeopleID | zip  | city |
+--+--+--+
| 2| Null | Null |
+--+--+--+


Where am I wrong ?
Thanks in advance
Michael


Here are the table definitions :

CREATE TABLE `people` (
  `peopleId` tinyint(1) unsigned NOT NULL auto_increment,
  `zip` varchar(255) default NULL,
  PRIMARY KEY  (`peopleId`)
)

CREATE TABLE `city` (
  `zip` tinyint(1) unsigned NOT NULL auto_increment,
  `city` varchar(255) default NULL,
  PRIMARY KEY  (`zip`)
)


__
Michael Delorme
GIS Manager
French Mediterranean Botanical Conservancy
mailto:[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query and join problem with null values

[Etienne Marcotte]

mysql create table users(
- usrID smallint unsigned auto_increment,
- usrZip char(5),
- primary key(usrID));
Query OK, 0 rows affected (0.00 sec)

mysql create table cities(
- ctyZip char(5) not null,
- ctyName varchar(30) not null,
- primary key(ctyZip));
Query OK, 0 rows affected (0.00 sec)

[Insert some values]

mysql select * from users;
+---++
| usrID | usrZip |
+---++
| 1 | 90210  |
| 2 | 12345  |
| 3 | 30300  |
| 4 | NULL   |
+---++
4 rows in set (0.00 sec)

mysql select * from cities;
++---+
| ctyZip | ctyName   |
++---+
| 90210  | Beverly Hills |
| 12345  | Fubar Town|
| 30300  | Rowland City  |
++---+
3 rows in set (0.00 sec)

mysql select usrID, usrZip, ctyName from users left join cities on
usrZip=ctyZip;
+---++---+
| usrID | usrZip | ctyName
+---++---+
| 1 | 90210  | Beverly Hills |
| 2 | 12345  | Fubar Town|
| 3 | 30300  | Rowland City  |
| 4 | NULL   | NULL  |
+---++---+
4 rows in set (0.00 sec)

mysql select usrID, usrZip, ctyName from users left join cities on
usrZip=ctyZip where usrID=4;
+---++---+
| usrID | usrZip | ctyName
+---++---+
| 4 | NULL   | NULL  |
+---++---+
1 row in set (0.00 sec)

Is that what you are looking for?

sorry I redone your table definitions, but you should have the same type
of data for the linked keys (in thsi case you had varchar255 for zip in
the peoples table and a tinyint for the zip in the city table.

HTH

Etienne


Michaël Delorme wrote:
 
 Hi guys
 
 I have a problem on a SQL query : I got no records selected. Below
 is a sample of my 2 tables :
 
 
 A table identifying people, containing their adress therefore a ZIP
 code. However for some people I don't have their adress, so no ZIP code :
 
 Table People
 +--+--+
 | PeopleID | zip  | ...
 +--+--+
 | 1| 1|
 | 2| Null |
 | 3| Null |
 | 4| 3|
 | ... |
 +--+--+
 
 
  Another table describe ZIP codes and cities :
 
 Table City
 +-+--+
 | zip | city | ...
 +-+--+
 | 1   | aaa  |
 | 2   | bbb  |
 | 3   | ccc  |
 | 4   | ddd  |
 | 5   | eee  |
 |... |
 +-+--+
 
 Here is my query :
 
 SELECT
   people.peopleId,
   people.zip,
   city.city
 
 FROM
   people,
   city
 
 WHERE
   people.zip = city.zip
   and people.peopleId = 2;
 
 I don't get any selection... wheras I expect :
 +--+--+--+
 | PeopleID | zip  | city |
 +--+--+--+
 | 2| Null | Null |
 +--+--+--+
 
 Where am I wrong ?
 Thanks in advance
 Michael
 
 Here are the table definitions :
 
 CREATE TABLE `people` (
   `peopleId` tinyint(1) unsigned NOT NULL auto_increment,
   `zip` varchar(255) default NULL,
   PRIMARY KEY  (`peopleId`)
 )
 
 CREATE TABLE `city` (
   `zip` tinyint(1) unsigned NOT NULL auto_increment,
   `city` varchar(255) default NULL,
   PRIMARY KEY  (`zip`)
 )
 
 __
 Michael Delorme
 GIS Manager
 French Mediterranean Botanical Conservancy
 mailto:[EMAIL PROTECTED]
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
Etienne Marcotte
Specifications Management - Quality Control
Imperial Tobacco Ltd. - Montreal (Qc) Canada
514.932.6161 x.4001

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query and join problem with null values

[Michael Delorme]

Thanks ! It's exactlly what I wanted.

I got the same result a few seconds ago using EMS MySQL Manager but I
didn't understand how. Your example is much better !

What is the difference between a LEFT JOIN and a LEFT OUTER JOIN ?

And of course you're right for the data type ;  

Thanks again


A 15:16 07/12/2001 -0500, vous avez écrit :
mysql create table users(
- usrID smallint unsigned auto_increment,
- usrZip char(5),
- primary key(usrID));
Query OK, 0 rows affected (0.00 sec)

mysql create table cities(
- ctyZip char(5) not null,
- ctyName varchar(30) not null,
- primary key(ctyZip));
Query OK, 0 rows affected (0.00 sec)

[Insert some values]

mysql select * from users;
+---++
| usrID | usrZip |
+---++
| 1 | 90210  |
| 2 | 12345  |
| 3 | 30300  |
| 4 | NULL   |
+---++
4 rows in set (0.00 sec)

mysql select * from cities;
++---+
| ctyZip | ctyName   |
++---+
| 90210  | Beverly Hills |
| 12345  | Fubar Town|
| 30300  | Rowland City  |
++---+
3 rows in set (0.00 sec)

mysql select usrID, usrZip, ctyName from users left join cities on
usrZip=ctyZip;
+---++---+
| usrID | usrZip | ctyName
+---++---+
| 1 | 90210  | Beverly Hills |
| 2 | 12345  | Fubar Town|
| 3 | 30300  | Rowland City  |
| 4 | NULL   | NULL  |
+---++---+
4 rows in set (0.00 sec)

mysql select usrID, usrZip, ctyName from users left join cities on
usrZip=ctyZip where usrID=4;
+---++---+
| usrID | usrZip | ctyName
+---++---+
| 4 | NULL   | NULL  |
+---++---+
1 row in set (0.00 sec)

Is that what you are looking for?

sorry I redone your table definitions, but you should have the same type
of data for the linked keys (in thsi case you had varchar255 for zip in
the peoples table and a tinyint for the zip in the city table.

HTH

Etienne


Michaël Delorme wrote:
 
 Hi guys
 
 I have a problem on a SQL query : I got no records selected. Below
 is a sample of my 2 tables :
 
 
 A table identifying people, containing their adress therefore a ZIP
 code. However for some people I don't have their adress, so no ZIP code :
 
 Table People
 +--+--+
 | PeopleID | zip  | ...
 +--+--+
 | 1| 1|
 | 2| Null |
 | 3| Null |
 | 4| 3|
 | ... |
 +--+--+
 
 
  Another table describe ZIP codes and cities :
 
 Table City
 +-+--+
 | zip | city | ...
 +-+--+
 | 1   | aaa  |
 | 2   | bbb  |
 | 3   | ccc  |
 | 4   | ddd  |
 | 5   | eee  |
 |... |
 +-+--+
 
 Here is my query :
 
 SELECT
   people.peopleId,
   people.zip,
   city.city
 
 FROM
   people,
   city
 
 WHERE
   people.zip = city.zip
   and people.peopleId = 2;
 
 I don't get any selection... wheras I expect :
 +--+--+--+
 | PeopleID | zip  | city |
 +--+--+--+
 | 2| Null | Null |
 +--+--+--+
 
 Where am I wrong ?
 Thanks in advance
 Michael
 
 Here are the table definitions :
 
 CREATE TABLE `people` (
   `peopleId` tinyint(1) unsigned NOT NULL auto_increment,
   `zip` varchar(255) default NULL,
   PRIMARY KEY  (`peopleId`)
 )
 
 CREATE TABLE `city` (
   `zip` tinyint(1) unsigned NOT NULL auto_increment,
   `city` varchar(255) default NULL,
   PRIMARY KEY  (`zip`)
 )
 
 __
 Michael Delorme
 GIS Manager
 French Mediterranean Botanical Conservancy
 mailto:[EMAIL PROTECTED]
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
Etienne Marcotte
Specifications Management - Quality Control
Imperial Tobacco Ltd. - Montreal (Qc) Canada
514.932.6161 x.4001

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php