about a WHERE

Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.If you want daily totals per product for the given data, try:
select
convert (datetime, convert (char (8), SaleDate, 112), 112) as SaleDate
, Product
, sum (Price) as Total
from
Sales
group by
convert (datetime, convert (char (8), SaleDate, 112), 112)
, Product
order by
SaleDate
, Product
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
Hi, I think this is an easy stuff but not for me.. I have an application
running on a production server.
In my DB I have a Sales Table like this
SaleDate Product Price$
2005-02-18 00:00:00.000 1 500
2005-02-18 00:00:00.000 1 100
2005-02-18 00:00:00.000 3 200
Using SP I'm getting a total by Date by product (e.g For SaleDate =
2005-02-18 Product 1 = 600, Product 3 = 200)
Now also I need to Store the times like this
SaleDate Product Price$
2005-02-18 09:37:39.000 1 500
2005-02-18 09:30:09.000 1 100
2005-02-18 14:20:10.000 3 200
How should I modify my SP to get the same..I tried this:
Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
but it does nothing, the SP return a total for each row
thks.|||This should answer your question..
given that the SaleDate is a datetime (you have seconds in your second
snippet)
DECLARE @.Date datetime
SET @.Date = '20050218' -- no seconds in here
... WHERE SaleDate>=@.Date AND SaleDate<@.Date+1
-- note the >= on LHS and the < on RHS to prevent overlaps
If possible, dont cast the column in your table to compare it as that
precludes the optimiser from using an index s (I hate it when the
optimiser does implicit casts on the column rather than the variable).
Mr Tea
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:7D87C046-13C6-4DE7-8932-4BC5BE369155@.microsoft.com...
> Hi, I think this is an easy stuff but not for me.. I have an application
> running on a production server.
> In my DB I have a Sales Table like this
> SaleDate Product Price$
> 2005-02-18 00:00:00.000 1 500
> 2005-02-18 00:00:00.000 1 100
> 2005-02-18 00:00:00.000 3 200
> Using SP I'm getting a total by Date by product (e.g For SaleDate =
> 2005-02-18 Product 1 = 600, Product 3 = 200)
> Now also I need to Store the times like this
> SaleDate Product Price$
> 2005-02-18 09:37:39.000 1 500
> 2005-02-18 09:30:09.000 1 100
> 2005-02-18 14:20:10.000 3 200
> How should I modify my SP to get the same..I tried this:
> Where CONVERT(CHAR(10),SaleDate ,112) = ''' +
> CONVERT(CHAR(10),@.ParameterDateIn,112) + ''''
> but it does nothing, the SP return a total for each row
> thks.
>
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
>>From sys.sysdatabases
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.
NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegr oups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>
|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End
|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegr oups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/U...2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable'
)
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTab
le'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>sql

A, B and C are the same but at least D or E are different (was "noob question")

hi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DBhi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DB

is this wat u wanted ?
Select A,B,C,D,E From Table1 Where Exists (Select 1 from Table1 Where (A=B and B=C) and (( A <>E) or (A <>D)))

Gurus, please comment|||I'm afraid your query won't work :o

Firstly, Exists isn't necessary. Secondly, if the condition is true for any of the rows it will display all the rows (the exists statement is not corrolated with the main query). Minor but SELECT * is recommended within an exists statement as the optimser will then chose the best index for the job (doubt it would make much difference here but I like to use it by habit).

Nearly there - a slight reworking:
Select A,B,C,D,E
From Table1
Where A=B and B=C and (A <> E or A <> D)|||Select A,B,C,D,E From Test
Where A=B
and A=C
and ((A=D or A=E)
or D<>E)

hope this works for u.|||Thanks for the suggessions|||Here's some NZDF code that will do what you've asked for:CREATE TABLE g11DB (
A INT
, B INT
, C INT
, D INT
, E INT
)

INSERT INTO g11DB (
A, B, C, D, E)
SELECT 1, 2, 3, 4, 5 UNION ALL
SELECT 1, 1, 1, 4, 5 UNION ALL
SELECT 1, 1, 1, 1, 5 UNION ALL
SELECT 1, 1, 1, 4, 1 UNION ALL
SELECT 1, 2, 1, 4, 1

