MYSQL :: Impose A Unique Key With 11K+ Rows

Best Way To Do This... Unique Key But 3 Rows Need It?
I have a weekly schedule table, each week should have a unique number but each week requires 3 different records (one for each game). What would be the best to create a unique identifier that is the same for the 3 records each week?

5 Unique Rows
My SQL so far is:

PHP Code:
$sql =     "SELECT
        Manufacturer.ManID,
        Manufacturer.ManName,
        Manufacturer.Active AS manactive,
        Manufacturer.ManText,
        Models.ModelID,
        Models.ModelName,
        Models.ModelText,
        Models.Active AS modelactive
        FROM Manufacturer
        INNER JOIN Models ON (Manufacturer.ManID = Models.ManID)
        WHERE Models.ModelName UNIQUE
        AND Manufacturer.ManID = " .$manid. "
        AND Models.ModelID = " .$modelid. "
        AND (Models.Active = 1 AND Manufacturer.Active = 1)
        LIMIT 5
        ";

Selecting Unique Rows
I have a query that checks a list of users against a list of items and returns for example the following.

SELECT users.username, ratings.recording_uid
FROM users
INNER JOIN
ratings ON (users.uid = ratings.user_uid)
WHERE ((users.username = 'a') OR (users.username = 'b'))

which returns...

username recording_uid
a 1
b 1
a 2
b 2
b 3
a 4
b 4
b 5

How can I add to that query so that I know what user a has that user b doesn't and vice versa? eg. so that it returns

username recording_uid
a 3
b 5

How Can Define A Combination Of Rows As Unique?
How can define a combination of rows as unique?

I'm using mysql and need to prevent duplicate row inserting. I don't want to define one column as unique but a combination of 3 columns.

Turning Off Allowing Multiple Non-unique Rows
I feel lazy and I am busy so i don't want to fix my perl code to only alllow insertion of unique rows. I know mysql is set to allow multiple rows of the same thing but I would like to turn that feature off. Is that possible?

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

Unique ID
Say I wanted a membership site. A user would register and get a auto created ID.. Starting from 1..

I want people to buy a User ID if they want..
Say I get the ID 567 but want to purchase the ID 5555 because I like the number. But its not created yet since there arent that many users.

Is this possible with an auto icrement ID setting for the users without messing up the flow of the auto increment field if so how would I go about it.

Unique
How can i define in mysql table that a field is unique??

