MYSQL :: Subselect With NULL

MySQL 4.0.17 Data Scrambles, Not-null Fields Become Null, Etc.
I'm having the most bizarre problem with PHP/MySQL that I've ever
faced and it's an urgent matter (of course) to try to fix it ASAP.

We have one record inserted into a table with 75 columns, whereby half
of the columns are not-null, but for some bizarre reason, the fields
are null. The rest of the columns that are null are completely
scrambled (e.g. "first_name" is in "address2", "fax" is in "url", "url"
is in "last_name", etc.)

This just recently occurred and seems to have no history otherwise. It
occurred sometime on Saturday when the record was inserted but logs
indicate no unusual activity at that time.

Has anyone seen this before and found a way to prevent it from
occurring again?

Getting Rid Of Subselect
I have a table, which -- simplified -- looks like this:

create table access_logs (
session_id varchar(32),
request_uri varchar(32)
);
Each pageview logs the users session-id + the request-uri. Now, to determine how many visitors followed a specific path, I need to select the number of sessions, which have a row including specific request_uri.

This is my own feeble attempt, but I have a feeling that this could be rewritten to get rid of the subselects:

select session_id
from access_logs
where session_id in (select session_id
from access_logs
where request_uri = "landing-page.php")
and session_id in (select session_id
from access_logs
where request_uri = "exit-page.php")
group by session_id;

Getting Around Subselect
My knowledge of SQL is basic so I need some help developing a query. Suppose we have a table called BID where each row is a bid on an item up for auction. The relevant columns are BID_ID which is the primary key, ITEM_ID which identifies the item, and a BID_DATE which records the datetime of the bid.

I would like to find the most recent bid for each distinct ITEM_ID in the table. I've worked out the query below which seems to do the job. However, I need to find a query that will work on a pre-4.1 server which does not support subqueries. Is there a way to re-state this query without using a subselect? Perhaps using some kind of join?

SELECT *
FROM BID, (SELECT ITEM_ID AS IID, max(BID_DATE) as MAXDATE
FROM BID
GROUP BY ITEM_ID) as MAXDATES
WHERE
(ITEM_ID=IID) and (BID_DATE=MAXDATE);

Subselect
I had some SQL calls which worked fine on a v4.1 server and now I've moved to another one which is 4.0.24 and certain subselects no longer work. Is there any basic way to convert statements such as this:

SELECT a.name, (SELECT COUNT(*) FROM table2 AS b WHERE b.id=a.id) as count FROM table1 AS a So that it conforms to the 4.0.x standard?

Subselect
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:

locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list. Code:

Subselect
Let's say i got this query:
select user.id, (select count(*) as posts_number from posts where posts.user_id = user.id), and some other fields, and a lot of joins here.Is it any way to *say* to mysql that the current user.id selected, is the one in the subselect ?
(the one from select "user.id" and the one in where ... = "user.id")

Subselect
Unless I'm wrong, here's a way to do a subquery (inner join two tables, then inner join the resulting table with a third table). It takes advantage of the two different ways of expressing an inner join ("INNER JOIN", and "t1, t2 WHERE...") to express two separate inner joins within a single statement.
SELECT p.p_id, v2.v_name FROM t_project p, t_volunteer v INNER JOIN t_volunteer v2 ON p.p_id=v2.p_id WHERE p.p_id=v.p_id AND v.v_name LIKE "%mike%";
Is this a technique that people use often? I couldn't see it documented in my SQL book ("MySQL", by Paul DuBois), even though it seems like a useful technique for what is effectively a subselect.

Subselect
I have been held up long enough on the query time to ask for help. Its basically a subselect that never returns.

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id IN ( SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC ) ORDER BY it.org_id ASC

If I break it up into 2 seperate

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id = 09238323 ORDER BY it.org_id ASC

SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC

They both return expected values.

Subselect / AS
SELECT
a.id
(SELECT width, height, filename FROM photos WHERE user_id = a.id LIMIT 0,1) AS (width, height, filename)
FROM users a

ORDER BY a.datestamp DESC

you can see my example, using with AS (example).
How can i extract values from subselects?

Slow Subselect
I've got two tables:
lo_users: nickname|id|...
lo_friends: from|to|...

The following query takes < 0.01 sec:
SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1'