SELECT *
FROM g11DB
WHERE 0 = A - B
AND A = C
AND A != D
UNION SELECT *
FROM g11DB
WHERE A = B
AND -A = -C
AND 0 != (A - E)-PatP|||Always the skeptic, Pat.|||Thanks guys but it seems i didn't properly state what I wanted... The code I was looking for is something like this (although i'm still wondering if there's some more efficient version...
SELECT IPA.*
FROM dbo.IRS_PAYMENT IPA INNER JOIN
dbo.IRS_PAYMENT IPB ON IPA.ip_employee_id = IPB.ip_employee_id
AND IPA.ip_paydate = IPB.ip_paydate
AND IPA.ip_pay_indicator = IPB.ip_pay_indicator
AND (NOT (IPA.ip_total_tax = IPB.ip_total_tax)
OR NOT (IPA.ip_net_pay = IPA.ip_net_pay))
ORDER BY IPA.ip_employee_id

what's NSDF stand for btw? :)|||How about:SELECT A.*
FROM dbo.IRS_PAYMENT AS A
INNER JOIN dbo.IRS_PAYMENT AS B
ON (B.ip_employee_id = A.ip_employee_id
AND B.ip_paydate = A.ip_paydate
AND B.ip_pay_indicator = A.ip_pay_indicator)
WHERE B.ip_total_tax != A.ip_total_tax
OR B.ip_net_pay != A.ip_net_pay
ORDER BY A.ip_employee_id, A.ip_paydate, A.ip_pay_indicator-PatP

a webpage that directly inputs data to a specific table in an sql database ?

im new to sql and ive created a test data base with a test table having 3 input fields i was wondering if it was possible to use a webpage to input data into the database in a specific table
ive seen some net articles discussing this but as i said im new they talked about using an asp page,so i was wondering if someone could help me out with a simple code to enable a webpage to input data on an sql table
so how do i go about it ?
test_database
dbo.test_table
the 3 input fields are name age gender

ive also set an odbc for the data base (test_database) set at native

tnx

Quote:

Originally Posted by arawsg

im new to sql and ive created a test data base with a test table having 3 input fields i was wondering if it was possible to use a webpage to input data into the database in a specific table
ive seen some net articles discussing this but as i said im new they talked about using an asp page,so i was wondering if someone could help me out with a simple code to enable a webpage to input data on an sql table
so how do i go about it ?
test_database
dbo.test_table
the 3 input fields are name age gender

ive also set an odbc for the data base (test_database) set at native

tnx

Hi ,

I don't know which platform you are using to develop your webpage.I give you sample code here to insert data into a sql server database table from an asp.net web page. I think this will help you.

---------
Using System.Data.SqlClient
void Page_Load()
{
SqlConnection con=new SqlConnection ("server=labserver;database=testdb;username=sa;passw ord=s");
SqlCommand cmd=new SqlCommand("insert into emptable(empid,empname) values(2312,'Adam')",con);
con.open();
cmd.ExecuteNonQuery();
con.close();
}
----------|||

Quote:

Originally Posted by balajiu

Hi ,

I don't know which platform you are using to develop your webpage.I give you sample code here to insert data into a sql server database table from an asp.net web page. I think this will help you.

---------
Using System.Data.SqlClient
void Page_Load()
{
SqlConnection con=new SqlConnection ("server=labserver;database=testdb;username=sa;passw ord=s");
SqlCommand cmd=new SqlCommand("insert into emptable(empid,empname) values(2312,'Adam')",con);
con.open();
cmd.ExecuteNonQuery();
con.close();
}
----------

tnx ill try your code out

A way to reference the value in the current cell, not by name?

