concatenate multiple fields

I don't have any troubles concatenating two fields together but so far, I
haven't been able to concatenate more than two fields.
(Order.FirstName+' '+order.LastName) as Name
will work
(Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
will not work.
Any suggestions?I can't think of any reason that shouldn't work.
What is the exact error?
Is this a problem you see in Query Analyzer or
via your "client" code such as ASP or ASP.NET?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"jeff fisher" <jeff@.fisher.com> wrote in message
news:eWY6$uvlFHA.3120@.TK2MSFTNGP09.phx.gbl...
>I don't have any troubles concatenating two fields together but so far, I
> haven't been able to concatenate more than two fields.
> (Order.FirstName+' '+order.LastName) as Name
> will work
> (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
> will not work.
> Any suggestions?|||If by "not work" you mean the seconds example is NULL, then it's because the
middle name field is NULL -- concatenating any char field with NULL yeilds
NULL; mathmatical, bitwise, and other operations have similar behavior.
Anyways this is a display issue and would be better handled by your client
code, but for some solutions, in BOL look up:
- ISNULL
- COALESCE
- SET CONCAT NULL YIELDS NULL
"jeff fisher" wrote:

> I don't have any troubles concatenating two fields together but so far, I
> haven't been able to concatenate more than two fields.
> (Order.FirstName+' '+order.LastName) as Name
> will work
> (Order.FirstName+' '+ order.MiddleName+ ' '+order.LastName) as Name
> will not work.
> Any suggestions?
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.
What the heck does "will not work" mean' Would you like to go to a
doctor that tells you something like that? Why did you use a reserved
word for a table name'
My guess -- based on absolutely nothing you told us -- is that you
have NULL-able columns and do not know that NULLs propagate, one of the
most basic priniciples in SQL.

concatenate

How can I concatenate a column of data that will be grouped together on a
separate column?
Thanks,
TonyBest place for this is usually at the presentation layer. Kludges exist, of
course.
http://www.aspfaq.com/2529
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:1BC21056-AF51-4E03-BA18-19CF1D0C0854@.microsoft.com...
> How can I concatenate a column of data that will be grouped together on a
> separate column?
> Thanks,
> Tony|||Tony,
Can you provide us with more information and CREATE/INSERT if possible?
HTH
Jerry
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:1BC21056-AF51-4E03-BA18-19CF1D0C0854@.microsoft.com...
> How can I concatenate a column of data that will be grouped together on a
> separate column?
> Thanks,
> Tony

Con Cat In Ate

I have a first name and a last name, in two different fields. I use a simple bit of SQL to show them together.

SELECT
{fn concat(dbo.table_employee.first_name , dbo.table_employee.last_name)}
FROM
dbo.table_employee

Gives me results like BradWilliams and DarWilliams and such.

Simple enough. But, it would be nice to put a space between first name and last name. And that is sending me for a loop. Any advise?

Thanks,
DanDan
why do it at SQL level - do it at the presentation / UI level, unless you have a direct need - ie the export from the SQL goes to another process.

how do you handle null forenames. depending on the SQL engine you are using you could write a function to do this.|||SELECT
{fn concat (dbo.table_employee.first_name,
{fn concat (' ', dbo.table_employee.last_name)})}
FROM
dbo.table_employee|||Perfect, Ida, that got it, thank you|||I wouldn't go so far as 'perfect'
say Fname= "Dan", SName="Srobe"
SQL returns: "Dan_Srobe"
say Fname= SName="Dansrobe"
SQL returns: "_Dansrobe", when I'm guessing you would want "Dansrobe" with no space "_".
It becomes more of a problem if you want to build a name from say Title,FName,SName,Qualifications where you could end up with
"__Dansrobe_"

still if it works appropriately then maybe it is "perfect" after all.

Composite clustered index - column order

Want to check my thinking with you folks...

I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.

Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000

C3 is effectively number of seconds since 01/01/1970.

The usage of the table is typically, insert a row, do something else, then update it.

Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.

My thinking is that this composite index is better ordered C1,C2,C3.

My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.

Welcome your thoughts.

What are the data types of these columns? If C3 is a datetime or a bigint, updating it with a larger value (more seconds since 1970) should not be causing page splits. That usually happens with varchars that are updated to a larger value for example. You are usually better off to have a narrow clustered index.

What are you trying to accomplish here? Are you worried about SELECT performance, INSERT/UPDATE performance, or about index size and maintenance?

If C3 is being updated a lot, you might be better off to have the clustered index on C1, C2, and then have a non-clustered index on C3.

|||

"What are the data types of these columns"

char(4),Char(4) and int

"If C3 is a datetime or a bigint, updating it with a larger value (more seconds since 1970) should not be causing page splits"

Together the 3 columns provide unique key, and none of the columns are updated. The page splitting aspect I'm considering is, if the first column in the clustered is effectively an identity (so the next value inserted can only ever be bigger than the last), does this bias the inserts to one side of the tree - page splits being necessary there, because a fill factor spreads the free space throughout the tree?

"You are usually better off to have a narrow clustered index"

Yes. I appreciate that, because it gets tagged onto all non-clustered indexes. Let's assume that space isn't an issue.

Looking for best pewrformance for select \ insert & update. Index size & maint not an issue.

Thanks

Complex SQL Query!

I have three tables in my database, part of a tennis league results and
fixtures website I am putting together.
Results Table
id fixture_id home_team_rubbers away_team_rubbers
1 1 2 2
2 2 1 3
3 3 3 1
4 4 1 3
5 5 0 4
6 6 3 1
7 7 4 0
8 8 4 0
9 9 1 3
10 10 2 2
11 11 2 2
12 12 0 4
13 13 1 3
14 14 2 2
15 15 3 1
16 16 4 0
17 17 4 0
18 18 3 1
19 19 1 3
20 20 2 2
21 21 0 4
22 22 2 2
23 23 3 1
24 24 3 1
Fixtures Table
id home_team_id away_team_id
1 1 2
2 1 3
3 2 1
4 2 3
5 3 1
6 3 2
7 4 5
8 4 6
9 4 7
10 5 4
11 5 6
12 5 7
13 6 4
14 6 5
15 6 7
16 7 4
17 7 5
18 7 6
19 8 9
20 8 10
21 9 8
22 9 10
23 10 8
24 10 9
Team Table
id division_id club_id
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 2
7 2 3
8 3 1
9 3 2
10 3 2
What I want to do it list the league table positions for all the teams
in all the divisions with the same club_id as myclub_id through an SQL
query.
The league table positions are determined by the total number of
rubbers acheived (total_rubbers_acheived) which is the sum of
home_team_rubbers and away_team_rubbers and then sorted so that the
highest is in position 1.
For example the league table for division_id = 1
team_id total_rubbers_acheived league_table_position
3 9 1
1 8 2
2 7 3
For example the league table for division_id = 2
team_id total_rubbers_acheived league_table_position
7 19 1
4 14 2
6 9 3
5 6 4
For example the league table for division_id = 3
team_id total_rubbers_acheived league_table_position
10 10 1
8 8 2
9 6 3
The resulting output from the query that I desire is the following.
Query Output - when myclub_id = 1
team_id division_id league_table_position
1 1 2
4 2 2
8 3 2
Query Output - when myclub_id = 2
team_id division_id league_table_position
2 1 3
5 2 4
6 2 3
9 3 3
10 3 1
Query Output - when myclub_id = 3
team_id division_id league_table_position
3 1 1
7 2 1
Any ideas how I can do this?
Cheers,
SimonCould you please post your DDL + INSERT statements of your data? That will
make it much easier to craft a solution.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<simon.stockton@.baesystems.com> wrote in message
news:1149416751.611482.138870@.u72g2000cwu.googlegroups.com...
I have three tables in my database, part of a tennis league results and
fixtures website I am putting together.
Results Table
id fixture_id home_team_rubbers away_team_rubbers
1 1 2 2
2 2 1 3
3 3 3 1
4 4 1 3
5 5 0 4
6 6 3 1
7 7 4 0
8 8 4 0
9 9 1 3
10 10 2 2
11 11 2 2
12 12 0 4
13 13 1 3
14 14 2 2
15 15 3 1
16 16 4 0
17 17 4 0
18 18 3 1
19 19 1 3
20 20 2 2
21 21 0 4
22 22 2 2
23 23 3 1
24 24 3 1
Fixtures Table
id home_team_id away_team_id
1 1 2
2 1 3
3 2 1
4 2 3
5 3 1
6 3 2
7 4 5
8 4 6
9 4 7
10 5 4
11 5 6
12 5 7
13 6 4
14 6 5
15 6 7
16 7 4
17 7 5
18 7 6
19 8 9
20 8 10
21 9 8
22 9 10
23 10 8
24 10 9
Team Table
id division_id club_id
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 2
7 2 3
8 3 1
9 3 2
10 3 2
What I want to do it list the league table positions for all the teams
in all the divisions with the same club_id as myclub_id through an SQL
query.
The league table positions are determined by the total number of
rubbers acheived (total_rubbers_acheived) which is the sum of
home_team_rubbers and away_team_rubbers and then sorted so that the
highest is in position 1.
For example the league table for division_id = 1
team_id total_rubbers_acheived league_table_position
3 9 1
1 8 2
2 7 3
For example the league table for division_id = 2
team_id total_rubbers_acheived league_table_position
7 19 1
4 14 2
6 9 3
5 6 4
For example the league table for division_id = 3
team_id total_rubbers_acheived league_table_position
10 10 1
8 8 2
9 6 3
The resulting output from the query that I desire is the following.
Query Output - when myclub_id = 1
team_id division_id league_table_position
1 1 2
4 2 2
8 3 2
Query Output - when myclub_id = 2
team_id division_id league_table_position
2 1 3
5 2 4
6 2 3
9 3 3
10 3 1
Query Output - when myclub_id = 3
team_id division_id league_table_position
3 1 1
7 2 1
Any ideas how I can do this?
Cheers,
Simon|||CREATE TABLE `results` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`fixture_id` SMALLINT NOT NULL ,
`home_team_rubbers` SMALLINT NOT NULL ,
`away_team_rubbers` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `results` ( `id` , `fixture_id` , `home_team_rubbers` ,
`away_team_rubbers` )
VALUES (
NULL , '1', '2', '2'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '3', '1'
);
CREATE TABLE `fixtures` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`home_team_id` SMALLINT NOT NULL ,
`away_team_id` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `fixtures` ( `id` , `home_team_id` , `away_team_id` )
VALUES (
NULL , '1', '2'
), (
NULL , '1', '3'
), (
NULL , '2', '1'
), (
NULL , '2', '3'
), (
NULL , '3', '1'
), (
NULL , '3', '2'
), (
NULL , '4', '5'
), (
NULL , '4', '6'
), (
NULL , '4', '7'
), (
NULL , '5', '4'
), (
NULL , '5', '6'
), (
NULL , '5', '7'
), (
NULL , '6', '4'
), (
NULL , '6', '5'
), (
NULL , '6', '6'
), (
NULL , '7', '4'
), (
NULL , '7', '5'
), (
NULL , '7', '6'
), (
NULL , '8', '9'
), (
NULL , '8', '10'
), (
NULL , '9', '8'
), (
NULL , '9', '10'
), (
NULL , '10', '8'
), (
NULL , '10', '9'
);
CREATE TABLE `teams` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`division_id` SMALLINT NOT NULL ,
`club_id` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `teams` ( `id` , `division_id` , `club_id` )
VALUES (
NULL , '1', '1'
), (
NULL , '1', '2'
), (
NULL , '1', '3'
), (
NULL , '2', '1'
), (
NULL , '2', '2'
), (
NULL , '2', '2'
), (
NULL , '2', '3'
), (
NULL , '3', '1'
), (
NULL , '3', '2'
), (
NULL , '3', '2'
);|||This code doesn't work in SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<simon.stockton@.baesystems.com> wrote in message
news:1149429772.119617.228190@.h76g2000cwa.googlegroups.com...
CREATE TABLE `results` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`fixture_id` SMALLINT NOT NULL ,
`home_team_rubbers` SMALLINT NOT NULL ,
`away_team_rubbers` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `results` ( `id` , `fixture_id` , `home_team_rubbers` ,
`away_team_rubbers` )
VALUES (
NULL , '1', '2', '2'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '4', '0'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '1', '3'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '0', '4'
), (
NULL , '2', '2', '2'
), (
NULL , '2', '3', '1'
), (
NULL , '2', '3', '1'
);
CREATE TABLE `fixtures` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`home_team_id` SMALLINT NOT NULL ,
`away_team_id` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `fixtures` ( `id` , `home_team_id` , `away_team_id` )
VALUES (
NULL , '1', '2'
), (
NULL , '1', '3'
), (
NULL , '2', '1'
), (
NULL , '2', '3'
), (
NULL , '3', '1'
), (
NULL , '3', '2'
), (
NULL , '4', '5'
), (
NULL , '4', '6'
), (
NULL , '4', '7'
), (
NULL , '5', '4'
), (
NULL , '5', '6'
), (
NULL , '5', '7'
), (
NULL , '6', '4'
), (
NULL , '6', '5'
), (
NULL , '6', '6'
), (
NULL , '7', '4'
), (
NULL , '7', '5'
), (
NULL , '7', '6'
), (
NULL , '8', '9'
), (
NULL , '8', '10'
), (
NULL , '9', '8'
), (
NULL , '9', '10'
), (
NULL , '10', '8'
), (
NULL , '10', '9'
);
CREATE TABLE `teams` (
`id` SMALLINT NOT NULL AUTO_INCREMENT ,
`division_id` SMALLINT NOT NULL ,
`club_id` SMALLINT NOT NULL ,
UNIQUE (
`id`
)
) ENGINE = innodb;
INSERT INTO `teams` ( `id` , `division_id` , `club_id` )
VALUES (
NULL , '1', '1'
), (
NULL , '1', '2'
), (
NULL , '1', '3'
), (
NULL , '2', '1'
), (
NULL , '2', '2'
), (
NULL , '2', '2'
), (
NULL , '2', '3'
), (
NULL , '3', '1'
), (
NULL , '3', '2'
), (
NULL , '3', '2'
);|||I am actually using MySQL, sorry I not sure what the differences are
and hence why it wouldn't worl, perhaps the ENGINE value is not correct!|||Well, this is a SQL Server newsgroup. As such, we make the assumption that
the problems you post here are for SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<simon.stockton@.baesystems.com> wrote in message
news:1149435355.608589.113130@.c74g2000cwc.googlegroups.com...
I am actually using MySQL, sorry I not sure what the differences are
and hence why it wouldn't worl, perhaps the ENGINE value is not correct!|||>> Any ideas how I can do this? <<
Have you considered using a relational design instead mimicking a
magnetic tape file? There is no such thing as a vague, universal id
that you can use to mark all the things in creation. Auto-increment is
a way of saying that you have no idea what a key is - and it ain't
a physical locator generated by the hardware!
I see that teams have no names, that you have no referencing among the
tables, so they are totally unrelated. Instead of computing standing
and results, you seem to want to write them to physical storage, thus
missing the basic point that tables - unlike files - can be virtual
tables.
I also find it strange that you have results, but nobody plays a game
in your model. But then you have a model where everything is a
SMALLINT.
CREATE TABLE Teams
(team_id SMALLINT NOT NULL PRIMARY KEY,
team_name CHAR(15) NOT NULL,
team_div CHAR(15) NOT NULL,
etc.);
When you say "rubbers" it means you are playing Bridge to me (or
engaged in another of my favorite sports with proper protection).
I vaguely remember that you score 0 (love), 15, 30, and 40 points
which leads to four points to win a game, six games to win a set and
two (or three?) sets to win a match. I am not sure if you want to keep
each set or just match points. Either way, you need better CHECK()
constraints than I am showing here to enforce valid scoring.
CREATE TABLE Games
(home_team_id SMALLINT NOT NULL
REFERENCES Teams(team_id),
away_team_id SMALLINT NOT NULL
REFERENCES Teams(team_id),
CHECK (away_team_id <> home_team_id),
game_date DATE NOT NULL,
home_team_score SMALLINT NOT NULL
CHECK (home_team_score >= 0),
away_team_score SMALLINT NOT NULL
CHECK (away_team_score >= 0),
PRIMARY KEY (away_team_id, home_team_id, game_date)
);
Do you need to be sure that teams are in the same division? Etc. You
did not post a good spec and assumed that everyone plays competition
Tennis, so they know the terms.

Complex queries using WHERE and mix of OR and AND

How do you effectively mix OR and AND together? I have the query below for m
y
SEARCH page. I would like the users to have the option of selecting one fiel
d
to search with OR selecting pairs of fields together to search the database
with. The problem is, with the SQL statement below, OR works (select 1 field
to search with) but AND does not (if I use more than 1 field to search with,
the search returns all entries in the database.
Could someone pls point out to me what I'm doing wrong? I'd really
appreciate it.
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE (vNXX LIKE 'varNXX' AND vLN LIKE 'varLINE') OR (vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR
(vMN LIKE 'varMONTH' AND vYR LIKE 'varYEAR' AND vSAVE LIKE 'varSAVE') OR (vM
N
LIKE 'varMONTH' AND vYR LIKE 'varYEAR') OR (vNXX LIKE 'varNXX') OR (vLN LIKE
'varLINE') OR (vMN LIKE 'varMONTH') OR (vDT LIKE 'varDATE') OR (vYR LIKE
'varYEAR') OR (vAGNT LIKE 'varAGENT') OR (vORD LIKE 'varORD') OR (vSAVE LIKE
'varSAVE')
ORDER BY vMN DESC, vDT DESC, vYR DESC> AND does not (if I use more than 1 field to search with,
> the search returns all entries in the database.
Could you post a working example of this so that we can understand what you
mean. There's no reason why you can't use as many ANDs and ORs as you need
in a WHERE clause. AND takes precedence over OR unless you use brackets to
alter the order of evaluation.
David Portas
SQL Server MVP
--|||Hi David,
First I formatted your SQL using www.sqlinform.com .
Then I have seen that some conditions are not logic, e.g. using
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
together with
(
vNXX LIKE 'varNXX'
)
because this condition is true independent from the value of vLN. You
will need to code your SQL in a different way.
Regards
Guido
SELECT vNXX, vLN, vMN, vDT, vYR, vAGNT, vORD, vSAVE
FROM salesdb
WHERE
(
vNXX LIKE 'varNXX'
AND vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
AND vAGNT LIKE 'varAGENT'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
AND vSAVE LIKE 'varSAVE'
)
OR
(
vMN LIKE 'varMONTH'
AND vYR LIKE 'varYEAR'
)
OR
(
vNXX LIKE 'varNXX'
)
OR
(
vLN LIKE 'varLINE'
)
OR
(
vMN LIKE 'varMONTH'
)
OR
(
vDT LIKE 'varDATE'
)
OR
(
vYR LIKE 'varYEAR'
)
OR
(
vAGNT LIKE 'varAGENT'
)
OR
(
vORD LIKE 'varORD'
)
OR
(
vSAVE LIKE 'varSAVE'
)
ORDER BY vMN DESC, vDT DESC, vYR DESC