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

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

[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: 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]
> 


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.


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\"" 
> >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]

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

[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\"" 
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

[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: 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]

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]

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

[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

[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

[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

[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

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

Re: Join problem, Please help.

[Christopher Thompson]

On Monday 01 April 2002 10:56 am, IvanLatysh wrote:
> 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)

Either upgrade your version of MySQL or use a temporary table.  Copy table A 
into the temp table (T), then copy table B into T.  Then select from T.

-
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

RE: Join problem, I think

[Kay Bowen]

Neil,

I did a quick test of your data and this is what I got to work.

SELECT item.item, titles.title FROM item LEFT JOIN titles ON
item.title_id = titles.title_id;

The left join selects all the items from the 'left' table even if there
are no matching entries in the 'right' table.  A bit of a warning
here... if there is no match, then the "NULL" value is inserted into
that spot.

Hope it helps,

K

--
Kay Bowen

Tec-Masters, Inc.
Advanced Studies and Research Center



-
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

[Andrew Murphy]

You could try using an AND in the WHERE statement.

EG.  SELECT DISTINCT R.ID, R.CatCode, R.Title
 FROM Recipes R, Ingredients I
 WHERE I.Description LIKE "%$SearchKey%"
 AND R.ID = I.ID;

Hope this helps.

Andrew Murphy


-Original Message-
From: Urb LeJeune [mailto:[EMAIL PROTECTED]]
Sent: 29 August 2001 3:32 pm
To: [EMAIL PROTECTED]
Subject: Join Problem


I've been staring at the following query for a day. I'm sure it's
something obvious but I just don't see it.

There are two table, Recipes which contains one row per
recipe. The second in Ingredients while holds one row per
ingredient. The Ingredients table has a field containing the
parent recipe ID. I want to perform a query on the Ingredients
Description field and return from the Recipes table distinct
parent ID, Category Code, and Title. Here is my query:

SELECT DISTINCT R.ID, R.CatCode, R.Title
   FROM Recipes R, Ingredients I
   WHERE I.Description LIKE "%$SearchKey%"

If the LIKE clause produces any match, all Recipe rows are returned.
If there are no matches, zero rows are returned. To wit. if
$SearchKey = "e" all rows in Recipes are returned as they should.
If $SearchKey = "rice" all rows in Recipes are returned but only one
should be returned.

Any suggests would be appreciated.

Urb


-
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