Is there any way to reference the value in the current table cell (Textbox)
from within formulas in that cell? I am curious because there are many places,
like 'Visiblity' where I would like to set it based on the current value,
but need to duplicate the formula I use for "Value". I would rather not
reference the name of the cell (like textbox4, etc), because that ALSO does
not change if I rename the cell, leaving yet another dangling reference.
I have many forumlas in the 'Visiblity->Expression' field that look like
this :
=Fields!with_xxxx_value.Value < 1
When I would like to have this (or something to that effect)
= CurrentObj.Value < 1, where CurrentObj would = the textbox, and Value
would = the "Value" expression for it.
Thanks,
// Andrew
P.S. Why does "Visibility->Expression:" seem to have the opposite effect?
If I want to HIDE a field when the value is < 0, then I have to set the
formula to "= Fields!the_value.Value < 1", which of course evaluates to TRUE
when I want to set Visible to False. Verrrrry funny.Hey Andrew,
I haven't actually tried this, but you the "ME" keyword might be what
your looking for i.e. Me.value
Michael
"Andrew Backer" wrote:
> Is there any way to reference the value in the current table cell (Textbox)
> from within formulas in that cell? I am curious because there are many places,
> like 'Visiblity' where I would like to set it based on the current value,
> but need to duplicate the formula I use for "Value". I would rather not
> reference the name of the cell (like textbox4, etc), because that ALSO does
> not change if I rename the cell, leaving yet another dangling reference.
> I have many forumlas in the 'Visiblity->Expression' field that look like
> this :
> =Fields!with_xxxx_value.Value < 1
> When I would like to have this (or something to that effect)
> = CurrentObj.Value < 1, where CurrentObj would = the textbox, and Value
> would = the "Value" expression for it.
> Thanks,
> // Andrew
> P.S. Why does "Visibility->Expression:" seem to have the opposite effect?
> If I want to HIDE a field when the value is < 0, then I have to set the
> formula to "= Fields!the_value.Value < 1", which of course evaluates to TRUE
> when I want to set Visible to False. Verrrrry funny.
>
>

A way to recursively look up hierarchal data using a lookup table

I have found the Common Table Expressions described in SQL 2005 and I
am not sure if it applies to this situation.

Here are the tables

<PRE>
<B>ManagedServer Table</B>
--IdManagedServer (PK, int, not Null)
--Name (nvarchar(256), not null)

<B>ManagedServerToManagedServer Table</B>
--IdParentManagedServer (PK, int, not null)
--IdChildManagedServer (PK, int, not null)
</PRE
The following will give you the parent

-- Get Managed Server Group Names
LEFT OUTER JOIN ManagedServerToManagedServer mstms ON
ms.IdManagedServer = mstms.IdChildManagedServer
LEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =
msg.IdManagedServer

How would you go about getting all of the "parents" in the tree?
Can this be done with CTEs? Unfortuately all of the examples found are
joining on itself.(patuww@.yahoo.com) writes:
> I have found the Common Table Expressions described in SQL 2005 and I
> am not sure if it applies to this situation.
> Here are the tables
><PRE>
><B>ManagedServer Table</B>
> --IdManagedServer (PK, int, not Null)
> --Name (nvarchar(256), not null)
><B>ManagedServerToManagedServer Table</B>
> --IdParentManagedServer (PK, int, not null)
> --IdChildManagedServer (PK, int, not null)
></PRE>
> The following will give you the parent
> -- Get Managed Server Group Names
> LEFT OUTER JOIN ManagedServerToManagedServer mstms ON
> ms.IdManagedServer = mstms.IdChildManagedServer
> LEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =
> msg.IdManagedServer
> How would you go about getting all of the "parents" in the tree?
> Can this be done with CTEs? Unfortuately all of the examples found are
> joining on itself.

For this type of query, it is also a good idea to post:

o CREATE TABLE statement(s) for the involved table(s).
o INSERT statement with sample data.
o The desired output given the sample.

This makes it easy to copy and paste and post a tested solution.

Judging from the table design, it appears that a child can have many
parents, which makes sort of interesting for the output.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

a way to have more than 8060 bytes per row?

Having a table with 5 columns that are varchar(2000).
This exceeds the maximum bytes per row and will fail
whenever someone adds more than 8060bytes in these columns.
Client whines about this and cant believe his eyes cause
he cant understand this since the maximum per column is
set to 8000 and how come the maximum is set to 8060 for
the whole row?
Anyways, do anyone of you guys out there have a workaround
for this problem or should i just tell the client to
rethink his model..
/RisunSorry, you will have to rethink your model - split the big table into two
smaller with one-to-one relation.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Risun" <risun@.wmdata.com> wrote in message
news:032401c37b69$e97cab30$a001280a@.phx.gbl...
> Having a table with 5 columns that are varchar(2000).
> This exceeds the maximum bytes per row and will fail
> whenever someone adds more than 8060bytes in these columns.
> Client whines about this and cant believe his eyes cause
> he cant understand this since the maximum per column is
> set to 8000 and how come the maximum is set to 8060 for
> the whole row?
> Anyways, do anyone of you guys out there have a workaround
> for this problem or should i just tell the client to
> rethink his model..
> /Risunsql

