Hi all, I have two tables that don't have any common data:
[Table1]
Column11 Int
AnotherColumn Int
[Table2]
Column21 Int
Data:
[Table1]
Column11 | AnotherColumn
111 | 8
112 | 8
113 | 8
114 | 8
[Table2]
Column21
211
212
213
214
I need to join them, to get a rowset that looks like this:
Column11 | Column21
111 | 211
112 | 212
113 | 213
114 | 214
When I try to join them, I use one of these two SQL Statements:
Select
Column11,
Column21
From Table2
Inner Join Table1 On Table1.AnotherColumn = 8
Select
Column11,
Column21
From Table2, Table1
Where Table1.AnotherColumn = 8
But I get a cartesian product (which I don't want). How can I just "put one
column next to the other" in my resultset, without having a Cartesian
product?
Thanks in advance,
FrankSeems like there are no relations between the tables like a
parent-child relation. Therefore only a cartesian product will make
sense. (?!)
HTH, jens Suessmeyer.|||Does the data really look like this? If you are trying to "line up"
physical rows or in the order of insertion, there's no way to tell SQL
Server to correlate that. If you are trying to match up 11, 12, 13 and 14
as "belonging to the same row", then you can do something like this:
SET NOCOUNT ON
CREATE TABLE #Table1
(
Column11 Int,
AnotherColumn Int
)
CREATE TABLE #Table2
(
Column21 Int
)
INSERT #Table1
SELECT 111,8
UNION SELECT 112,8
UNION SELECT 113,8
UNION SELECT 114,8
INSERT #Table2
SELECT 211
UNION SELECT 212
UNION SELECT 213
UNION SELECT 214
SELECT
t1.Column11,
t2.Column21
FROM
#Table1 t1
INNER JOIN #Table2 t2
ON t1.Column11 % 100 = t2.Column21 % 100
WHERE
t1.AnotherColumn = 8
DROP TABLE #table1, #table2
If this is not what you're looking for, please provide better requirements.
See http://www.aspfaq.com/5006
"John Francisco Williams" <JohnFranciscoWilliams1010@.Yahoo.Com> wrote in
message news:erVNz9iEGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi all, I have two tables that don't have any common data:
> [Table1]
> Column11 Int
> AnotherColumn Int
> [Table2]
> Column21 Int
> Data:
> [Table1]
> Column11 | AnotherColumn
> 111 | 8
> 112 | 8
> 113 | 8
> 114 | 8
> [Table2]
> Column21
> 211
> 212
> 213
> 214
> I need to join them, to get a rowset that looks like this:
> Column11 | Column21
> 111 | 211
> 112 | 212
> 113 | 213
> 114 | 214
> When I try to join them, I use one of these two SQL Statements:
> Select
> Column11,
> Column21
> From Table2
> Inner Join Table1 On Table1.AnotherColumn = 8
> Select
> Column11,
> Column21
> From Table2, Table1
> Where Table1.AnotherColumn = 8
> But I get a cartesian product (which I don't want). How can I just "put
> one column next to the other" in my resultset, without having a Cartesian
> product?
> Thanks in advance,
> Frank
>|||not sure I understand what you really need, but try this:
create table #t1(i1 int primary key)
insert into #t1 values(123)
insert into #t1 values(124)
insert into #t1 values(125)
insert into #t1 values(126)
create table #t2(i2 int primary key)
insert into #t2 values(23)
insert into #t2 values(24)
insert into #t2 values(25)
insert into #t2 values(26)
insert into #t2 values(27)
select i1, i2 from
(select i1, (select count(*) from #t1 t11 where t11.i1<t1.i1) rn from
#t1 t1) t1
full outer join
(select i2, (select count(*) from #t2 t21 where t21.i2<t2.i2) rn from
#t2 t2) t2
on t1.rn=t2.rn
i1 i2
-- --
123 23
124 24
125 25
126 26
NULL 27
(5 row(s) affected)
on SQL Server 2005 you can use row_number() to calculate rn|||is it just a conincidence, or is it really that you want to match rows from
Table1 and Table2 in a way that 111 in Table1.Column11 matches 211 in
Table2.column22, 112 matches 212, etc? you can do something like this:
select *
from table1 t1 inner join table2 t2 on t1.column11%100=t2.column21%100
dean
"John Francisco Williams" <JohnFranciscoWilliams1010@.Yahoo.Com> wrote in
message news:erVNz9iEGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi all, I have two tables that don't have any common data:
> [Table1]
> Column11 Int
> AnotherColumn Int
> [Table2]
> Column21 Int
> Data:
> [Table1]
> Column11 | AnotherColumn
> 111 | 8
> 112 | 8
> 113 | 8
> 114 | 8
> [Table2]
> Column21
> 211
> 212
> 213
> 214
> I need to join them, to get a rowset that looks like this:
> Column11 | Column21
> 111 | 211
> 112 | 212
> 113 | 213
> 114 | 214
> When I try to join them, I use one of these two SQL Statements:
> Select
> Column11,
> Column21
> From Table2
> Inner Join Table1 On Table1.AnotherColumn = 8
> Select
> Column11,
> Column21
> From Table2, Table1
> Where Table1.AnotherColumn = 8
> But I get a cartesian product (which I don't want). How can I just "put
> one column next to the other" in my resultset, without having a Cartesian
> product?
> Thanks in advance,
> Frank
>|||and you can use PIVOT as well:
select [i1], [i2]
from (
select
row_number() over (order by i1) as rn,
'i1' as Src,
i1 as x
from #t1
union all
select
row_number() over (order by i2),
'i2',
i2
from #t2
) T PIVOT (
max(x) FOR Src in ([i1],[i2])
) as P
-- Steve Kass
-- Drew University
Alexander Kuznetsov wrote:
>not sure I understand what you really need, but try this:
>create table #t1(i1 int primary key)
>insert into #t1 values(123)
>insert into #t1 values(124)
>insert into #t1 values(125)
>insert into #t1 values(126)
>create table #t2(i2 int primary key)
>insert into #t2 values(23)
>insert into #t2 values(24)
>insert into #t2 values(25)
>insert into #t2 values(26)
>insert into #t2 values(27)
>select i1, i2 from
>(select i1, (select count(*) from #t1 t11 where t11.i1<t1.i1) rn from
>#t1 t1) t1
>full outer join
>(select i2, (select count(*) from #t2 t21 where t21.i2<t2.i2) rn from
>#t2 t2) t2
>on t1.rn=t2.rn
>
>i1 i2
>-- --
>123 23
>124 24
>125 25
>126 26
>NULL 27
>(5 row(s) affected)
>on SQL Server 2005 you can use row_number() to calculate rn
>
>|||This looks like you are creating the rows by matching the SORTED ORDER
OF THE VALUES IN EACH TABLE, in volation of the basic relational
principles. This means that the rows have no meaning whatsoever and
that you are probably doing this for display purposes, in violation of
the principle of a tiered archtecture.
However, look up a query I did to match boys and girls as dance
partners. The trick was to add a relative row in derived tables and to
use a view to close gaps when the base tables change.
CREATE VIEW DanceCard (boy_name, girl_name)
AS SELECT B.name, G.name
FROM
(SELECT B1.name, COUNT(B2.*)
FROM Boys AS B1, Boys AS B2
WHERE B2.name <= B1.name
GROUP BY B1.name) AS B(name, match_nbr)
FULL OUTER JOIN
(SELECT G1.name, COUNT(G2.*)
FROM Girls AS G1, Girls AS G2
WHERE G2.name <= G1.name
GROUP BY G1.name) AS G(name, match_nbr)
ON B.match_nbr = G.match_nbr;
This is not a good way to do such things; you really need a better
rule.|||On 5 Jan 2006 16:42:22 -0800, "--CELKO--" <jcelko212@.earthlink.net> wrote:
in <1136508142.931773.99530@.o13g2000cwo.googlegroups.com>
Is that your face in the piratesdinneradventure newspaper ads?|||>> in volation of the basic relational
principles. This means that the rows have no meaning whatsoever and
that you are probably doing this for display purposes, in violation of
the principle of a tiered archtecture. <<
In real life the problem is quite common, for instance:
- 20 non-smoking guests arrive in a hotel with 30 vacant identical
non-smoking rooms, each guest needs to get a room. And that does not
mean that "the rooms and the guests have no meaning whatsoever".
If this simple real life situation is in "volation of the basic
relational principles", as you say, that's just one more indication
that the relational theory is not perfect, it does not cover all the
bases.
Anyway, the vendors do listen to us practitioners, and they have
provided row_number() to deal with this very common problem. I guess
row_number() is in ANSI standard now, is it not?
Showing posts with label joing. Show all posts
Showing posts with label joing. Show all posts
Monday, March 12, 2012
Joing tables using more than one field
I have two tables I need to join but there are 2 fields which they
could be joined on.
could be joined on.
Using the example Tablles, TableA and TableB below;
TableA
ID1 ID2 Qty
1 Null 4
2 A 5
Null B 6
TableB
ID1 ID2 Qty
Null A 6
3 B 6
4 Null 7
Null C 8
I want to create TableC which will look like this;
ID1 ID2 TableA.Qty Tableb>Qty
1 Null 4 Null
2 A 5 6
3 B 6 6
4 Null Null 7
Null C Null 8
Any ideas?
Regards,
CiarnTry:
select
*
from
TableA a
join
TableB b on b.ID1 = a.ID1 and b.ID2 = a.ID2
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<chudson007@.hotmail.com> wrote in message
news:1142947436.438108.100180@.j33g2000cwa.googlegr oups.com...
I have two tables I need to join but there are 2 fields which they
could be joined on.
Using the example Tablles, TableA and TableB below;
TableA
ID1 ID2 Qty
1 Null 4
2 A 5
Null B 6
TableB
ID1 ID2 Qty
Null A 6
3 B 6
4 Null 7
Null C 8
I want to create TableC which will look like this;
ID1 ID2 TableA.Qty Tableb>Qty
1 Null 4 Null
2 A 5 6
3 B 6 6
4 Null Null 7
Null C Null 8
Any ideas?
Regards,
Ciarn|||select coalesce(a.ID1,b.ID1),
coalesce(a.ID2,b.ID2),
a.Qty,
b.Qty
from TableA a
full outer join TableB b on a.ID1=b.ID1 or a.ID2=b.ID2
Joing tables from different databases - performance issues
Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||Użytkownik Tibor Karaszi napisaÅ?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> Użytkownik Tibor Karaszi napisaÅ?:
>> 1 is same as 2. The optimizer has all the information and can process the query the same whether
>> the tables are in the same database or different database. For 3 and 4, the query is optimized
>> locally and parts of the query is passed onto the linked server. This limits the flexibility that
>> the optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test
>> with your data, schema, queries etc.
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||Użytkownik Tibor Karaszi napisaÅ?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> Użytkownik Tibor Karaszi napisaÅ?:
>> 1 is same as 2. The optimizer has all the information and can process the query the same whether
>> the tables are in the same database or different database. For 3 and 4, the query is optimized
>> locally and parts of the query is passed onto the linked server. This limits the flexibility that
>> the optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test
>> with your data, schema, queries etc.
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>
Joing tables from different databases - performance issues
Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the qu
ery the same whether the
tables are in the same database or different database. For 3 and 4, the quer
y is optimized locally
and parts of the query is passed onto the linked server. This limits the fle
xibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, yo
u need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on different physical machines and these machi
nes contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||
> It is really opene-end question , because only you do know about your tabl
es
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||U?ytkownik Tibor Karaszi napisa?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
> Merlin
>
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the qu
ery the same whether the
tables are in the same database or different database. For 3 and 4, the quer
y is optimized locally
and parts of the query is passed onto the linked server. This limits the fle
xibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, yo
u need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on different physical machines and these machi
nes contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||
> It is really opene-end question , because only you do know about your tabl
es
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||U?ytkownik Tibor Karaszi napisa?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
> Merlin
>
Joing tables from different databases - performance issues
Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
That really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:
>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
That really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:
>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin
Joing tables from different databases - performance issues
Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
It is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||
> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin
|||U?ytkownik Tibor Karaszi napisa?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin
|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
It is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||
> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin
|||U?ytkownik Tibor Karaszi napisa?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin
|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>
Friday, March 9, 2012
Joing tables from different databases - performance issues
Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinThat really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:
>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinThat really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:
>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin
Joing Multiple Columns
Hi All,
Bit of a newbie question i'm afraid, so sorry if this is a really
stupid question,
I am not sure if I am trying to do this in the right place as I am sure
SQL Server has a far better way of doing this
What I am trying to do is join the contents of several columns and
present the entry into a new column. the entries of all the columns I
am trying to join are different types such as dates and numbers. I want
to join all columns as a text string. To make it just a even more
tricky I wish to insert a # between all fields.
Just in case my poor description has got you thinking What the F*&K
here is an example of what I am trying to do
C1 C2 C3 C4 C5
Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890
I have been trying to do this in the Formula in the design veiw of the
table.
Any Advice would be fantastic.
Many Thank
IanTry:
select
C1 + '#' + cast (C2 as varchar (10)) + '#' + convert (char (10), C3,
103) + '#' + cast (C4 as varchar (10))
from
MyTable
--
Tom
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1140655591.245227.133490@.f14g2000cwb.googlegr oups.com...
Hi All,
Bit of a newbie question i'm afraid, so sorry if this is a really
stupid question,
I am not sure if I am trying to do this in the right place as I am sure
SQL Server has a far better way of doing this
What I am trying to do is join the contents of several columns and
present the entry into a new column. the entries of all the columns I
am trying to join are different types such as dates and numbers. I want
to join all columns as a text string. To make it just a even more
tricky I wish to insert a # between all fields.
Just in case my poor description has got you thinking What the F*&K
here is an example of what I am trying to do
C1 C2 C3 C4 C5
Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890
I have been trying to do this in the Formula in the design veiw of the
table.
Any Advice would be fantastic.
Many Thank
Ian
Friday, February 24, 2012
jOIN Query help(urgent)
i want to create a join query (for view) that will show one data per day for each agent.
just a select query joing these two tables..Seems like the date field is given me problem
i want result like below
TOTALCALL , TOTALESCA , AGENTID , DATE
50 , 5 , IDME1 , 10/28/2004 12:28:00 PM
TOTALESCA shows NuMbers of escalated calls out of totalcalls
table 1
TOTALCALL
AGENTID
DATE
TABLE 2
TOTALESCA
AGENTID
DATE
SAMPLE DATA ON TABLE 1
TOTALCALL, AGENTID, DATE
50 , IDME1 , 10/28/2004 12:28:00 PM
SAMPLE DATA ON TABLE 2
TOTALESCA, AGENTID , DATE
5 , IDME1 , 10/28/2004 12:28:00 PMTry datepart function to ignore the time part in your query and try it. If you still have issue, publish the query you have and I could help|||gives me inaccurate results and date column on select shows wrong data's.
possible unique data is agent id and date (wihout time messed), if i join with agent id then it will filter out table 2
Note.. There's some days agent wont escalate anycall and so therefore no record on table2, but has record on table1.
i want a query that will still show 0 on TOTALESCA column even if agent didnt escalate any call on that day.
Begining to think this is not possible with query
any idea?
eg below
TOTALCALL , TOTALESCA , AGENTID , DATE
30, 0 , IDME1 , 10/28/2004
SELECT table1.TOTALCALL, table2.TOTALESCA,
table2.[agent Id],
datepart(day,table1.Date)
FROM dbo.totalcall table1,
dbo.totalEsc table2
WHERE datepart(day,table1.Date)=datepart(day,table2.Date )|||I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||pat your query work like charm
but i get this message too
Null value is eliminated by an aggregate or other SET operation.
what does that means?|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.
-PatP|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.
-PatP|||pat check your pm|||From the PM, I got:hey pat,
can you help me join this query with another table? the query i got from you yesterday.
there's one more table that has agent name, agent id, supervisor and manager. agent id is unique. i want the same result on this query but now to show agent name, agent id, supervisor and manager. let's say this is table3 and has this columns
agent_ID NVARCHAR(20),
SUP_LAST NVARCHAR(25),
SUP_FRST NVARCHAR(25),
MGR_LAST NVARCHAR(255),
MGR_FRST NVARCHAR(255)
i will love to concenate like
EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS ManagerThere was also some informaiton that pointed back into this thread too. Moving on, I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||To eliminate the error do either:
set ansi_warnings off
or
, Sum(isnull(TOTALCALL, 0)) AS DAY_CALLS
, Sum(isnull(TOTALESCA, 0)) AS DAY_ESCA|||thanks
i had this error
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
but i fixed it when i added all column to groupby
rdjabarov
set ansi_warnings off works but i want to use the query to create view.
where should i put it
im getting error when i try
ALTER VIEW myview
as
set ansi_warnings off
SELECT...
or
ALTER VIEW myview
set ansi_warnings off
as
SELECT ...|||I would recommend that you avoid changing settings to suppress messages. That has always been a receipe for disaster for me. You could also suppress them using:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(Coalesce(TOTALCALL, 0)) AS DAY_CALLS
, Sum(Coalesce(TOTALESCA, 0)) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, EMP_FRST, EMP_LAST, SUP_FRST, SUP_LAST, MGR_FRST, MGR_LAST-PatP|||thanks pat
that works without error|||The setting needs to be set on the connection that is used to create an object, so it needs to preceed the CREATE statement.
Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!|||Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!True, but what I meant was that changing settings (of any kind, any where) to make warning messages go away has always proved to be a disaster for me. I didn't mean that you ought to rely on default settings, I meant that changing settings to suppress messages was a receipe for disaster.
I always try find the underlying source of the problem, and correct it or code to ignore the meassage instead of finding ways to suppress the message.
-PatP|||That's why I gave 2 options, SET and ISNULL (which you changed to Coalesce).
just a select query joing these two tables..Seems like the date field is given me problem
i want result like below
TOTALCALL , TOTALESCA , AGENTID , DATE
50 , 5 , IDME1 , 10/28/2004 12:28:00 PM
TOTALESCA shows NuMbers of escalated calls out of totalcalls
table 1
TOTALCALL
AGENTID
DATE
TABLE 2
TOTALESCA
AGENTID
DATE
SAMPLE DATA ON TABLE 1
TOTALCALL, AGENTID, DATE
50 , IDME1 , 10/28/2004 12:28:00 PM
SAMPLE DATA ON TABLE 2
TOTALESCA, AGENTID , DATE
5 , IDME1 , 10/28/2004 12:28:00 PMTry datepart function to ignore the time part in your query and try it. If you still have issue, publish the query you have and I could help|||gives me inaccurate results and date column on select shows wrong data's.
possible unique data is agent id and date (wihout time messed), if i join with agent id then it will filter out table 2
Note.. There's some days agent wont escalate anycall and so therefore no record on table2, but has record on table1.
i want a query that will still show 0 on TOTALESCA column even if agent didnt escalate any call on that day.
Begining to think this is not possible with query
any idea?
eg below
TOTALCALL , TOTALESCA , AGENTID , DATE
30, 0 , IDME1 , 10/28/2004
SELECT table1.TOTALCALL, table2.TOTALESCA,
table2.[agent Id],
datepart(day,table1.Date)
FROM dbo.totalcall table1,
dbo.totalEsc table2
WHERE datepart(day,table1.Date)=datepart(day,table2.Date )|||I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||pat your query work like charm
but i get this message too
Null value is eliminated by an aggregate or other SET operation.
what does that means?|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.
-PatP|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.
-PatP|||pat check your pm|||From the PM, I got:hey pat,
can you help me join this query with another table? the query i got from you yesterday.
there's one more table that has agent name, agent id, supervisor and manager. agent id is unique. i want the same result on this query but now to show agent name, agent id, supervisor and manager. let's say this is table3 and has this columns
agent_ID NVARCHAR(20),
SUP_LAST NVARCHAR(25),
SUP_FRST NVARCHAR(25),
MGR_LAST NVARCHAR(255),
MGR_FRST NVARCHAR(255)
i will love to concenate like
EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS ManagerThere was also some informaiton that pointed back into this thread too. Moving on, I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||To eliminate the error do either:
set ansi_warnings off
or
, Sum(isnull(TOTALCALL, 0)) AS DAY_CALLS
, Sum(isnull(TOTALESCA, 0)) AS DAY_ESCA|||thanks
i had this error
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
but i fixed it when i added all column to groupby
rdjabarov
set ansi_warnings off works but i want to use the query to create view.
where should i put it
im getting error when i try
ALTER VIEW myview
as
set ansi_warnings off
SELECT...
or
ALTER VIEW myview
set ansi_warnings off
as
SELECT ...|||I would recommend that you avoid changing settings to suppress messages. That has always been a receipe for disaster for me. You could also suppress them using:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(Coalesce(TOTALCALL, 0)) AS DAY_CALLS
, Sum(Coalesce(TOTALESCA, 0)) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, EMP_FRST, EMP_LAST, SUP_FRST, SUP_LAST, MGR_FRST, MGR_LAST-PatP|||thanks pat
that works without error|||The setting needs to be set on the connection that is used to create an object, so it needs to preceed the CREATE statement.
Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!|||Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!True, but what I meant was that changing settings (of any kind, any where) to make warning messages go away has always proved to be a disaster for me. I didn't mean that you ought to rely on default settings, I meant that changing settings to suppress messages was a receipe for disaster.
I always try find the underlying source of the problem, and correct it or code to ignore the meassage instead of finding ways to suppress the message.
-PatP|||That's why I gave 2 options, SET and ISNULL (which you changed to Coalesce).
Subscribe to:
Posts (Atom)