UNIQUE As Multiple Columns
its possible to define multiple columns as being UNIQUE - not the value of either column, but the two together. For example, you're storing people's names and addresses in a table. Two or more people could have the same lastname, and more than one person will surely have the same firstname. However, no two people can share the same lastname AND firstname. Is there a way to set this constraint?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Multiple Non-Unique Columns
I'm trying to store the call detail reports from a phone system to a database. I've successfully been able to read the CSV files it gives me into an InnoDB table using PHP. I just realized that the system does not give a single unique key in the raw data, but that two columns together will identify a unique call entry. The columns if interest are the timestamp (two people could conceivably make a call at the same time) and a call identifier (which seems to be unique over a month or so, then the system starts recycling the call identifier). With these two columns you can identify a unique call over ANY given time. I'm not sure how to use that fact to assure I'm not inserting the same data if I were to read the same CSV file twice.
Unique For All Columns
I have seen examples of using unique to prevent duplicates of a column. However can this be done to make sure the entire record has no duplicates to other records? Each of my records has 20 columns. There are records that have 19 identical columns and 1 that is slightly different, which is fine. However, if all 20 columns match, that is considered a duplicate.
Unique Columns And Indexing
I have a table where all of the columns are set to 1 unique key: CREATE TABLE `feedlist_details` ( `productlist_id` int(8) NOT NULL default Ɔ', `product_id` varchar(50) NOT NULL default '', `category_id` varchar(255) NOT NULL default '', `merchant_id` int(8) NOT NULL default Ɔ', UNIQUE KEY `product_list` (`productlist_id`,`product_id`,`category_id`,`merchant_id`) ) I will be doing joins on this table on 1 or many of the columns. Do I still need to index these columns? Or are the already indexed because they are unique?
How To Apply UNIQUE Across Several Columns?
I have a database, with columns as follows (example): index1 int(16) unsigned not null index2 int(16) unsigned not null data text what I would like to do is to set this table up such that both index1 and index2 are checked for uniqueness. For example: index1 index2 data 15 20 "hello world" 15 22 "hello mars" 22 20 "hello moon" 20 15 "hello jupiter" 15 20 "hello venus" The last row would be illegal because there is already a row in which index1 = 15 and index2 = 20. All other rows are legal (including the hello jupiter row). So, how could I set up the database to achieve this? (rather than doing it through queries to check existing values of index1 and index2 each time I want to add a new row).
Mutiple Unique Columns
Is it possible to create a mySQL table with multiple unique columns? I have some data being parsed into the mySQL table that frequently has duplicate lines of data. I would like to be able to exclude these duplicate lines of data without having to exlude all other duplicate data in other columns that have some same data but is not a complete line duplicate. right now I can only set one column in the mySQL table to "unique" - if I try to set another column as unique, I get errors on the table.
Select From Table Using 2 Columns As Unique
How do I select from a single table using 2 columns (surname,postcode)as the unique criteria? e.g. "Surname","Forename","Salutation","Postcode" "Bear","Daddy","Mr","CT10 7AF" "Bear","Mummy","Mrs","CT10 7AF" "Bear","Baby","Miss","CT10 7AF" "Piggy","Daddy","Mr","CT10 7AF" "Piggy","Mummy","Mrs","CT10 7AF" "Piggy","Baby","Miss","CT10 7AF" "Piggy","Cousin","Ms","CT10 7AF" would return just the following 2 rows:- "Bear","Daddy","Mr","CT10 7AF" "Piggy","Daddy","Mr","CT10 7AF" I don't mind whether it's the first unique row or the last unque row. All I am interested in removing the duplicates.
Select From Table Using 2 Columns As Unique
How do I select from a single table using 2 columns (surname,postcode) as the unique criteria? e.g. "Surname","Forename","Salutation","Postcode" "Bear","Daddy","Mr","CT10 7AF" "Bear","Mummy","Mrs","CT10 7AF" "Bear","Baby","Miss","CT10 7AF" "Piggy","Daddy","Mr","CT10 7AF" "Piggy","Mummy","Mrs","CT10 7AF" "Piggy","Baby","Miss","CT10 7AF" "Piggy","Cousin","Ms","CT10 7AF" would return just the following 2 rows:- "Bear","Daddy","Mr","CT10 7AF" "Piggy","Daddy","Mr","CT10 7AF" I don't mind whether it's the first unique row or the last unque row.
Absolute Unique Index Across Two Columns
I am coming across a problem setting up my database. I want to have a table called double matches which holds information about matches between two people. It has w two columns: person1 and person2. I want to ensure that each row is unique across both columns. However, I need this work no matter which person is entered in which column. For example, person1 person2 "Joe" "Jane" "Jane" "Joe" should be invalid as that is the same "double match." So I would want a query that inserts a duplicate to not insert it. I've played around with having a unique index across two columns, but it doesn't recognize the uniqueness both ways. I.e. it will allow input of rows which have person1 and person2 switched from an existing row. I've had some trouble identifying the right terms to search, thought I'd ask her. The next step would then be how to search that table for all rows containing one name, ("Joe") and returning a list of his partners ("Jane").
Unique Data In Keyed Columns
Just getting started with MySQL after 20 faithful years with a Dbase clone called DBMAN. I'm starting to design my database "Tables" and notice that data stored in a keyed column must alway be unique. Is this true. If so what is the best way to get around doing a search for Smith in large data file containing over a million rows and there are 3000 Smiths strewed through out the table? Or is that fast it can scan a million records in a few seconds.
Unique Constraint With TEXT Columns?
I want to create a table that has a unique constraint on two columns (i.e. the combination of the values in the two columns cannot be repeated). When I create the table below I get the message "ERROR 1170: BLOB column 'f3' used in key specification without a key length". This would seem very limiting - I can only be unique on the first 255 characters of a TEXT field !? Is there a way round this? It seems as though I can't have both a FULLTEXT index being UNIQUE too; it's one or the other. My table type is MyISAM and I'm on version 4.0.17. CREATE TABLE testUC2 ( f1 VARCHAR(10), f2 VARCHAR(10), f3 TEXT, CONSTRAINT UNIQUE (f2, f3) );
Making Values Of Two Columns Unique
1. Two columns, one is CHAR type, second is INTEGER. None of them is Unique, but combination of them must be. How to make this in MySQL 4.1.10? 2. What kind of Index (INDEX, UNIQUE INDEX,...) I should make on this columns to get better performance?
Select From Table Using 2 Columns As Unique
How do I select from a single table using 2 columns (surname,postcode) as the unique criteria? e.g. "Surname","Forename","Salutation","Postcode" "Bear","Daddy","Mr","CT10 7AF" "Bear","Mummy","Mrs","CT10 7AF" "Bear","Baby","Miss","CT10 7AF" "Piggy","Daddy","Mr","CT10 7AF" "Piggy","Mummy","Mrs","CT10 7AF" "Piggy","Baby","Miss","CT10 7AF" "Piggy","Cousin","Ms","CT10 7AF" would return just the following 2 rows:- "Bear","Daddy","Mr","CT10 7AF" "Piggy","Daddy","Mr","CT10 7AF" I don't mind whether it's the first unique row or the last unque row. All I am interested in removing the duplicates.
Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful! Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better. I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns. For example, I'd want this: 1-1,1-2,1-3,1-4,1-5 2-1,2-2,2-3,2-4,2-5 To become: 1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5 The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like: SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345 This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).
Multiple Unique Indices
Consider this table: ID | Number | Name 1 | 10 | X 2 | 11 | Y 3 | 12 | ABC 4 | 13 | X ID is the primary key, and there is a unique index on Number. My program loads the table for editing (ID is read-only, Number and Name are modifiable), then checks through each line if any was updated by the user and writes the record back to the database using UPDATE statements (the ID must stay the same). Consider that the numbers of Y and ABC are exchanged, such that: ID | Number | Name 1 | 10 | X 2 | 12 | Y 3 | 11 | ABC 4 | 13 | X When my program executes the query "UPDATE Table SET Number = 12 WHERE ID = 2", the unique index throws an error since ABC (ID=3) still has Number=12. How could I avoid that and update both rows safely (without first deleting all records)?
Innodb Multiple Tablespace & Unique Name
I'm using Mysql Max-4.1.4-gamma for Linux. I have a question related to a problem with my database server. If on my db server I have 2 different database, say DB1 & DB2, I create two INNODB tables with the same name (foo), one for each database, how they are stored in the innodb data dictionary ? DB1.foo & DB2.foo or simply foo ?
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?
MIN() Over Multiple Columns
I need to find out how to find the minimum value usig a query over three columns: SterlingTradeGross BlowOutNett SterlingTradeQGross These are the three columns, I need a query which calculates the minimum value from all of these, how do I do it?
WHERE With Multiple Columns
I have three tables: person with per_id, event with eve_id and than third table per_eve with the two columns per_id and eve_id. I now need to check, if a row exists in the per_eve-table for a certain event and person. If there is an occurence, than I don't want that row in my overallresult. Which person and event to take I get from other conditions. To check for non-existence I thought something like "WHERE (person.per_id, event.eve_id) != (per_eve.per_id, per_eve.eve_id)" could give me all rows not to drop, as I need the combination of both values. But it does not work, I get an errormessage after the first comma.
Where In Multiple Columns
I am attempting to search multiple columns for the same foriegn key. I have the following columns in my catalog_images table: nID, strFileName, nCategoryA, nCategoryB, nCategoryC, nCategoryD, nCategoryE and I want to select strFileName from catalog_images where ANY of the following matches occur: nCategoryA="1", nCategoryB="1", nCategoryC="1", nCategoryD="1", nCategoryE="1" I can't figure out my query string. Any suggestions?
STD Of Multiple Columns
I want to calculate the standard deviation of data that are in multiple columns. I know how to calculate the STD of 1 column (e.g. X1 of table_X) using: SELECT STD(X1) FROM table_X; but I want to calculate now the STD of the union of data of columns (e.g. X1, X2, ..., X100 of table_X). Does anyone has any suggestion on how to do that? I hoped something as SELECT STD(X1,X2,...,X100) FROM table_X existed, but apparently it does not.
Multiple Columns
I did a search and could not find what I was looking for. Hopefully someone can answer this. I am trying to SELECT multiple columns as one column for display. Something like this (which obviously does not work): Quote: SELECT column1 as combinedlist, column2 as combinedlist FROM `myTable` WHERE column1='data1' OR column2='data1' ORDER BY combinedlist I have also tried: Quote: SELECT (column1, column2) as combinedlist FROM `myTable` WHERE column1='data1' OR column2='data1' ORDER BY combinedlist
Multiple Columns
How do I query a table in order to combine ST_ADDRESS, CITY, STATE, ZIP fields into one ADDRESS field?
Enforcing Unique Field Values Accross Multiple Tables
I have 2 tables (t1,t2) with unique keys defined in each. In addition to enforcing unique key values in each table, I would also like to enforce unique key values accross both tables (ex. If key value 'XXX' appears in t1 I don't want to allow entry of key value 'XXX' in t2 and vice versa).
Selecting Multiple Columnn Values Into 1 Distinct/unique List
I have a table with several fields for email (primary email, alternative email, contact email) Some times, some of these fields will be blank and sometimes the same address will be enter for both primary and contact. I use this table and these fields to do a mailout but I don't want to mail people twice because there address occurs in more than 1 column. I need to end up with a list containing all email values from those 3 columns that is unique (no duplicates). I can do this by checking each one and putting it into a php array but thought there may be a nicer way to do it in the SQL?
Index Multiple Columns
I have a table that looks like this: PlayerItems -------------------------------- | PlayerID | ItemID | ItemCount | -------------------------------- | 1 | 1 | 5 | | 1 | 3 | 1 | | 2 | 1 | 2 | -------------------------------- What index types should I use for PlayerID and ItemID so that it will throw a duplicate key error if a row is inserted that matches the playerID and the itemID. Basically im looking to run a query like this: Insert into playeritems values (1, 1, 1) on duplicate key update itemcount=itemcount+1 How should I set the indexes so it will throw a key error only whe nthe player id and item id matches.
ORDER BY Multiple Columns
I am trying to order by multiple columns, here is my example and what I want to output Code PHP ORDER BY lastName ASC, State ASC Output Quote: James Anna, Alabama Ben Bloggs, Alabama Chris Cats, Alabama Mark Aims, Delaware Hopefully you can see what I am trying to achieve, but I don't get there all it does is sort the result by lastName and ignores the states...
ORDER BY Multiple Columns
I am trying to order by multiple columns, here is my example and what I want to output Code ORDER BY lastName ASC, State ASC Output James Anna, Alabama Ben Bloggs, Alabama Chris Cats, Alabama Mark Aims, Delaware Hopefully you can see what I am trying to achieve, but I don't get there all it does is sort the result by lastName and ignores the states.
Max() With Multiple Columns W/o Groupby
I have a table with two columns ts(timestamp) and tempc(int). From this I need to pull the both of these values but where tempc is max value. Individually I can this: select max(tempc) from templog; and it works fine. However when I try this it fails: select ts, tempc from templog where tempc = (select max(tempc) from templog); I'm sure this is something inanely obvious, but I cannot seem to figure it out... Any ideas?
Search Multiple Columns
I would like to search for a keyword '%schultze%', and I would like the search to include more than one column. I was experimenting with this: SELECT * FROM `listItem` WHERE `owner` = 128 AND MATCH (`title`) AGAINST ('%schultze%'); But I would get funny errors complaining about not being able to find a FULLTEXT index. I imagine this is something that has to be built before the search happens. As the database is changing all the time, I would want this to be built once a day. Perhaps I could stick a batch file in the xinetd that would take care of that. Ideally I would like to search over 10 columns, (with the constraint that the owner equals 128) that are varchar and text, all utf8_general_ci. I suppose this would look something like: SELECT * FROM `listItem` WHERE `owner` = 128 AND MATCH (`referenceNumber`, `title`, `menus`, `added`, `format`, `ratio`, `source`, `description`, `genre`, `rating`) AGAINST ('%schultze%'); Or perhaps I'm barking up the wrong tree here and I could do a search that was like this: SELECT * FROM `listItem` WHERE `owner` = 128 AND (`referenceNumber`, `title`, `menus`, `added`, `format`, `ratio`, `source`, `description`, `genre`, `rating`) LIKE ('%schultze%'); But where mysql would know each column was OR, where '%schultze%' matches for `referenceNumber`OR `title` and so on,
Ranking For Multiple Columns
A query in my application return several columns with amounts, Now i need to add a ranking to all those columns seperatly. So the result should be something like this: ID Amount1 Ranking1 Amount2 Ranking2 ---- -------- --------- ------- -------- 135 123.34 4 23.00 3 184 160.23 1 60.89 2 845 140.22 3 100.20 1 987 155.00 2 1.20 4 The ID isnt important here. The only solution i can think of now is to create a (temp) table, populate it with the query, and then sort it for each ranking and fill the ranking one by one. Is there a way to get the ranking filled in the same pass as the amounts are calculated?
Group By Multiple Columns
I have a "users" table as follows: first_namelast_name JohnStuart JohnDell JohnStuart BillWhite If I want to know how many people of each first name there are, I do the following: select first_name, count(*) AS Number from users GROUP BY first_name; I am getting: John|3 Bill|1 But how can I get the same for both first and last name? I just don't want to count John Stuart twise. I tried: select first_name,last_name count(*) AS Number from users GROUP BY first_name,last_name; This gives me the same result as before, just adding column of first name (the same old last name grouping). I want to get: John | Stuart |2 John | Dell |1 Bill | White |1 Do I need to add multiple column index for multiple column Group By?
Select Sum(x) >0 With Multiple Columns
I am writing a script to show the average value of a question in a submitted survey form. I have 14 questions, which can have the value 1-10, if the user submitts "I don't know" it returns the value 0. When i calculate the average it includes the value 0 but still divides with all the submitted answers (see code). How can i make a select statement which only includes values > 0? Current select statement: $select = "SELECT SUM(S1), SUM(S2), SUM(S3), SUM(S4), SUM(S5), SUM(S6) , SUM(S7), SUM(S8), SUM(S9), SUM(S10), SUM(S11) , SUM(S12), SUM(S13), SUM(S14) FROM Evaluering"; I want it to do something like this : $select = "SELECT SUM(S1), SUM(S2), SUM(S3), SUM(S4), SUM(S5), SUM(S6) , SUM(S7), SUM(S8), SUM(S9), SUM(S10), SUM(S11) , SUM(S12), SUM(S13), SUM(S14) WHERE S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14 > 0 FROM Evaluering"; Any suggestions?
Selecting Min,max,avg Of Multiple Columns
I have to select "min, max, avg, sum( if( somecolumn = 123, 1, 0 ) )" from 5 to 15 columns so far, each query is for different table. Is there any other way of doing that except writing all the columns to query ? Of course that job does the script, but still, query looks miles long. Example of table: CREATE TABLE `example` ( `ID` int(255) NOT NULL auto_increment, `some_int` int(255) NOT NULL default '0', `some_int2` int(4) NOT NULL default '0', `create_time` date NOT NULL default '0000-00-00', `some_int3` decimal(65,2) default NULL, `some_int4` int(255) NOT NULL, `some_int5` int(255) NOT NULL, `a_somecolumn` decimal(65,2) default NULL, `a_somecolumn2` decimal(65,2) default NULL, `a_somecolumn3` decimal(65,2) default NULL, `a_somecolumn4` decimal(65,2) default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; So, I have to select these "min,max,avg,sum" from all the a_somecolumn.
Multiple Records As Columns
I have a table of Sessions and a table of Speakers. Each Session could have several Speakers and each Speaker could speak on several Sessions. I have a link table containing just SessionID's and SpeakerID's. All this is working fine on a successful website. I am now trying to write a query that will return records for each Session with each of the Speakers' details for that Session. Each record could have a different number of fields according to how many Speakers were assigned to that Session - this is the bit that makes it seem difficult.
DISTINCT With Multiple Columns
I need to select unique combinations of 4 columns from one table and insert them into a new table but I can't think of any way of finding unique combinations of more than 1 column since SELECT DISTINCT only works on single columns.
Searching Across Multiple Columns
Is it possible to search for a word across multiple columns in a table? For example: Table contains columns "Year" "Make" "Model" "Color" (etc etc.....) Then, using a user entered keyword, generate a query that checks the word against all ( or a list ) of columns in that table? Something like.... SELECT * from Table where * LIKE '%keyword%'
Adding Multiple Columns
Schema on a table has changed between versions of an application I use. I'm planning on migrating the existing table (member table as is, data and schema) and then wish to add the new columns after an existing column (eg password) and am wondering do I have to add each column at a time with multiple sql statements or can I add them all in one sql? Additional columns: `invisible` set('1','0') default '0', `pmfolders` text NOT NULL, `saveogpm` char(3) NOT NULL default '', `emailonpm` char(3) NOT NULL default '', `useoldpm` char(3) NOT NULL default '', `webcam` varchar(75) default NULL,
Query WHERE Multiple Columns
I was wondering which is faster: creating a table with 1 column and 2 million rows OR 5 columns and about 400,000 rows the only difference is I would have to have WHERE col=1 OR col2=1 OR col3=1 OR col4=1
Update Multiple Columns
I get an error on this command: UPDATE patchMapTable SET row=0,column=0,patchNumber=23 "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 'column=0,patchNumber=23' at line 1" row, column, and patchNumber are columns in patchMapTable all 3 are int row and column are primary keys
Include Multiple Columns
I have a table with these fields: id - account - ip - data what I want to do is select only unique ip, but also include the 'data' field in the result so that I can print the unique ip along with the associated data .. is this possible?i tried 'select distinct ip,data from table' but that returns duplicate ips.
DISTINCT Problem... I Need Multiple Columns!
$qry = "SELECT DISTINCT(`title`, `body`, `id`, `guid`) FROM news ORDER BY date DESC LIMIT 5"; That results in an error near the distinct all the way to the end. What I need, is a way to select title, body, id and guid where they are ALL unique (there are NO rows selected with the same title, body, id or guid)
Search Results In Multiple Columns
Currently using WHERE email LIKE '%$keyword%' I would like for the keyword to look into multiple fields WHERE email,fname,lname LIKE '%$keyword%' Searching the entire table would be fine too. I tried something similar to the above and it didn't work. Sorry if this has been covered before. I couldn't find a thread...
Joining Multiple Columns To The Same Table...
I have a table of job postings and they can have up to three certifications associated with them. In the job table they are stored as certification_1, certification_2, certification_3 and feed off the same look-up table. I need to join the jobs table to the look-up table for reporting so I'm trying to write three join statements (one for each column) but I keep getting the following error: "Not unique table/alias: 'certification'" The query is below: SELECT job_postings.fname, job_postings.lname, job_postings.phone, job_postings.extension, job_postings.fax, job_postings.email, job_postings.title, location.value as location, job_postings.posting_date, job_postings.description, licensure.value as licensure, education.value as education, certification.value as certification_1, certification.value as certification_2, certification.value as certification_3, job_postings.salary, experience.value as experience, description.value as job_description FROM job_postings LEFT OUTER JOIN location ON location.id = job_postings.location LEFT OUTER JOIN licensure ON licensure.id = job_postings.licensure LEFT OUTER JOIN education ON education.id = job_postings.education LEFT OUTER JOIN certification ON certification.id = job_postings.certification_1 LEFT OUTER JOIN certification ON certification.id = job_postings.certification_2 LEFT OUTER JOIN certification ON certification.id = job_postings.certification_3 LEFT OUTER JOIN experience ON experience.id = job_postings.experience LEFT OUTER JOIN description ON description.id = job_postings.job_desc
Select DISTINCT Between Multiple Columns??
So i have a table 'hobbies' in which i store up to 6 user's hobbies. The columns are called: hobby1 hobby2 hobby3 hobby4 hobby5 hobby6 Now I want to list all hobbies but without having the same results over and over. I tried: SELECT DISTINCT hobby1, hobby2, hobby3, hobby4, hobby5, hobby6 but this filters only hobby1 Also tried: SELECT DISTINCT hobby1, DISTINCT hobby2, DISTINCT hobby3, DISTINCT hobby4, DISTINCT hobby5, DISTINCT hobby6
Update Syntax For Multiple Columns
I'm looking for a feature I'm accustomed to with other SQL products, whereby multiple columns can be updated with a single SET clause, e.g.: UPDATE tab1 SET (col1, col2, col3) = (SELECT col4, col5, col6 FROM tab2 WHERE tab2.col1 = tab1.col1); I don't see this feature documented for MySQL. Is there a way to accomplish it?
SELECT From Multiple Tables With Different Columns
I am trying to combine 2 SELECT queries so that I can order them to display in proper date order. The queries I am using are below. My question is, how can I join together these queries so that they will mix the news and events together to display them in order instead of displaying the news items first in date order then displaying the events beneath them in their order. $query = "SELECT * FROM news_articles WHERE (date_full >= '$days_back' AND date_full <= '$days_ahead') AND (news_type = '$news_query') ORDER BY 'date_full' DESC"; $query = "SELECT calendar.* FROM calendar LEFT JOIN events_type ON events_type.event_id = calendar.id WHERE calendar.date_full >= '$eventdate_time' AND date_full <= '$days_time_ahead' AND STATUS = 'active' AND (events_type.type_title = '$events_query') ORDER BY 'date_full' ASC"; Anyone got any ideas?
|
TRACKER