A way to get Table shema as xml ?

Dear all,
Is there a way to get from SQL table and XML shema file (XSD) that can be
read afterwards from a .NEt application ?
I know that I could read frommy ASP.NET code the whole table structure but
having the local xsd file would be faster for reading
regards
serge
Hello serge,

> Is there a way to get from SQL table and XML shema file (XSD) that can
> be read afterwards from a .NEt application ?
> I know that I could read frommy ASP.NET code the whole table structure
> but having the local xsd file would be faster for reading
AFAIK, not directly. One of the things I've done in the past is generate
information about the schema from the metadata. Something like this:
alter function dbo.GetColumnsForTable(@.TableObjectID int)
returns xml
as begin
declare @.rv xml
set @.rv = (select
c.column_id'@.position'
, c.name'name'
, y.name'dataType'
, c.max_length'maxLength'
, c.precision'precision'
, c.scale'scale'
, c.collation_name'collationName'
, c.is_nullable'nullable'
, c.is_rowguidcol'isRowGUID'
, c.is_identity'isIdentity'
, c.is_computed'isComputed'
, x.name
from sys.columns c
join sys.types y on c.system_type_id = y.system_type_id
left join sys.xml_schema_collections x on c.xml_collection_id = x.xml_collection_id
where c.object_id = @.TableObjectID
for xml path('column'),type)
return @.rv
end
go
select t.name'name',
dbo.GetColumnsForTable(t.object_id) as 'table/columns'
from sys.tables t
for xml path('table'),root('tables')
go
While its not a schema per se, you can get a lot of information doing this
kind of coding.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

A way to get Table shema as xml ?

Dear all,
Is there a way to get from SQL table and XML shema file (XSD) that can be
read afterwards from a .NEt application ?
I know that I could read frommy ASP.NET code the whole table structure but
having the local xsd file would be faster for reading
regards
sergeHello serge,

> Is there a way to get from SQL table and XML shema file (XSD) that can
> be read afterwards from a .NEt application ?
> I know that I could read frommy ASP.NET code the whole table structure
> but having the local xsd file would be faster for reading
AFAIK, not directly. One of the things I've done in the past is generate
information about the schema from the metadata. Something like this:
alter function dbo.GetColumnsForTable(@.TableObjectID int)
returns xml
as begin
declare @.rv xml
set @.rv = (select
c.column_id '@.position'
, c.name 'name'
, y.name 'dataType'
, c.max_length 'maxLength'
, c.precision 'precision'
, c.scale 'scale'
, c.collation_name 'collationName'
, c.is_nullable 'nullable'
, c.is_rowguidcol 'isRowGUID'
, c.is_identity 'isIdentity'
, c.is_computed 'isComputed'
, x.name
from sys.columns c
join sys.types y on c.system_type_id = y.system_type_id
left join sys.xml_schema_collections x on c.xml_collection_id = x.xml_collec
tion_id
where c.object_id = @.TableObjectID
for xml path('column'),type)
return @.rv
end
go
select t.name 'name',
dbo.GetColumnsForTable(t.object_id) as 'table/columns'
from sys.tables t
for xml path('table'),root('tables')
go
While its not a schema per se, you can get a lot of information doing this
kind of coding.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

a way to export data from table to flat file

Hi there!
I know bulk insert for importing data into tables and bcp for both
importing, exporting data between tables..from flat files...ok..
But is there any way to export data using a SQL statement and a format
file (need to export in kind of csv) just like bcp ? a kind of *bulk
output*...:)
any idea'
thanks a lot
++
Vincelook at DTS...|||Vince
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=D:\FolderName;','select * from Text1.txt')
"Vince .>" <vincent@.<remove> wrote in message
news:ro7751hjpvc9l0lks58pds2dda1prok8lu@.
4ax.com...
> Hi there!
> I know bulk insert for importing data into tables and bcp for both
> importing, exporting data between tables..from flat files...ok..
> But is there any way to export data using a SQL statement and a format
> file (need to export in kind of csv) just like bcp ? a kind of *bulk
> output*...:)
> any idea'
> thanks a lot
> ++
> Vince
>

A way to "SELECT constraints FROM table"

is there any way to get the contraints or indexes of a table doing a SELECT QUERY?

