Concatenating Nulls
I would like to concatenate two columns where one is potentially null. The result I get now is if one is null, the concatenated value is null. Here's what I would like:
Column1 = '123', Column2 = null, return '123' (not null)
Column1 = null, Column2 = 'abc', return 'abc' (not null)
Column1 = '123', Column2 = 'abc', return '123abc'
The statement that isn't working is something like:
SELECT CONCAT(Column1,Column2) FROM Table WHERE id=12
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Concatenating Conundrum
I'm new to MySQL and am having a problem concatenating within an expression. What I have now is this: update table MYTABLE set FIELD10 = CONCAT(left(FIELD1,3)),(left(FIELD2,3)); I want FIELD10 to contain the first three characters of field1 and the first three characters of field2. What am I missing here?
Concatenating Path
I have been frustrated by this by some time now. I have a simple table like this:Code: id int(255) parent int(255) name varchar(255) path varchar(255) I am creating a hierchial table with `id` being the id of the record, `parent` the parent's id, `name` the name of the record, and `path` the path, from parent to child. My question is how to select the path from the parent, insert that into path, then append an incremental value. I am trying to do this in php in one query. So I am trying to form a query to do this.
Concatenating Multiple Rows To One Field?
I got a slight problem with a web application, its supposed to several entries for a certain ID, and display them as one string. Example: ID | Name ------------- 1 | Jack 1 | Peter 2 | John 2 | Mark 2 | Ellis Expected Result for ID 1 should be "Jack, Peter" for example, or "John, Mark, Ellis" for 2. Least problem would be clipping off a trainling or leading ",", but the main problem is a SELECT statement to get them all in one go. Else I'd have to select every Name per ID, and concatenate them in PHP or elsewhere. Given its around 100 IDs, I'd have to do like 100 querys to get the names, *in addition* to the Querys I need before and after. I want to avoid that, but I couldnt find a simple solution that works in MySQL 4.1 and above, aswell as it mustn't involve Stored Functions or something, because I got no access to the mysqld itself, to add the funcs as modules. Code:
NULLS 2
if I have a table for: PHONE_NUMBERS that can be associated with FKeys to one or more COMPANY_ID and/or a PERSON_ID how would you construct the columns/relationships? Assuming a PHONE_NUMBER_ID as PK then a n:m relationship to both the COMPANY and the PERSON tables??? (i.e. a new table with a compound PK of phone_number_id plus company_id plus person_id) but then I'd have to allow either the company_id or the person_id to be null wouldn't I?
NULLS
if you had a table with columns: Prefix_ID ---> data for say 75% of rows normalized to Mr/Mrs./Ms FirstName ---> data for 50% of rows MiddleName ---> data for 25% of rows LastName ---> data for 100% of rows Suffix_ID ---> data for 5% of rows normalized to Jr./Sr./III would you: 1.) make suffix_ID NULL-able ? 2.) make any other column NULL-able? 3.) make Suffix_ID default to 1 where Suffix_ID 1 = ''? 4.) make FirstName and/or MiddleName default to '' ? or other alternatives/suggestions for best practices?
Joins And Nulls
I have three tables containing information about companies. The first lists companies, each with an id. The second defines what information (attributes) can be held about each company and format it takes (eg address, postcode, owner, etc). The third table lists the specific values of each attribute for each company. Each attribute value for each company has its own line. Now, I am trying to select data from this group of tables. The problem is that not every company has complete information. Some, for example, don't have a postcode. But where they do, I'd like to display that. Which brings me to this SQL: Code:
NOT NULLs With Default
I'm cleaning up a database but I see this all over the MySQL world. I understand that having defaults speeds things up. But what I don't understand is why someone would make a field NOT NULL and then put a null default in it? This makes no sense to me at all. Is this the fault of using phpMyAdmin to create tables? Here's a sample: CREATE TABLE `listnames` ( `id` int(11) NOT NULL auto_increment, `list_name` char(30) NOT NULL default '', `display_name` char(100) NOT NULL default '', `active` char(3) NOT NULL default '', `last_modified` timestamp(14) NOT NULL, UNIQUE KEY `id` (`id`) ) Ok, so you don't want NULLs in your data but there is no default. It's very likely that the data will be wildly differently. So setting a default makes no sense -- or does it? I don't want to drop the default '' if it makes sense & I'm just not getting it.
Finding NULLS
I am querying with the following hoping NULL and not NULL: SELECT * FROM pet WHERE sex='NULL'; returns empty set, but there should be one record. SELECT * FROM pet WHERE death<>'NULL'; also returns empty set when there should be one record.
Count Of NULL And NOT NULLS
How would I write a query to count the number of instances where a the PREP field is NULL and when it is not null. I know how to do it seperately, can I get the numbers in the same query. I want something like this: Project #PREP_NOTNULL #PREP_NULL
Ignoring NULLs In Indexes
Access has a nice feature that allows creation of indexes WITH IGNORE NULL which means that records with null values are ignored in the index, so the index will be shorter and faster to search. Is there any equivalent for this in MySQL?
Equality Testing With NULLs?
When I do "SELECT count(1) FROM table_name WHERE 0 <> 2", I get the total number of rows in the table. When I do "SELECT count(1) FROM table_name WHERE NULL <> 2", I get 0. Shouldn't I the same answer as I did with WHERE 0 <> 2?
SQL Select Statement Returning NULLS
when I run this SQL qry: SELECT DATE_FORMAT(recDate, '%Y-%m-%d') AS a_date, recTitle, recBody, recID FROM j_plug ORDER by recID DESC LIMIT 15; The 'recTitle' column often returns a NULL. Is there any way I can change the SQL statement to return an actual value if there is a NULL, such as 'NOTHING'. This is probably a silly request - obviously if it is a NULL, then that should be that. Trouble is that I am having a problem with my ASP code when the value is NULL, and it'd be useful if it returned an actual value for NULL records.
Unexpected NULLs When Using 'left Join'
I'm trying to select all records from a table, plus the appropriate values from other tables to which my first table has pointers. I quickly discovered the benefits of LEFT JOIN over WHERE clauses but then found that I was getting more NULLs than expected. For example: Code:
Unique Index With Nulls Where Is It Stored
The memory space defined by DataMemory is also used to store ordered indexes, which use about 10 bytes per record. Each table row is represented in the ordered index. A common error among users is to assume that all indexes are stored in the memory allocated by IndexMemory, but this is not the case: Only primary key and unique hash indexes use this memory; ordered indexes use the memory allocated by DataMemory. However, creating a primary key or unique hash index also creates an ordered index on the same keys, unless you specify USING HASH in the index creation statement. This can be verified by running ndb_desc -d db_name table_name in the management client. if i issue a : create unique index idx_b on tablename(b); where b can contain nulls is this index stored in index memory and in data memory or just data memory?
NULLs And Empty Strings, Treating Them Equally?
I've built a database class in PHP which, when given an empty string for a field value, converts it to NULL, simply because although their technically not the same thing, as far as useful data goes, they are equally useless. I want to use the NOT NULL flag on fields to dictate which fields should have data, and which shouldn't, and really, an empty string is no data. I was just wondering what other peoples opinion was on this, and weather or not it is a reasonable restriction to enforce, I cant think of any situation where data is required for a field (NOT NULL), and an empty string would be a reasonable value.
LOAD DATA INFILE, Updates All Other Fields With Nulls.
I am using the following command to update three fields ONLY in the destination table. The infile that I am using was created using: .......................................................... select code, desc2 , qty_on_hand from plu_tmp into outfile "c:mp.txt" .......................................................... LOAD DATA INFILE "c:mp.txt" REPLACE INTO TABLE `plu` (code, desc2 , qty_on_hand) I expect to update only the three fields, without touching the other fields. What happens is that the specified fields are updated, and all the rest of the fields are reset to nulls as they have not been specified. Is there any way I can use the load inifile to insert / Update just the specified fields.
NULLS (was "Is This Normal?")
to have empty cells in the results? like...i have one column called 'specs' but only a few rows in the result have actually a value in it, the rest is NULL i know beforehand that the result will be like this most of the time...is it bad?
|
TRACKER