...but if I use it in a subselect, the whole thing takes about 0.54 sec:
SELECT u.nickname FROM (SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1') f LEFT JOIN lo_users u ON u.id = f.userid

What can I do to make the query faster? "from" and "to" are indexed and lo_users.id is the primary key.

Subselect In 4.0.12-max With -- New Option
I was reading the manual and it said that the subselect is only
available in 4.1 or using the 4.0.12 with the mysqld =96new command line
to start it.

But it doesn=92t working!! So I downloaded the 4.1 alpha version with =
the
same problem. The error is:

ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near =85.

Any ideas? I need subselect working.

Delete Subselect
I know that MySQL 3.23.nnn did not support a delete subselect, just wondering
what the best/most efficient way to do the following is:

delete from table_a where table_a.column_1 in ( select column_1 from table_b);

Assuming that column_1 is the same data type and size in both table_a and table_b.

Update Self Subselect
I have a log table that creates a row for every page view. I have a field called "flagged" which defaults to 0. I'm trying to update the flagged field to 1 when the ip count is greater then 30... This is what I have but I get the error "You can't specify target table 'ip_log' for update in FROM clause".

UPDATE `ip_log` SET `flagged` = 1
WHERE `ip` IN(
SELECT `ip`
FROM `ip_log`
GROUP BY `ip` HAVING COUNT(*) > 30
)

Subselect Wierdness
I am trying to get 3 active article IDs from the table ARTICLES for a random active feed from table FEEDS. Here is the query I have:

CODEselect AID, a.fid as FID from ARTICLES a where active='Y' and a.fid = (SELECT f.fid FROM FEEDS f where active='Y' ORDER BY RAND() desc limit 1) limit 3;

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list.

Get Last Records Details With A Subselect?
I have a ticketsystem where each ticket belongs to an user and each user can insert a couple of messages to one ticket. Therefore I have implemented a date field (used as primary key). Now I want to get details from the last entry belongs to a ticketid.

kdn_message:
updated (date)
ticketid (int)
kdnr (int)
detail
state

select * from kdn_message t
where updated in (select max(updated) as updated from kdn_message group by ticketid where ticketid=t.ticketid order by updated desc)

what is wrong i this statement?

Convert A Subselect To Inner Join
I was developing a php/postuke app for a client and I wrote two of my SQL queries with subselects. I found out after I was done that they were pretty much stuck with MySQL 4.0.x for awhile, so I need to revamp my queries to avoid subselects. The query uses three tables:

nuke_gwbt_guild_halls
nuke_gwbt_guild_halls_notes
nuke_gwbt_matches

I am getting all of the fields in the first table, matching the notes id from the second table to a notes id in the first table, and then counting some metrics from the third table to return as fields in the resulting recordset, used for ORDER BY sorting. Here is the working subselect query: Code:

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table d=
oesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo=
cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B=
Y l.location ASC

so when shotlistID is selected all the keys from the locations_join joined =
to the shotlistID would be remove from the locations list please help, i'm =
trying to do this in one query saving from getting all the keys into an arr=
ay then checking if the values arent in the array when generating the list.

Subselect/left Join
I have a table like this

| ID | THING | NUMBER |
---------------------------------------------------------------
| 1 | white | 1 |
| 2 | white | 2 |
| 3 | green | 1 |
| 4 | green | 3 |
| 5 | brown | 1 |
| 6 | brown | 4 |

and I want to get just white back if I know two numbers are 1 and 2 or green back if I know the nubmers are 1 and 3.

Its mysql 4.1 so I am allowed subselects or left joins. I am drawing a blank!?

Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table:

CREATE TABLE b5_assignment_lookup (
as_id int(11) NOT NULL auto_increment,
as_blog int(11) NOT NULL default Ɔ',
as_blogger int(11) NOT NULL default Ɔ',
as_milestone enum('start','finish') NOT NULL default 'start',
as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`as_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ;
By running this query, I get the following resultset:

mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = ྕ' AND as_timestamp <= &#55614;&#57158;-09-30'
+--------+----------+-------------+---------------+---------------------+
| as_id | as_blog | as_blogger | as_milestone | as_timestamp |
+--------+----------+-------------+---------------+---------------------+
| 87 | 89 | 41 | start | 2006-05-01 00:00:00 |
|208 | 89 | 41 | finish| 2006-09-02 11:55:27 |
|209 | 89 | 103 | start | 2006-09-02 11:55:27 |
+--------+----------+-------------+---------------+---------------------+
3 rows in set (0.00 sec)
What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41.

What I really need to grok is all bloggers who blogged all or a part of a range of dates.

For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02.

I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25.

The flags are the start/finish, obviously.

Transform SubSelect In OUTER JOIN
maybe I'm simply to dump but I could not transform this SQL-Statment
which uses a Sub-select and create on that uses an OUTER JOIN ....

Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.

I used to set it through MS Access 2002 and use this kind of query:

SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*) FROM tblStudentGrades
WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;

I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though.

Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?

Or maybe I should have approach it differently?

PK Field Not Null Is Being Null
I m newbie. I made a MySQL database table and in the primary key field I set the data type VAR CHAR.I also set it not null .BUT still I can insert blank values in this PRIMARY KEY field. If I just blank my primary key field by this query- Insert Tablename Values ('','','','','',''); (if my table has 6 fields).HOW IS IT POSSIBLE. How can PK field allow blank value when I set not null. HOW TO SOLVE IT.

2. I use a MySQL GUI Tools from http://dev.mysql.com/downloads/. In Table viewer of Query browser, each time I insert the first field, it sets the datatype to INTEGER,NOT NULL, AUTO INCREMENT. But I change it to VARCHAR as needed. Do I have to/should use Integer type in PK field and can Varchar be incremented.

3.Can I set user privilege to a particular row (on a particular PK); HOW?

4. Is it correct/incorrect that the name of the foreign key has to be the same as the name of the corresponding PK. I saw that in MySQL I can easily change the FK name to anything else and then just make a reference of FK to the PK.

5. How can I make a one-to-one relationship in MySQL;

VARCHAR Null/Not Null
I'm having a problem with a table that is already created - I want insert attempts to FAIL when the column for 'firstname' is empty (a VARCHAR field). Currently, the column is set to Null=YES and a Default of 'NULL'. This allows records to be inserted with no 'firstname' value (NULL appears in the column). If I change it in phpmyadmin 'structure' to Null=NO and Default of [empty field] then it allows records to be inserted with no 'firstname', and 'firstname' shows empty in the column

Using NULL
I was wondering is it good practice to use NULL for unknown values?

In the PHP forum sticky it says:

2) Declare all of your columns NOT NULL unless you need to store NULL values (NULL is not the same as 0 or the empty string). If you need to store NULL, you'll know. Again, NOT NULL saves space and speeds things up.

In the fields I am thinking of storing NULL, I could instead use empty string in the CHAR fields, and 0 in the numeric fields. However using NULL would make life a bit easier, and is probably more readable.

Should I use NULL? Or, is it better practice to avoid it where possible and use 0/""? Or, am I misunderstanding use of NULL altogether?

NOT NULL Vs. NULL
I have a table that's populated by a form. There are some fields that are optional. Should I send empty strings to a NOT NULL column or should I set the column as NULL and send nothing at all?

Am I right in interpreting the meaning of NOT NULL by saying: NOT NULL means an INSERT will fail if the corresponding field is left out of the query?

Null Value
I have a query which returns exam results as a string say a value of 55, which i convert to an int, but in some case's a student wont have a result and so a null value is returned. In the below code out.println(CA419_Overall); have a value of 50, but out.println(CA421_OverallX); has a value of null, CA419_Overall and CA419_Overall are both ints. When I try to view the page I get java.lang.NullPointerException.

while(rs.next())
{
String CA419_OverallX = rs.getString("CA419_Overall");
if (CA419_OverallX.equals("null"))
{
CA419_Overall = 0;
}
else
CA419_Overall = Integer.parseInt(CA419_OverallX);
out.println(CA419_Overall);

String CA421_OverallX = rs.getString("CA421_Overall");
out.println(CA421_OverallX);
if (CA421_OverallX.equals("null"))
{
CA421_Overall = 0;
}
else
CA421_Overall = Integer.parseInt(CA421_OverallX);

NULL And NOT NULL
I understand the meaning of NULL, but I don't understand its usage. For example, a field declared to be NOT NULL, but has a NULL value. Or when a field is declared to be NULL, and yet values can still be placed into it. I don't understand the logic behind this.

Null Value
I added a field to my company table (PBV_rstCompany.Fields("Installer")) the
default value of the field is Null. I place this If statement and it doesn't
work

If PBV_rstCompany.Fields("Installer") <> "Y" Then
txtInstaller.Visible = False
lblInstallerLBL.Visible = False
cmdNextInstaller.Visible = False
cmdPrevInstaller.Visible = False
End If

It assumes the statement is false when in fact it's true... Null in not
equal "Y"!

LIKE And IS NOT NULL
Are the LIKE and IS NOT NULL options available in MySQL - these 2 options
are availble in Access and MS SQL.

LIKE is for searching using wildcards - e.g WHERE Name Like 'AD%'.

Null Or Not Null
I am setting up my db, and am reading a great book on all of this, but am confused on exactly when to use null and not null.

NULL
I have table with columns which has NOT NULL definition. What can I do to be ensure that update or insert will not modify table with empty values?

MY EXAMPLE:

if($zm=='')
$zm=null ;

or what can I do?

How can I use NOT NULL table column definition? or what NOT NULL definition is for?

IS NOT NULL
I tried below mentioned sql statement

mysql> SHOW COLUMNS FROM Country WHERE `Default` IS NOT NULL;

and suprinsgly it even list down those record as well where coloumn 'default' is NULL

Note : In the preceding statement, the column name (Default) must be given as a quoted identifier because it is a reserved word.

Any specific reason why it produced record having value as NULL, i tried somewhat similar to my own table , and it works fine for my other table.

Trying To Put NULL
When I pick the option "allowed null" while creating a table field, and If that field does not have a value i see NULL written in that field when i browse the table using phpadmin,

But when I update the table doing followig

update accessories set field_A = null
I don't see "NULL" written in the field, does that mean that above SQL did not put null?

What Should Be NULL Is Zero
I am collecting user feedback via an HTML form and sending it to a DB via a PHP form handler. My problem is that values that should be NULL are showing up in the DB as zero.

When a user skips a question, I want the value for that field to be NULL. In the form, I set the variables used to capture the user's choices to default to NULL (not enclosed in quotes). All the PHP handler does is gather the submitted values and send them off to the database. When I echo the variables from the form handler, they are NULL. In the database, the fields are defined as INT(1) with a NULL default value. But when the values (which should be NULL) are submitted to the DB via the form handler, the resulting fields are zero.

(I have also tried setting the form's default values to "" and '' with the same result.)

What can I do to make these values NULL?

NULL
I have table with columns which has NOT NULL definition.
What can I do to be ensure that update or insert will not modify
table with empty values?
MY EXAMPLE:

if($zm=='')
$zm=null ;

or what can I do?
How can I use NOT NULL table column definition?
or what NOT NULL definition is for?

NULL/NOT NULL
I'm drawing a blank when it comes to using NULL/NOT NULL in my tables... can someone refresh my memory about this option in laemen terms. Also how or why does the field with this option have "NULL" entered in the field as the default value?

Getting The Null
I have two tables to compare
Code:
Schedule of Subject(table 1) previous subject taken(table 2)
Semister ID Subj ID | Semister ID Subj ID
1 1 | 1 1
1 2 | 1 2
1 3 | 1 3
1 4 | null null
1 5 | null null

My PK for this should be Semister ID.

My table 2 has only 3 records, so obviously if I compare
table2 from table1 (which has 6 records), table 2 should show the null value because it has no record for the following semister and subjects.

NULL
I have a date field in my database which is set to accept NULLs. If I enter
a record from phpMyAdmin and leave the date field blank, the record is
entered properly. However, if I do the same thing from a form, I get an
error that the date cannot be blank. It seems that I need to enter a NULL
in the date field from the form rather that nothing.

NULL
This is really silly, I used to do it many times in the past but now I completely forgot the syntax, so here I go:
What I want to do is select from one specific column, if this column is empty (NULL or = "") get the value from a second column instead. I remember doing it with a single query, but now I can't seem to remember or find how to do that.

WHERE A OR B OR C IS NOT NULL
I am trying to sort my recordset so that records will only display if certain columns contain information. I am unsure of the syntax on this. My first two columns will always be populated with data, but I only want to display the record if any one of the rest of the columns contain data. I know that .Code:

SELECT *
WHERE a OR b OR c IS NOT NULL

is not quite right.

Using NULL
Is it possible to insert NULLs when making an INSERT statement where the column counts dont match to have the extra columns be inserted with NULLs automatically?
The following is my query

insert into team select playerID,teamID from batting where playerID like 'aaro%' group by teamID

aaron played for 3 teams, but the table team has space for 7 teams. I would like team4..team7 to get NULLs, if possible or another way to not get the column count doesn't match error.

Using NULL Vs 0
I have a DB i MySQL that uses many int(1) type fields. I store there some time value 0 or NULL.
My question is which solution is faster from DB point of view?
Should I store NULL for all those fields or is "0" better solution?
Which one takes less space and time to process?

NULL
I have a MySQL table that stores name and age of a person. The name field needs to have a value; the age field is optional.I'm quite confused now with the NULL and NOT NULL settings... It seems that neither one does what I want (rejecting an INSERT query where the name field is empty)...So what exactly do I have to do?

Column Specified As Not Null
I have a column in mysql that is specified as "Not Null" I have noticed that my form fields that map to these columns are able to populate data into the table regardless of the not null, like if I have an email field and I leave that blank on the form then submit well all other data of my form fields populate and I guess I am expecting the behaviour of the not null on the email column to deny data entry for that entire form submission thus record not entered.

SET NULL Doesn't Work
I have this table:
MySQL
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`login` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`name` VARCHAR(30) NOT NULL,
`email` VARCHAR(30) NOT NULL,
`icq` INT(9) UNSIGNED DEFAULT NULL,
`personal_stream_id` INT(11) NOT NULL,
`TIME` DATETIME NOT NULL,
`url_name` VARCHAR(30) DEFAULT NULL,
`avatar` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `login` (`login`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `icq` (`icq`),
CONSTRAINT `users_fk` FOREIGN KEY (`id`) REFERENCES `photos` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1;

The constraint is linking this table:

MySQL
CREATE TABLE `photos` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`user_id` INT(11) DEFAULT NULL,
`streams_id` INT(11) NOT NULL,
`description` TEXT,
`hardware_id` INT(11) DEFAULT NULL,
`ratings_id` INT(11) DEFAULT NULL,
`thread_id` INT(11) DEFAULT NULL,
`url_name` VARCHAR(20) DEFAULT NULL,
`views` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `ratings_id` (`ratings_id`),
KEY `user_id` (`user_id`),
KEY `streams_id` (`streams_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

It works, but as you can see, the constraint ondelete action is set to CASCADE, which is not good - it is bad to delete all photos when the user is deleted, I want the database to set user_id value to NULL using the SET NULL action - PHP frontend should display something like "User deleted" then. But when I try to alter the constraint to SET NULL, EMS SQL Manager I'm using shows a very strange error like "Can't create table '.dfk#sql-8e4_4.frm' (errno: 150)". Similiar error is shown when I try to link unsigned int with not unsigned int.

Use 0 Instead Of NULL In This Case?
I am designing a table that will be used for a translation lookup. I want the option to make global rules, and then come back later and make more specific rules if I need to. My current design calls for 4 columns:
id(int(10)) = surrogate key, auto_increment
designer_id(int(4)) = relates to another table on a foreign index to give me information about the designer -- this column is an "optional" column
code(varchar(25)) = code that will be translated
prompt(varchar(50)) = translation to be displayed

if the rule is "global", then there will be no designer. I want to create a unique index on columns designer_id,code so that there will never be any duplicate (contradictory rules), but this doesn't work if I leave designer_it default to NULL? I was able to create the same row twice (prompt was different) without breaking any rules... I think that if I made the default designer_id 0, then the index would work. Is this true? If so, is this the best way to prevent duplicate rules?
The designer_id will always be an int > 0.

Query NULL
SELECT sales_reps.sr_id,
sales_reps.order_id,
sales_reps.name,
sales_reps.job_number,
UNIX_TIMESTAMP( shop_orders.date ) AS date,
sales_reps.stage,
sales_reps.status,
pi.fname AS pfname,
pi.lname AS plname,
sales_reps.date_to_shop,
users.builder,
users.division,
shop_assignments.editor_id,
shopper.fname AS sfname,
shopper.lname AS slname,
ei.shop_result,
ei.good_tape
FROM sales_reps
LEFT JOIN shop_orders ON shop_orders.order_id = sales_reps.order_id
LEFT JOIN users ON users.user_id = shop_orders.builder_id
LEFT JOIN shop_assignments ON shop_assignments.sr_id = sales_reps.sr_id
LEFT JOIN users AS shopper ON shopper.user_id = shop_assignments.shopper_id
LEFT JOIN users AS pi ON pi.user_id = shop_assignments.pi_id
LEFT JOIN exit_interviews AS ei ON ei.shop_id = shop_assignments.shop_id
WHERE sales_reps.allow_shop = 'Y'
AND users.user_id = ཎ'
AND sales_reps.status = 'Rejected'
AND ei.shop_result NOT
IN (
'Trip x1', 'Trip x2', 'Non-Reg Trip x1', 'Non-Reg Trip x2', 'Office Closed'
)
ORDER BY users.builder, users.division, shop_orders.date DESC , sales_reps.stage ASC
the ei.shop_result NOT IN is also including NULL in that list. I don't want this since there are some Rejected records that have a NULL value in that field.

Subselect With NULL

why don't i get some results for the second query?mysql> select * from a;+---+| b |+---+| a || b |+---+mysql> select * from a where b not in (select NULL from dual);Empty set (0.00 sec).

why don't i get some results for the second query?

mysql> select * from a;
+---+
| b |
+---+
| a |
| b |
+---+

mysql> select * from a where b not in (select NULL from dual);
Empty set (0.00 sec).