I am using a VC++ application to do some changes on a Access DB and there is a contraint that as a random name... This would help me to find the constraint name automaticaly and change drop it as I want

hope someone knows a way to do thisSELECT * FROM MSysRelationships-PatP|||Thank you very very very much... you don't know how much did you help me :)

Finally someone gave me a good answer to my problem and it WORKS :D

A view that include a table from a database in another sql server

I need to create a view that contain a table from a database that reside in
another sql server.
The view is a simple select statement joining two tables; i.e. table A inner
joining table B that resides in different server. I hope this make sense.
I thank you in advance for any recommendations.
JamesCreate a linked server to the SQL Server containing table B or replicate the
data from table B to another table locally for the join.
HTH
Jerry
"jamesww7" <jamesww7@.discussions.microsoft.com> wrote in message
news:30796887-26D4-44E1-B3CF-AA4EB31C2894@.microsoft.com...
>I need to create a view that contain a table from a database that reside in
> another sql server.
> The view is a simple select statement joining two tables; i.e. table A
> inner
> joining table B that resides in different server. I hope this make sense.
> I thank you in advance for any recommendations.
> --
> James

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

A view of two tables

Hi I am trying to create a view from two tables.

Table 1 Sales

Cust_ID | Name | Genre | Sales Person | Last Order |
-
A123 | John | Fiction | Bill | 543A |
A123 | John | Sci-Fi | Bill | 534G |
B432 | Mark | Music | Ted | 748H |
C991 | Kevin | Sci-Fi | Bob | 017S |
C991 | Kevin | Classics | Bob | 663H |
C991 | Kevin | Fiction | Bob | 882G |
D912 | Syd | Music | Ted | 917F |
G941 | Paul | Sci-Fi | Bill | 991C |
G941 | Paul | Music | Bill | 947D |

Each customer will only have one record for each Genre.

Table 2 Acc_holders

Cust_ID | Name | Account No | Balance |
-
A123 | John | ABT110234 | 12.34 |
B432 | Mark | ADE145521 | 53.32 |
C991 | Kevin | NDU11E234 | 55.90 |
F723 | Andy | GGE124349 | 22.60 |
H882 | Sammy | NJW310264 | 12.99 |
I731 | Jane | HAT219845 | 55.23 |

cUST_ID is unique in this table.
A customer may be in either one or both tables

I am looking to create a view that will contain the following

Cust ID | Name | Has Account | Balance | Sci-Fi | Fiction | Music | Classics |

A123 | John | Y | 12.34 | Y | Y | N | N |
B432 | Mark | Y | 53.32 | N | N | Y | N |
C991 | Kevin | Y | 55.90 | Y | Y | N | N |
D912 | Syd | N | NULL | Y | Y | N | Y |
F723 | Andy | Y | 22.60 | N | N | Y | N |
G941 | Paul | N | NULL | N | N | N | N |
H882 | Sammy | Y | 12.99 | N | N | N | N |
I731 | Jane | Y | 55.23 | N | N | N | N |

Ok so I am trying to figure out how I can create a summary view that contains all my customers from both tables.
I need a single record for each customer and for it to show a balance and if they have a account from tabel 2 and if there are any genres from table 1

so far I have

SELECT DISTINCT
TOP (100) PERCENT dbo.SALES.CUST_ID,
dbo.SALES.Name
FROM dbo.SALES FULL OUTER JOIN
dbo.Acc_holders ON dbo.SALES.CUST_ID, = dbo.Acc_holders.CUST_ID, AND dbo.SALES.Name = dbo.Acc_holders.Name
ORDER BY dbo.SALES.CUST_ID

This gives me the first two columns but I can't figure out how to do the rest.

Any help would be very much appreciated.

Cheers.

The example below returns the results that I think you are expecting (in accordance with your source data).

Incidentally, if you are intending to use the code inside a View then it is not recommended to include an ORDER BY clause within the View, as you displayed in your example. If ordering of the results is required then you should use ORDER BY when SELECTing from the View instead.

Chris

DECLARE @.Sales TABLE

(

Cust_ID CHAR(4) NOT NULL,

[Name] VARCHAR(100) NOT NULL,

[Genre] VARCHAR(20) NOT NULL,

[Sales Person] VARCHAR(20),

[Last Order] CHAR(4) NOT NULL

)

DECLARE @.Acc_holders TABLE