Set A Unique Row
I have a website which is database driven (it's a world cup prediction league).

The problem i have is that if a user changes their predictions, the script reposts every prediction again in the database.

The database table name is:
plpredictiondata

This consists of 6 rows. The ones i am interested in are called:
userid
matchid
predtime

What i need to do, is every time that predictions are posted, the row matchid should only contain a unique number per userid. Does that make sense??

As an example, say i have 2 users (userid=1, userid=2), and 2 matches (matchid=1, matchid=2). Userid=1 posts scores for both matches, then subsequently changes them. At the moment, it will show userid=1 as having matchid=1 & 2 in the table twice. I want it to only allow the most recent prediction. I have tried using REPLACE INTO, but thats not working.

UNIQUE
I am trying to create a statement where it will return the UNIQUE values
from a table, I think its the UNIQUE statment.

So a table may have 60 entries, 20 of them are X, 20 Y and 20 Z
I want it to return only X,Y,Z e.g. 3 returned entries.

Unique Value
I have a table like this:

CREATE TABLE IF NOT EXISTS `test` (
`id` INT(11) unsigned NOT NULL auto_increment,
`cid` VARCHAR(10) NOT NULL,
`item` VARCHAR(10) NOT NULL,
`val` VARCHAR(10) NOT NULL,
`dt` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

How do I find the unique highest value in `val` ?

Unique Row Problem
I have this statement that shows who made affiliate commisions during the month I choose.

$sql = "SELECT *, COUNT(id) AS referrals FROM user WHERE id IN (";
$sql .= " SELECT DISTINCT u.referral FROM user u, servtrans t WHERE u.id=t.userID AND t.createdOn BETWEEN '$startDate' AND '$endDate'";
$sql .= ") GROUP BY id";
I run it and it runs correctly except for the fact that if an affiliate makes a couple sales from the same person, its not showing. Its only showing one sale.
Is this because this statment is only pulling out unique id's? If so, how can i fix it?

Unique Results
I am querying my mysql database from php like so:

$query = "
SELECT $wpdb->posts.*
FROM $wpdb->posts
LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id)
WHERE $wpdb->post2cat.category_id NOT IN (2, 6)
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->posts.post_date DESC";

Its joining a second table (post2cat) as the main table (posts) does not have a value (category_id) which I need to check is not 2 or 6. As a result if an item in posts has two category ids (and so is listed twice) the item goes into my mysql result twice which I don't need. Is there a way I can return only unique results (i.e. not twice)??

This is the Wordpress mysql structure if anyone is familiar with it.

Unique Key Pair
I'm very new to MySQL, and therefore don't even know what to search for when it comes to this issue (I've tried about a dozen terms to no avail).

My table is set up like this:

CREATE TABLE `tableName` (
`sessionID` int(10) unsigned NOT NULL default 0,
`name` varchar(40) default NULL,
`value` longtext NOT NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1
`sessionID` actually corresponds to another value in a different table. From what I understand, I've set up a "one-to-many relationship" (`sessionID` must be unique in the other table, but not this one). However, I would like to add some kind of a constraint that requires that `name` is unique within the context of it's `sessionID` value.

For example, this would be allowed:

`sessionID``name``value`
1'thing1''stuff'
1'thing2''more stuff'
2'thingy''stuff'
2'thing2''blahblah'
But this would not, because there would be two identical `name` values ('thing2') for one `sessionID` value (2):

`sessionID``name``value`
1'thing1''stuff'
1'thing2''more stuff'
2'thingy''stuff'
2'thing2''blahblah'
2'thing2''error'
Is this possible, and if so, how can I state this in my CREATE TABLE query? Also, does this have a specific name (so I can look up more information if need be)?

Primary Key + Unique Key?
my question is: do I have to declare primary key and unique key in this table?

CREATE TABLE `sessions` (
`id` varchar(32) NOT NULL default '',
`access` int(10) unsigned default NULL,
`data` text,
`usr` varchar(12) default NULL,
UNIQUE KEY `id` (`id`)

Unique Query Help
I have two tables

Table 1 has 3 unique URL (for e.g. hotmail.com) and Table 2 has 4 records of (hotmail.com)...The only way to display the combination for a particular URL is to use group by.

Table 1

id url
1 hotmail.com
2 sitepoint.com
3 youtube.com

Table 2
id userid (user who added) url notes
1 user1 hotmail.com good site
2. user2 hotmail.com email site
3. user3 hotmail.com great site
4. user4 hotmail.com cool site

Now I want to JOIN table 1 and table 2 and display unique records for hotmail.com...Only way i can do is by using group by...I don't want to use that. And it doesn't really matter if it display record by any user, but as long as it is only one record for hotmail.com in Table 2

Can anyone please help me

If I use inner join from Table 1 to table 2 ON URL, it still shows 4 records of that url, though i only want to show the first one if repeat record occur.

A 'UNIQUE' Question
I have to check whether my db contains same value.
so i have altered my table as

ALTER TABLE details ADD UNIQUE (name);

so if i am giving same user name mysql will throw a false error

is it a good method ? or

should i use select query to check whether name exist or not

Unique Id Between 2 Tables
Is there a way to give a feild, lets call it "id" a unique value but without using autoincrement?

Why? I need to have 2 seperate tables ("bands" and "soloists") which both have the field "id" but there cant be a collision in the id's.

Index & Unique Key
I created a table with two columns 'user_id' and 'name', and defined the combination of the two columns as UNIQUE and defined an INDEX on each column.
Why is the following error generated: UNIQUE and INDEX keys should not both be set for column `user_id`?

Unique Identifier
I wanted to create a unique identifier field in a mysql database, this is a fairly common practice in MS SQL, is there an equivalent in mysql?

How To Make A Unique Key 1 Again?
I want to make a unique number 1 again.

Creating A Unique Value
I'm trying to create a stored procedure which creates a unique value each time.

This one creates the a value based on what length I want to give it and prints it.
CREATE PROCEDURE `test3`(IN idlength int)
BEGIN
declare uid char(30);
declare a char(1);
set uid='';
while length(uid) < idlength do
set a= substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',floor(rand()*64), 1);
set uid = concat(uid, a);
end while;
insert into t2 values (uid);
select UID;
END $$

What I need to do is to compare this value with the already stored values in the table to check if the new one is unique. Any suggestions on how to do that?

Getting All Unique Entries
I need a query to get all unique entries in a field.

If you have:
3
3
5
4
5
3
5
5

it would return 3, 4, and 5.
Can this be done in a MySQL query.

Unique In Mysql 3.23.52
i have to use mysql 3.23.52. I have problems to create UNIQUE over two columns. Is it possible to create uniqueness over two columns? It is also not possible to create an Primary Key over two columns.

Counting Unique
I have the following code...

$query = "SELECT DISTINCT newsid FROM comments";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){

which works fine. My questions is there any way to determine which newsid shows the most amount of times. I'm trying to make a "top5" sort of things and it would make it much easier.

UNIQUE Constraint
If I have a table with a UNIQUE key 'v', trying to store upper and lower case letters should work, but won't. For example, if I insert a value of 'a' for the column v, and then try to insert a value 'A', I get an error message. Likewise, if I enter this statement:

select * from tname where v='A';

the display will include rows where v='a'.

Surely there must be a way to force MySQL to recognize the difference between upper and lower case values, and to specify that they be stored that way?

Get The Next, Unique Primary ID
I have a table with an ID (Primary, Auto-Increment), and some other fields. The problem is, whenever I insert a new row and leave the ID field unspecified, it inserts the new row with an incremented ID, EVEN if my records look like this:

ID:
1
2
7
8
9
10

Notice how above, the records between 2 & 7 appear to have been deleted? I want mySQL to recognize this when inserting new records, and to use a number that would be the next available, unique number.... So instead of inserting the new row with an ID of 11, it would insert the new row with an ID of 3 - filling up the unused number.

Primary Key & Unique Key
When running PhpMyAdmin versin 2.8.x, I notice a warning message on tables that have PRIMARY & UNIQUE Key on the same column (field).

It says that Primary & Unique Key SHOULD not be set both on the same column.

Is there any body can advise me how to appropriately handle Primary & unique key on the same field ?

Unique Row Not Column?
I know Mysql is able to prevent duplicates being entered in the one column, via the 'unique' feature.

Is it possible to stop rows of the same values being created.

For example
joining two or more columns in a row, and merging the values into a singular value, upon which the 'unique' feature may work. Or is their more of a simple way of doing this.???

Invoice_ID Products UNIQUE
56 ---------- X --------- 56X
56 ---------- Y --------- 56Y
56 ---------- X --------- 56X
57 ---------- Y --------- 57Y

Unique Column
Each time I try to insert multiple rows of data, MYSQL will stop when it finds the first duplicate. I'd like to know how I can have it continue on and either suppress the error message completely, or put the duplicated entries into an array and output them afterwards.

Unique Constraint
tell me is it possible to set a unique key constraint over a Blob field?
If it is possible could you please tell me how can i achieve this.

My Scenario is i got a sub_photo table(sub_phid intger primary key,sub_id,sub_photo)
i want (sub_id,sub_photo) as unique. Please tell me how can i go about with this.

Unique Key Also Act As Index
someone said that a unique index also acts as a normal index for the respective fields. Is that true?

I :
- need unique constraint and indexing on a field
- don't need redundant indexing on the field

So I don't need to add a normal index on the which has unique index already applied, is this correct?

Unique ID Field
Hi, i need to create a table which will create a unique field called tracking_number. But i want this tracking number to always be 10 characters long and start at say 1230000000 instead of 1 because it would look a bit stupid with the first order on the system by the customer being 1!

Would really help me if any one knew the code to do this.

This is what i have but tracking_number is stating at 1.

<?php

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE jobs3(
tracking_number INT ( 10 ) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(tracking_number),
delivery_address VARCHAR(100),
date_dispatched VARCHAR(100),
status VARCHAR(100),
signed_by VARCHAR(100)
)")
or die(mysql_error());

echo "Table Created!";
?>

Two UNIQUE Fields Together
I'm trying to add records to a table via command line. I would like to setup the table so that it rejects a record insert if two of the fields are not UNIQUE together.

Here is my table:

logNum mediumint(8)
logDateTime datetime
docNum tinyint(3)
docName varchar(200)
owner varchar(15)
sourceIP varchar(15)
printerName varchar(25)
printerPort varchar(15)
sizeInBytes varchar(15)
pagesPrinted smallint(10)

So, if two records have the same logNum AND the same LogDateTime. I want that record rejected and the insert to continue with the rest of the records.

Inserting Unique Value
I need to insert a unique value into a (not auto-increment) column.
I try

insert into idtest (val) values ((select max(val) from idtest)+1);

but I get

ERROR 1093 (HY000): You can't specify target table 'idtest' for update
in FROM clause

- what is the correct way to do this?

Primary Key--or At Least Unique
I have an existing table and need to alter it to make it so that an author can only vote for an item one time. Therefore, it is a composite key of:

author1 item1

This author cannot be allowed to vote again:

author1 item1
author1 item1

I tried altering by just adding:
alter table rating modify primary key (ratingquest, ratingauth)

but that didn't work. I don't think modify is correct because I'm not modifying an attribute, but rather, adding a primary key.

Getting Count Of Unique IPs
I have this query:

SELECT CONCAT( YEAR( date ) , '-' , MONTH( date ) , '-' , DAYOFMONTH( date ) ) AS DateOfVisit , COUNT( ip ) as count FROM jenkins WHERE ip != '160.79.119.194' GROUP BY DateOfVisit ORDER BY date DESC, count DESC

This is great, but I only want to know how many hits from unique IPs ... not all IPs.

If I group by IP then I get the date repeated with a count for each IP on that day.

Unique Keys
Is it possible to specify a unique key with an exception? For example, all my users have there own user id and i log them in a session table however i also have guests which register as 0 for the user id in the same table. So there may be lots of users with a user id as 0 but i only want 1 of each of everythihng else.

Unique Entries
I'm creating this HITS script, which logs all unique hits, storing ip, query string, time, browser information.
However, i've been looking for a way to show al unique visits (ip based) is there a simple query that selects only the unique entries of a column?

UNIQUE Vs INDEX
i have a table with two fields. i want both to be indexed since i will be searching with both of them, and at the same time i want to ensure uniqueness on these two fields together.i cannot create an index on both fields individually and create a unique key on both fields. does creating a unique key on both fields accomplish the same as indexing>?

Return Unique
I have a table with unique words.
I want to insert new words, but if the word I'm inserting already exists I want the ID of that record returned so I can use it in the next query...

Insert Unique Only
I want a table with the following:

1) ip's
2) an int

however, when inserting, I only want new data to be inserted if that EXACT column doesn't already exist (ie, same ip with the same int).
what's the best way to set this up as far as the database structure?
what's the syntax I should use when inserting?

Unique Query
I don't know if this can be done
but here goes
I have a mysql table with the fields
id, user_name, L1 and L2
the first query is

Code:

select L1 from tbl_name where id=some_value

now I want it to check if L1 is NULL if it is not then check if L2 is null if it is not take the value of L1 as some value and redo the query and if both L1 and L2 are not NULL in this query go back to the original L2 and take that value as some_value and do the query and continue like this until a NULL value is found then do an update
I am not very good at explaining so if this is not clear please ask

Best Solution For Unique ID
For any db where you will be entering tons of data and want to create a unique ID for each row, whats the best way of creating a full proof unique ID. Ive used auto incr and md5 hash, I prefer using the auto incr since there are less characters to deal with. Id like to see what other people think