Here's another variant:
All businesses with a record in any year but 2004 that have a package other
than 16. (This will not find any businesses who have records ONLY in 2004
or that ONLY have package 16.)
SELECT *
FROM business AS b
INNER JOIN records AS r
on b.b_id=r.r_b_id
AND r_date<>'2004'
INNER JOIN packages AS p
on p.p_id=r.r_p_id
AND p_id<>'16'
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
[EMAIL PROTECTED]
To: Michael Baerwolf <[EMAIL
PROTECTED]>
06/28/2004 04:01 cc: [EMAIL PROTECTED]
PM Fax to:
Subject: Re: Query Help
You need to flip the business table around your join so that you get all of
the businesses listed and check for the appropriate NULL values in the
other tables.
This will give you all of the business that neither have a "record" in 2004
nor will they be part of "package" 16
SELECT *
FROM business AS b
LEFT JOIN records AS r
on b.b_id=r.r_b_id
AND r_date = '2004'
LEFT JOIN packages AS p
on p.p_id=r.r_p_id
AND p_id='16'
WHERE p.p_ID is null and r.r_p_id is null
If you wanted to see businesses that did not have a record in 2004 but were
part of package 16 then do this
SELECT *
FROM business AS b
LEFT JOIN records AS r
on b.b_id=r.r_b_id
LEFT JOIN packages AS p
on p.p_id=r.r_p_id
AND p_id='16'
WHERE p.p_ID is not null and r.r_date <> '2004'
Or businesses not part of part of package 16 for any year other than 2004
SELECT *
FROM business AS b
LEFT JOIN records AS r
on b.b_id=r.r_b_id
LEFT JOIN packages AS p
on p.p_id=r.r_p_id
WHERE p.p_ID is not null
AND p_id <>'16'
and r.r_date <> '2004'
(because a record in P, regardless of package, will not exist unless there
is a record in R, the existence of a record in P implies a record in R so
we do not have to check for "AND r.r_date IS NOT NULL".)
Once you start dealing in "negative" queries, you can get yourself into a
lot of "logical" trouble very quickly. Queries generally perform better
when you are looking _for_ something and not looking for _everything but_
something. What makes this so hard to get right is that any NULL
comparisons will always be FALSE or NULL.
localhost.warehouse2>select (null <> '2004'),('2003' <> '2004'), ('2004' <>
'2004') ;
+------------------+--------------------+--------------------+
| (null <> '2004') | ('2003' <> '2004') | ('2004' <> '2004') |
+------------------+--------------------+--------------------+
| NULL | 1 | 0 |
+------------------+--------------------+--------------------+
1 row in set (0.00 sec)
+---------------------------------------------------+
| if((null<>'2004'),'null not match', 'null match') |
+---------------------------------------------------+
| null match |
+---------------------------------------------------+
1 row in set (0.01 sec)
And once you start using LEFT and RIGHT JOINS, you are potentially
introducing a lot of NULL values into the data. This is like dealing with
anti-matter, you must work carefully to make sure you don't wipe out the
wrong rows of data from your results.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Michael Baerwolf
<[EMAIL PROTECTED] To:
[EMAIL PROTECTED]
s.com> cc:
Fax to:
06/25/2004 09:22 Subject: Query Help
PM
Hello,
I'm having some problems with a join I've been trying. Here's the table
structure
CREATE TABLE `business` (
`b_id` int(5) NOT NULL auto_increment,
`b_name` varchar(100) default NULL,
`b_contact` varchar(100) default NULL,
`b_address` varchar(100) default NULL,
`b_city` varchar(50) default NULL,
`b_state` char(2) default NULL,
`b_zip` varchar(25) default NULL,
`b_phone` varchar(20) default NULL,
`b_fax` varchar(20) default NULL,
`b_dcn` varchar(10) default NULL,
PRIMARY KEY (`b_id`)
) TYPE=MyISAM;
CREATE TABLE `packages` (
`p_id` int(5) NOT NULL auto_increment,
`p_name` varchar(75) default NULL,
PRIMARY KEY (`p_id`)
) TYPE=MyISAM;
CREATE TABLE `records` (
`r_id` int(5) NOT NULL auto_increment,
`r_b_id` int(5) default NULL,
`r_p_id` int(5) default NULL,
`r_sold` tinyint(1) default NULL,
`r_date` year(4) default NULL,
PRIMARY KEY (`r_id`)
) TYPE=MyISAM;
I've worked out this to retrieve records based on a package id and year.
SELECT * FROM records AS r
LEFT JOIN business AS b on (b.b_id=r.r_b_id)
LEFT JOIN packages AS p on (p.p_id=r.r_p_id)
WHERE p_id='16' AND r_date = '2004';
Here's where I'm having trouble. I also need to retrieve all the
businesses not in the results of the above query. I've been playing
around with this for a couple of days now with no luck. Any help would
be greatly appreciated.
Thanks in advance,
Mike
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