(

Cust_ID CHAR(4) NOT NULL PRIMARY KEY,

[Name] VARCHAR(100) NOT NULL,

[Account No] CHAR(9) NOT NULL,

[Balance] MONEY NOT NULL

)

INSERT INTO @.Sales

SELECT 'A123','John','Fiction','Bill','543A' UNION

SELECT 'A123','John','Sci-Fi','Bill','534G' UNION

SELECT 'B432','Mark','Music','Ted','748H' UNION

SELECT 'C991','Kevin','Sci-Fi','Bob','017S' UNION

SELECT 'C991','Kevin','Classics','Bob','663H' UNION

SELECT 'C991','Kevin','Fiction','Bob','882G' UNION

SELECT 'D912','Syd','Music','Ted','917F' UNION

SELECT 'G941','Paul','Sci-Fi','Bill','991C' UNION

SELECT 'G941','Paul','Music','Bill','947D'

INSERT INTO @.Acc_holders

SELECT 'A123','John','ABT110234',12.34 UNION

SELECT 'B432','Mark','ADE145521',53.32 UNION

SELECT 'C991','Kevin','NDU11E234',55.9 UNION

SELECT 'F723','Andy','GGE124349',22.6 UNION

SELECT 'H882','Sammy','NJW310264',12.99 UNION

SELECT 'I731','Jane','HAT219845',55.23

SELECT t.Cust_ID,

t.[Name],

CASE WHEN EXISTS (SELECT 1 FROM @.Acc_holders a WHERE a.Cust_ID = t.Cust_ID) THEN 'Y' ELSE 'N' END AS [Has Account],

ac.Balance,

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Sci-Fi') THEN 'Y' ELSE 'N' END AS [Sci-Fi],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Fiction') THEN 'Y' ELSE 'N' END AS [Fiction],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Music') THEN 'Y' ELSE 'N' END AS [Music],

CASE WHEN EXISTS (SELECT 1 FROM @.Sales s WHERE s.Cust_ID = t.Cust_ID AND s.[Genre] = 'Classics') THEN 'Y' ELSE 'N' END AS [Classics]

FROM

(SELECT Cust_ID, [Name]

FROM @.Sales

UNION

SELECT Cust_ID, [Name]

FROM @.Acc_holders) t

LEFT JOIN @.Acc_Holders ac ON ac.Cust_ID = t.Cust_ID

|||

That worked a treat.

Thank you very much!

Kevin.

|||This should be faster

select
Cust_ID = coalesce(s.Cust_ID, a.Cust_ID),
[Name] = coalesce(s.[Name], a.[Name]),
[Has Account] = case when a.Cust_ID is null then 'N' else 'Y' end,
a.Balance,
[Sci-Fi] = coalesce(s.[Sci-Fi], 'N'),
[Fiction] = coalesce(s.[Fiction], 'N'),
[Music] = coalesce(s.[Music], 'N'),
[Classics] = coalesce(s.[Classics], 'N')
from
(
select Cust_ID, [Name],
[Sci-Fi] = max(case when Genre = 'Sci-Fi' then 'Y' else 'N' end),
[Fiction] = max(case when Genre = 'Fiction' then 'Y' else 'N' end),
[Music] = max(case when Genre = 'Music' then 'Y' else 'N' end),
[Classics] = max(case when Genre = 'Classics' then 'Y' else 'N' end)
from @.Sales
group by Cust_ID, [Name]
) s full outer join @.Acc_Holders a
on s.Cust_ID = a.Cust_ID|||

Thanks,

I have it working for now but when it comes to optimising the code I will give it a go.

Cheers,

Kevin.

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an author.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaking
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply grateful
Kindest Regards
Simon
Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.
|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.
|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
>
>
|||Hello David,

> Simon Harvey wrote:
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an auth
or.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaki
ng
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply gratefu
l
Kindest Regards
SimonHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
>
>|||Hello David,

> Simon Harvey wrote:
>
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an auth
or.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaki
ng
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply gratefu
l
Kindest Regards
SimonHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
>
>|||Hello David,

> Simon Harvey wrote:
>
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon

A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an author.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaking
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply grateful
Kindest Regards
SimoHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I know
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID may
> be null.
> Now, I need to Select all the books, including those that don't have an author.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books that
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a sneaking
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grateful
> Kindest Regards
> Simon
>
>