Hi,
Correlated subquery is written in the following syntax in Oracle.
Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) from
Emplyee where sal>=X.sal)
How can this be written in SQL Server?
Please throw some light.
Thanks,
Su ManIn SQL server you use HAVING clause. For more information and the syntx
please refer to Books Online available.
thanks and regards
Chandra
"Su Man" wrote:
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) fro
m
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>
>|||Your query is valid in SQL server also
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Su Man" <subu501@.yahoo.com> wrote in message
news:d4sl39$eqi$1@.news.mch.sbs.de...
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal))
> from
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>|||The query you posted is valid in SQL Server. This is Standard SQL syntax.
Did you try it?
David Portas
SQL Server MVP
--|||It is working. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gKOdneGg3IlLQOzfRVn-tA@.giganews.com...
> The query you posted is valid in SQL Server. This is Standard SQL syntax.
> Did you try it?
> --
> David Portas
> SQL Server MVP
> --
>
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Thursday, March 29, 2012
Correlated SUB Queries?
I would like to combine the following 3 select statements:
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoiti'm not sure where the correlation comes in
these are uncorrelated subqueries --select SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID = '101' ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if these really should be correlated, then you can add a WHERE condition to each subquery that references one of the outer tables (D_SECTION or D_REGULATION)|||to clarify
these are not correlated subqueries because the inner query does not reference the outer query with an table alias.|||however, thats exactly what i need to do. In this section:
select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.|||I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.yeah, that was the missing info, wasn't itselect SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID
= D_SECTION.REG_SURR_ID ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if that's not the right correlation, at least now you know how to do it
;) :)|||Hi, Thanks for the answer but im still a bit confused on that same section.
The thing is that I have to find the current inspection number to see if there were any VIOLATIONS (if there was 1 or more VIOLATIONS!!, then its not compliant, otherwise it is considered compliant).
NOTICE the alais currSecID I created in order to make a link between the current SECTION_ID (not regulation_ID) and the one used in the VIOLATIONS Compliant section (above)
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID,
D_SECTION.SECTION_SURR_ID currSecID,
( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections,
case when
( select count(*)
from F_VIOLATIONS
where SECTION_SURR_ID = currSecID) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')|||don't use the alias in the subquery
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoiti'm not sure where the correlation comes in
these are uncorrelated subqueries --select SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID = '101' ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if these really should be correlated, then you can add a WHERE condition to each subquery that references one of the outer tables (D_SECTION or D_REGULATION)|||to clarify
these are not correlated subqueries because the inner query does not reference the outer query with an table alias.|||however, thats exactly what i need to do. In this section:
select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.|||I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.yeah, that was the missing info, wasn't itselect SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID
= D_SECTION.REG_SURR_ID ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if that's not the right correlation, at least now you know how to do it
;) :)|||Hi, Thanks for the answer but im still a bit confused on that same section.
The thing is that I have to find the current inspection number to see if there were any VIOLATIONS (if there was 1 or more VIOLATIONS!!, then its not compliant, otherwise it is considered compliant).
NOTICE the alais currSecID I created in order to make a link between the current SECTION_ID (not regulation_ID) and the one used in the VIOLATIONS Compliant section (above)
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID,
D_SECTION.SECTION_SURR_ID currSecID,
( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections,
case when
( select count(*)
from F_VIOLATIONS
where SECTION_SURR_ID = currSecID) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')|||don't use the alias in the subquery
Correlated SUB Queries?
I would like to combine the following 3 select statements:
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoitplease don't cross-post
http://www.dbforums.com/showthread.php?t=1117027sql
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoitplease don't cross-post
http://www.dbforums.com/showthread.php?t=1117027sql
Correlated query in SELECT clause
There is a problem when I include correlated query in the SELECT clause in
the SQL statement like the following:
SELECT a.column1, (
SELECT b.column2
FROM b
WHERE b.a_id = a.a_id
) as dummy
FROM a;
(just replace a and b with any related table names from your database)
This query executed without any problems in query designer and in a report.
But when executed from inside report designer (Microsoft Developer
Environment, Data tab) I got the following error:
The column prefix 'a' doesn't match with a table name or alias name used in
the query.
Is it some kind of bug or just 'feature' of Query Designer?Try taking out all carraige returns.
VA wrote:
>There is a problem when I include correlated query in the SELECT clause in
>the SQL statement like the following:
>SELECT a.column1, (
> SELECT b.column2
> FROM b
> WHERE b.a_id = a.a_id
>) as dummy
>FROM a;
>(just replace a and b with any related table names from your database)
>This query executed without any problems in query designer and in a report.
>But when executed from inside report designer (Microsoft Developer
>Environment, Data tab) I got the following error:
>The column prefix 'a' doesn't match with a table name or alias name used in
>the query.
>Is it some kind of bug or just 'feature' of Query Designer?
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Does it work if you make it a VIEW, and do SELECT * FROM TheView in
your frontend?
the SQL statement like the following:
SELECT a.column1, (
SELECT b.column2
FROM b
WHERE b.a_id = a.a_id
) as dummy
FROM a;
(just replace a and b with any related table names from your database)
This query executed without any problems in query designer and in a report.
But when executed from inside report designer (Microsoft Developer
Environment, Data tab) I got the following error:
The column prefix 'a' doesn't match with a table name or alias name used in
the query.
Is it some kind of bug or just 'feature' of Query Designer?Try taking out all carraige returns.
VA wrote:
>There is a problem when I include correlated query in the SELECT clause in
>the SQL statement like the following:
>SELECT a.column1, (
> SELECT b.column2
> FROM b
> WHERE b.a_id = a.a_id
>) as dummy
>FROM a;
>(just replace a and b with any related table names from your database)
>This query executed without any problems in query designer and in a report.
>But when executed from inside report designer (Microsoft Developer
>Environment, Data tab) I got the following error:
>The column prefix 'a' doesn't match with a table name or alias name used in
>the query.
>Is it some kind of bug or just 'feature' of Query Designer?
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Does it work if you make it a VIEW, and do SELECT * FROM TheView in
your frontend?
correct way to backup?
hi,
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"rtn" wrote:
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a file
s
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it back
up
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new databas
e
> (empty) by the same name in a different machine and then restore it from t
he
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:
> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get
.
> Document the process and any extra recovery steps you figure out. You don
't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>
>|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...[vbcol=seagreen]
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
>|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:
> Look up sp_change_users_login in BOL to relink users and logins. The user
s
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environme
nt
> to replace a failed server? That is what you have to document. Restoring
a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
>
>
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"rtn" wrote:
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a file
s
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it back
up
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new databas
e
> (empty) by the same name in a different machine and then restore it from t
he
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:
> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get
.
> Document the process and any extra recovery steps you figure out. You don
't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>
>|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...[vbcol=seagreen]
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
>|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:
> Look up sp_change_users_login in BOL to relink users and logins. The user
s
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environme
nt
> to replace a failed server? That is what you have to document. Restoring
a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
>
>
correct way to backup?
hi,
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"rtn" wrote:
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new database
> (empty) by the same name in a different machine and then restore it from the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:
> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get.
> Document the process and any extra recovery steps you figure out. You don't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> > hi,
> >
> > to backup my sql server 2000 databases, I right click on the database name
> > (from enterprise mgr),select the backup database option, choose 'database
> > complete' , and then add a filename as the destination. I backup to a
> > files
> > on the harddisk and then backs up those files to a tape.
> >
> > Is this a good backup process? I mean, the way I am doing it, will it
> > backup
> > the complete database, the users on that database, etc? And will I have
> > problems while restoring? By restoring, I mean I will create a new
> > database
> > (empty) by the same name in a different machine and then restore it from
> > the
> > backup file.Will the users in that database also get restored?
> >
> > Your suggestions are very much appreciated.
> >
> > thanks!!
>
>|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
>> This is a good start. You need to automate the process somehow. The
>> database maintenance wizard offers some help or you can write scripts and
>> use the SQL agent to schedule them. The most important thing is to test
>> your recovery process. Restore the databases somewhere. See what you
>> get.
>> Document the process and any extra recovery steps you figure out. You
>> don't
>> want to be figuring this out under the time crunch of a down system.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "rtn" <rtn@.discussions.microsoft.com> wrote in message
>> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>> > hi,
>> >
>> > to backup my sql server 2000 databases, I right click on the database
>> > name
>> > (from enterprise mgr),select the backup database option, choose
>> > 'database
>> > complete' , and then add a filename as the destination. I backup to a
>> > files
>> > on the harddisk and then backs up those files to a tape.
>> >
>> > Is this a good backup process? I mean, the way I am doing it, will it
>> > backup
>> > the complete database, the users on that database, etc? And will I have
>> > problems while restoring? By restoring, I mean I will create a new
>> > database
>> > (empty) by the same name in a different machine and then restore it
>> > from
>> > the
>> > backup file.Will the users in that database also get restored?
>> >
>> > Your suggestions are very much appreciated.
>> >
>> > thanks!!
>>|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:
> Look up sp_change_users_login in BOL to relink users and logins. The users
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environment
> to replace a failed server? That is what you have to document. Restoring a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
> > thanks for your reply.
> >
> > I tried a test recovery. All goes well, expect the fact that users that
> > are
> > present in the original database does not get restored. I think everything
> > except the users get restored.
> >
> > any suggestions on this would be greatly appreciated.
> >
> > thanks!
> >
> > "Geoff N. Hiten" wrote:
> >
> >> This is a good start. You need to automate the process somehow. The
> >> database maintenance wizard offers some help or you can write scripts and
> >> use the SQL agent to schedule them. The most important thing is to test
> >> your recovery process. Restore the databases somewhere. See what you
> >> get.
> >> Document the process and any extra recovery steps you figure out. You
> >> don't
> >> want to be figuring this out under the time crunch of a down system.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >>
> >> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> >> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> >> > hi,
> >> >
> >> > to backup my sql server 2000 databases, I right click on the database
> >> > name
> >> > (from enterprise mgr),select the backup database option, choose
> >> > 'database
> >> > complete' , and then add a filename as the destination. I backup to a
> >> > files
> >> > on the harddisk and then backs up those files to a tape.
> >> >
> >> > Is this a good backup process? I mean, the way I am doing it, will it
> >> > backup
> >> > the complete database, the users on that database, etc? And will I have
> >> > problems while restoring? By restoring, I mean I will create a new
> >> > database
> >> > (empty) by the same name in a different machine and then restore it
> >> > from
> >> > the
> >> > backup file.Will the users in that database also get restored?
> >> >
> >> > Your suggestions are very much appreciated.
> >> >
> >> > thanks!!
> >>
> >>
> >>
>
>sql
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"rtn" wrote:
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new database
> (empty) by the same name in a different machine and then restore it from the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:
> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get.
> Document the process and any extra recovery steps you figure out. You don't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> > hi,
> >
> > to backup my sql server 2000 databases, I right click on the database name
> > (from enterprise mgr),select the backup database option, choose 'database
> > complete' , and then add a filename as the destination. I backup to a
> > files
> > on the harddisk and then backs up those files to a tape.
> >
> > Is this a good backup process? I mean, the way I am doing it, will it
> > backup
> > the complete database, the users on that database, etc? And will I have
> > problems while restoring? By restoring, I mean I will create a new
> > database
> > (empty) by the same name in a different machine and then restore it from
> > the
> > backup file.Will the users in that database also get restored?
> >
> > Your suggestions are very much appreciated.
> >
> > thanks!!
>
>|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
>> This is a good start. You need to automate the process somehow. The
>> database maintenance wizard offers some help or you can write scripts and
>> use the SQL agent to schedule them. The most important thing is to test
>> your recovery process. Restore the databases somewhere. See what you
>> get.
>> Document the process and any extra recovery steps you figure out. You
>> don't
>> want to be figuring this out under the time crunch of a down system.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "rtn" <rtn@.discussions.microsoft.com> wrote in message
>> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>> > hi,
>> >
>> > to backup my sql server 2000 databases, I right click on the database
>> > name
>> > (from enterprise mgr),select the backup database option, choose
>> > 'database
>> > complete' , and then add a filename as the destination. I backup to a
>> > files
>> > on the harddisk and then backs up those files to a tape.
>> >
>> > Is this a good backup process? I mean, the way I am doing it, will it
>> > backup
>> > the complete database, the users on that database, etc? And will I have
>> > problems while restoring? By restoring, I mean I will create a new
>> > database
>> > (empty) by the same name in a different machine and then restore it
>> > from
>> > the
>> > backup file.Will the users in that database also get restored?
>> >
>> > Your suggestions are very much appreciated.
>> >
>> > thanks!!
>>|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:
> Look up sp_change_users_login in BOL to relink users and logins. The users
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environment
> to replace a failed server? That is what you have to document. Restoring a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
> > thanks for your reply.
> >
> > I tried a test recovery. All goes well, expect the fact that users that
> > are
> > present in the original database does not get restored. I think everything
> > except the users get restored.
> >
> > any suggestions on this would be greatly appreciated.
> >
> > thanks!
> >
> > "Geoff N. Hiten" wrote:
> >
> >> This is a good start. You need to automate the process somehow. The
> >> database maintenance wizard offers some help or you can write scripts and
> >> use the SQL agent to schedule them. The most important thing is to test
> >> your recovery process. Restore the databases somewhere. See what you
> >> get.
> >> Document the process and any extra recovery steps you figure out. You
> >> don't
> >> want to be figuring this out under the time crunch of a down system.
> >>
> >> Geoff N. Hiten
> >> Microsoft SQL Server MVP
> >>
> >> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> >> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> >> > hi,
> >> >
> >> > to backup my sql server 2000 databases, I right click on the database
> >> > name
> >> > (from enterprise mgr),select the backup database option, choose
> >> > 'database
> >> > complete' , and then add a filename as the destination. I backup to a
> >> > files
> >> > on the harddisk and then backs up those files to a tape.
> >> >
> >> > Is this a good backup process? I mean, the way I am doing it, will it
> >> > backup
> >> > the complete database, the users on that database, etc? And will I have
> >> > problems while restoring? By restoring, I mean I will create a new
> >> > database
> >> > (empty) by the same name in a different machine and then restore it
> >> > from
> >> > the
> >> > backup file.Will the users in that database also get restored?
> >> >
> >> > Your suggestions are very much appreciated.
> >> >
> >> > thanks!!
> >>
> >>
> >>
>
>sql
correct way to backup?
hi,
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"rtn" wrote:
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new database
> (empty) by the same name in a different machine and then restore it from the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!
|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!
|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:
> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get.
> Document the process and any extra recovery steps you figure out. You don't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>
>
|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...[vbcol=seagreen]
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:
> Look up sp_change_users_login in BOL to relink users and logins. The users
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environment
> to replace a failed server? That is what you have to document. Restoring a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
>
>
to backup my sql server 2000 databases, I right click on the database name
(from enterprise mgr),select the backup database option, choose 'database
complete' , and then add a filename as the destination. I backup to a files
on the harddisk and then backs up those files to a tape.
Is this a good backup process? I mean, the way I am doing it, will it backup
the complete database, the users on that database, etc? And will I have
problems while restoring? By restoring, I mean I will create a new database
(empty) by the same name in a different machine and then restore it from the
backup file.Will the users in that database also get restored?
Your suggestions are very much appreciated.
thanks!!
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"rtn" wrote:
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new database
> (empty) by the same name in a different machine and then restore it from the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!
|||This is a good start. You need to automate the process somehow. The
database maintenance wizard offers some help or you can write scripts and
use the SQL agent to schedule them. The most important thing is to test
your recovery process. Restore the databases somewhere. See what you get.
Document the process and any extra recovery steps you figure out. You don't
want to be figuring this out under the time crunch of a down system.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
> hi,
> to backup my sql server 2000 databases, I right click on the database name
> (from enterprise mgr),select the backup database option, choose 'database
> complete' , and then add a filename as the destination. I backup to a
> files
> on the harddisk and then backs up those files to a tape.
> Is this a good backup process? I mean, the way I am doing it, will it
> backup
> the complete database, the users on that database, etc? And will I have
> problems while restoring? By restoring, I mean I will create a new
> database
> (empty) by the same name in a different machine and then restore it from
> the
> backup file.Will the users in that database also get restored?
> Your suggestions are very much appreciated.
> thanks!!
|||thanks for your reply.
I tried a test recovery. All goes well, expect the fact that users that are
present in the original database does not get restored. I think everything
except the users get restored.
any suggestions on this would be greatly appreciated.
thanks!
"Geoff N. Hiten" wrote:
> This is a good start. You need to automate the process somehow. The
> database maintenance wizard offers some help or you can write scripts and
> use the SQL agent to schedule them. The most important thing is to test
> your recovery process. Restore the databases somewhere. See what you get.
> Document the process and any extra recovery steps you figure out. You don't
> want to be figuring this out under the time crunch of a down system.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:DF6C6D32-9A0C-4866-9918-79EAFF6DEEB1@.microsoft.com...
>
>
|||Look up sp_change_users_login in BOL to relink users and logins. The users
still exist, it is just the logins they link to are not on the restore
server. That is part of what I meant by documenting the entire recovery
process. What changes do you need to make to the server and the environment
to replace a failed server? That is what you have to document. Restoring a
database is a very small part of the entire recovery process.
Geoff N. Hiten
Microsoft SQL Server MVP
"rtn" <rtn@.discussions.microsoft.com> wrote in message
news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...[vbcol=seagreen]
> thanks for your reply.
> I tried a test recovery. All goes well, expect the fact that users that
> are
> present in the original database does not get restored. I think everything
> except the users get restored.
> any suggestions on this would be greatly appreciated.
> thanks!
> "Geoff N. Hiten" wrote:
|||thanks for the help again. As you said, sp_change_users_login did the job of
linking the SQL users and the logins. I documented the procedure.
two more quick questions.
1. this stored procedure doesn't work with windows users. In addition to sql
users, my databases also have a few windows users. How can I go about
restoring these windows users and their permissions?
2. also, this stored procedure doesn't work for dbo etc. So what can i do
about restoring dbo, etc
thanks again, you have been a major help.
"Geoff N. Hiten" wrote:
> Look up sp_change_users_login in BOL to relink users and logins. The users
> still exist, it is just the logins they link to are not on the restore
> server. That is part of what I meant by documenting the entire recovery
> process. What changes do you need to make to the server and the environment
> to replace a failed server? That is what you have to document. Restoring a
> database is a very small part of the entire recovery process.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "rtn" <rtn@.discussions.microsoft.com> wrote in message
> news:AEE9F493-416C-46FD-A410-6E8473C0C10A@.microsoft.com...
>
>
correct syntax for this select in SQL Server?
This (demo) statement is fine in Access, and so far as I can see, should
be OK in SQL Server.
In SQL Server, you need to provide an alias for the derived table:
<esquel@.sommarskog.sewrote:
>
>In SQL Server, you need to provide an alias for the derived table:
>
SELECT * FROM (SELECT *FROM test) AS x
Thanks to both you and Roy.
--
Jim
a Yorkshire polymoth|||On Sat, 23 Jun 2007 06:32:00 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:
>>
>>In SQL Server, you need to provide an alias for the derived table:
>>
> SELECT * FROM (SELECT *FROM test) AS x
>
And, actually, and for the record, let's just add, that for this to work
on Oracle we have to omit the "as" - which is OK for SQL Server as well.
--
Jim
a Yorkshire polymoth
be OK in SQL Server.
But Enterprise Manager barfs at the final bracket. Can anyone help
please?
select sum(field1) as sum1, sum(field2) as sum2 from
(SELECT * from test where id < 3
union
SELECT * from test where id 2)
In fact, I can reduce it to :-
select * from
(SELECT * from test)
with the same effect - clearly I just need telling :-)
cheers,
Jim
--
Jim
a Yorkshire polymothJim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:
Quote:
Originally Posted by
This (demo) statement is fine in Access, and so far as I can see, should
be OK in SQL Server.
>
But Enterprise Manager barfs at the final bracket. Can anyone help
please?
>
select sum(field1) as sum1, sum(field2) as sum2 from
(SELECT * from test where id < 3
union
SELECT * from test where id 2)
>
In fact, I can reduce it to :-
>
select * from
(SELECT * from test)
>
with the same effect - clearly I just need telling :-)
In SQL Server, you need to provide an alias for the derived table:
SELECT * FROM (SELECT *FROM test) AS x
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Derived tables must be assigned an alias.
select * from
(SELECT * from test) as X
Roy Harvey
Beacon Falls, CT
On Fri, 22 Jun 2007 21:19:31 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:
Quote:
Originally Posted by
>This (demo) statement is fine in Access, and so far as I can see, should
>be OK in SQL Server.
>
>But Enterprise Manager barfs at the final bracket. Can anyone help
>please?
>
>select sum(field1) as sum1, sum(field2) as sum2 from
>(SELECT * from test where id < 3
>union
>SELECT * from test where id 2)
>
>In fact, I can reduce it to :-
>
>select * from
>(SELECT * from test)
>
>with the same effect - clearly I just need telling :-)
>
>
>cheers,
>Jim
<esquel@.sommarskog.sewrote:
Quote:
Originally Posted by
>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:
Quote:
Originally Posted by
>This (demo) statement is fine in Access, and so far as I can see, should
>be OK in SQL Server.
>>
>But Enterprise Manager barfs at the final bracket. Can anyone help
>please?
>>
>select sum(field1) as sum1, sum(field2) as sum2 from
>(SELECT * from test where id < 3
>union
>SELECT * from test where id 2)
>>
>In fact, I can reduce it to :-
>>
>select * from
>(SELECT * from test)
>>
>with the same effect - clearly I just need telling :-)
>
>In SQL Server, you need to provide an alias for the derived table:
>
SELECT * FROM (SELECT *FROM test) AS x
Thanks to both you and Roy.
--
Jim
a Yorkshire polymoth|||On Sat, 23 Jun 2007 06:32:00 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:
Quote:
Originally Posted by
>On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
><esquel@.sommarskog.sewrote:
>
Quote:
Originally Posted by
>>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:
Quote:
Originally Posted by
>>This (demo) statement is fine in Access, and so far as I can see, should
>>be OK in SQL Server.
>>>
>>But Enterprise Manager barfs at the final bracket. Can anyone help
>>please?
>>>
>>select sum(field1) as sum1, sum(field2) as sum2 from
>>(SELECT * from test where id < 3
>>union
>>SELECT * from test where id 2)
>>>
>>In fact, I can reduce it to :-
>>>
>>select * from
>>(SELECT * from test)
>>>
>>with the same effect - clearly I just need telling :-)
>>
>>In SQL Server, you need to provide an alias for the derived table:
>>
> SELECT * FROM (SELECT *FROM test) AS x
>
And, actually, and for the record, let's just add, that for this to work
on Oracle we have to omit the "as" - which is OK for SQL Server as well.
--
Jim
a Yorkshire polymoth
correct syntax for ADO.net recordsets Select method.
I have a interger stored in x.
I want to use x in a SELECT statement like so :
SELECT * from aTable WHERE A_Column = x
This select statement is then assigned to and passed as a string like :
sql = "SELECT * from aTable WHERE A_Column = x"
How does the x get interpreted correctly ?Try:
sql = "SELECT * from aTable WHERE A_Column = " & x
I want to use x in a SELECT statement like so :
SELECT * from aTable WHERE A_Column = x
This select statement is then assigned to and passed as a string like :
sql = "SELECT * from aTable WHERE A_Column = x"
How does the x get interpreted correctly ?Try:
sql = "SELECT * from aTable WHERE A_Column = " & x
Correct SELECT Statement via joins
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to be
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>
|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:
> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
> ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan
|||Thanks for the reply... however you say the 'LIKE' is not efficient, can you suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If you can have nulls then you'd have to change the INNER joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER returned even if you have a NULL last supplier or current supplier. Also the "LIKE '%something%'" is not a fantastic search because it will require a complete index scan, ie. it will have to search every single row in the index from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
|||Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If you can have nulls then you'd have to change the INNER joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER returned even if you have a NULL last supplier or current supplier. Also the "LIKE '%something%'" is not a fantastic search because it will require a complete index scan, ie. it will have to search every single row in the index from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
|||There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
[vbcol=seagreen]
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =
> i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to be
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>
|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:
> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
> ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan
|||Thanks for the reply... however you say the 'LIKE' is not efficient, can you suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If you can have nulls then you'd have to change the INNER joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER returned even if you have a NULL last supplier or current supplier. Also the "LIKE '%something%'" is not a fantastic search because it will require a complete index scan, ie. it will have to search every single row in the index from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
|||Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If you can have nulls then you'd have to change the INNER joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER returned even if you have a NULL last supplier or current supplier. Also the "LIKE '%something%'" is not a fantastic search because it will require a complete index scan, ie. it will have to search every single row in the index from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
|||There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
[vbcol=seagreen]
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =
> i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
Tuesday, March 27, 2012
Correct SELECT Statement via joins
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
GavSELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what
i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIER
S
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to b
e
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:
> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement
:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
> ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan|||Thanks for the reply... however you say the 'LIKE' is not efficient, can you
suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a
given ITEM row. If you can have nulls then you'd have to change the INNER
joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER
returned even if you have a NULL last supplier or current supplier. Also t
he "LIKE '%something%'" is not a fantastic search because it will require a
complete index scan, ie. it will have to search every single row in the inde
x from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav|||Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a
given ITEM row. If you can have nulls then you'd have to change the INNER
joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER
returned even if you have a NULL last supplier or current supplier. Also t
he "LIKE '%something%'" is not a fantastic search because it will require a
complete index scan, ie. it will have to search every single row in the inde
x from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav|||There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
[vbcol=seagreen]
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =
> i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
>
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
GavSELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what
i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIER
S
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to b
e
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:
> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement
:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
> ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan|||Thanks for the reply... however you say the 'LIKE' is not efficient, can you
suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a
given ITEM row. If you can have nulls then you'd have to change the INNER
joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER
returned even if you have a NULL last supplier or current supplier. Also t
he "LIKE '%something%'" is not a fantastic search because it will require a
complete index scan, ie. it will have to search every single row in the inde
x from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav|||Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a
given ITEM row. If you can have nulls then you'd have to change the INNER
joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER
returned even if you have a NULL last supplier or current supplier. Also t
he "LIKE '%something%'" is not a fantastic search because it will require a
complete index scan, ie. it will have to search every single row in the inde
x from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav|||There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
[vbcol=seagreen]
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =
> i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
>
Correct SELECT Statement via joins
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
GavThis is a multi-part message in MIME format.
--090105020707090300000908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to be
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>
--090105020707090300000908
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<blockquote><tt>SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME</tt><br>
<tt>FROM ITEMS as i</tt><br>
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER</tt><br>
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt><br>
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt><br>
</blockquote>
<tt><br>
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or
current supplier. Also the "LIKE '%something%'" is not a fantastic
search because it will require a complete index scan, ie. it will have
to search every single row in the index from start to end, which is not
terribly efficient.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="midu0PpRd8ZFHA.2520@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
</pre>
</blockquote>
</body>
</html>
--090105020707090300000908--|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:
> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER => ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan|||This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C56851.48FDF610
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks for the reply... however you say the 'LIKE' is not efficient, can =you suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =3D =i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER =for a given ITEM row. If you can have nulls then you'd have to change =the INNER joins to OUTER joins (left outer joins) so that you still get =the ITEMNUMBER returned even if you have a NULL last supplier or current =supplier. Also the "LIKE '%something%'" is not a fantastic search =because it will require a complete index scan, ie. it will have to =search every single row in the index from start to end, which is not =terribly efficient.
HTH
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_000C_01C56851.48FDF610
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks for the reply... however you say =the 'LIKE' is not efficient, can you suggest a more efficient way to do =this?
Thanks again
Gav
"Mike Hodgson" wrote in message news:%23VzsIQ9ZFHA.=2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAMEFROM ITEMS as =i INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER INNER JOIN SUPPLIERS =as c on c.SUPPLIERNUMBER =3D i.CURRENTSUPPLIERWHERE =l.SUPPLIERNAME LIKE '%MICROSOFT%'This is assuming you =can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If =you can have nulls then you'd have to change the INNER joins to OUTER joins =(left outer joins) so that you still get the ITEMNUMBER returned even if you =have a NULL last supplier or current supplier. Also the "LIKE ='%something%'" is not a fantastic search because it will require a complete index scan, =ie. it will have to search every single row in the index from start to end, =which is not terribly efficient.HTH
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_000C_01C56851.48FDF610--|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C56854.400BBDA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =3D =i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER =for a given ITEM row. If you can have nulls then you'd have to change =the INNER joins to OUTER joins (left outer joins) so that you still get =the ITEMNUMBER returned even if you have a NULL last supplier or current =supplier. Also the "LIKE '%something%'" is not a fantastic search =because it will require a complete index scan, ie. it will have to =search every single row in the index from start to end, which is not =terribly efficient.
HTH
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_001D_01C56854.400BBDA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just tried it out and performance aside =it's working great thanks
Gav
"Mike Hodgson" wrote in message news:%23VzsIQ9ZFHA.=2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAMEFROM ITEMS as =i INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER INNER JOIN SUPPLIERS =as c on c.SUPPLIERNUMBER =3D i.CURRENTSUPPLIERWHERE =l.SUPPLIERNAME LIKE '%MICROSOFT%'This is assuming you =can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If =you can have nulls then you'd have to change the INNER joins to OUTER joins =(left outer joins) so that you still get the ITEMNUMBER returned even if you =have a NULL last supplier or current supplier. Also the "LIKE ='%something%'" is not a fantastic search because it will require a complete index scan, =ie. it will have to search every single row in the index from start to end, =which is not terribly efficient.HTH
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_001D_01C56854.400BBDA0--|||This is a multi-part message in MIME format.
--040807050503000601030701
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER => i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
>>Hi all,
>>I cannot quite get my head around these outer/inner joins (if this is what i
>>need).
>>Basically I have 2 tables ie
>>TABLE ITEMS
>>PRIMARYKEY ITEMNUMBER
>>FOREIGNKEY LASTSUPPLIER
>>FOREIGNKEY CURRENTSUPPLIER
>>TABLE SUPPLIERS
>>PRIMARYKEY SUPPLIERNUMBER
>>SUPPLIERNAME
>>Currently I can get the information I need by using this sort of statement:
>>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =>>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>>ITEMS
>>However this is not how I really want to do it... because I would like to be
>>able to something like:
>>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>>Could anyone help me with this please?
>>Thanks
>>Gav
>>
>>
--040807050503000601030701
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>There's nothing wrong with the LIKE operator per se. It's just
when you you compare it to a string that begins with '%' (such as
'%MICROSOFT%'), which basically means this data could start with
anything and so could be anywhere in the index so I'd better scan the
entire index so we find all occurrences. Just make the search more
specific.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="miduaIEsjEaFHA.3384@.TK2MSFTNGP09.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2627" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Thanks for the reply... however you
say the 'LIKE' is not efficient, can you suggest a more efficient way
to do this?</font></div>
<div> </div>
<div><font face="Arial" size="2">Thanks again</font></div>
<div><font face="Arial" size="2">Gav</font></div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl">news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl</a>...</div>
<blockquote><tt>SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME</tt><br>
<tt>FROM ITEMS as i</tt><br>
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =i.LASTSUPPLIER</tt><br>
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt><br>
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt><br>
</blockquote>
<tt><br>
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or
current supplier. Also the "LIKE '%something%'" is not a fantastic
search because it will require a complete index scan, ie. it will have
to search every single row in the index from start to end, which is not
terribly efficient.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2">database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font
face="Tahoma" size="2"> +61 (2) 9296 3668 |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">F</font></b><font
face="Tahoma" size="2"> +61 (2) 9296 3885 |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">M</font></b><font
face="Tahoma" size="2"> +61 (408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
|</font><b><font face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=http://www.mallesons.com</a></font></span>">http://www.mallesons.com">http://www.mallesons.com</a></font></span>
</p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="midu0PpRd8ZFHA.2520@.TK2MSFTNGP09.phx.gbl"
type="cite">
<pre wrap="">Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--040807050503000601030701--
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
GavThis is a multi-part message in MIME format.
--090105020707090300000908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to be
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>
--090105020707090300000908
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<blockquote><tt>SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME</tt><br>
<tt>FROM ITEMS as i</tt><br>
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER</tt><br>
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt><br>
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt><br>
</blockquote>
<tt><br>
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or
current supplier. Also the "LIKE '%something%'" is not a fantastic
search because it will require a complete index scan, ie. it will have
to search every single row in the index from start to end, which is not
terribly efficient.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="midu0PpRd8ZFHA.2520@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
</pre>
</blockquote>
</body>
</html>
--090105020707090300000908--|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:
> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER => ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan|||This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C56851.48FDF610
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks for the reply... however you say the 'LIKE' is not efficient, can =you suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =3D =i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER =for a given ITEM row. If you can have nulls then you'd have to change =the INNER joins to OUTER joins (left outer joins) so that you still get =the ITEMNUMBER returned even if you have a NULL last supplier or current =supplier. Also the "LIKE '%something%'" is not a fantastic search =because it will require a complete index scan, ie. it will have to =search every single row in the index from start to end, which is not =terribly efficient.
HTH
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_000C_01C56851.48FDF610
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks for the reply... however you say =the 'LIKE' is not efficient, can you suggest a more efficient way to do =this?
Thanks again
Gav
"Mike Hodgson"
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAMEFROM ITEMS as =i INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER INNER JOIN SUPPLIERS =as c on c.SUPPLIERNUMBER =3D i.CURRENTSUPPLIERWHERE =l.SUPPLIERNAME LIKE '%MICROSOFT%'This is assuming you =can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If =you can have nulls then you'd have to change the INNER joins to OUTER joins =(left outer joins) so that you still get the ITEMNUMBER returned even if you =have a NULL last supplier or current supplier. Also the "LIKE ='%something%'" is not a fantastic search because it will require a complete index scan, =ie. it will have to search every single row in the index from start to end, =which is not terribly efficient.HTH
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_000C_01C56851.48FDF610--|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C56854.400BBDA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =3D =i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER =for a given ITEM row. If you can have nulls then you'd have to change =the INNER joins to OUTER joins (left outer joins) so that you still get =the ITEMNUMBER returned even if you have a NULL last supplier or current =supplier. Also the "LIKE '%something%'" is not a fantastic search =because it will require a complete index scan, ie. it will have to =search every single row in the index from start to end, which is not =terribly efficient.
HTH
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_001D_01C56854.400BBDA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just tried it out and performance aside =it's working great thanks
Gav
"Mike Hodgson"
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAMEFROM ITEMS as =i INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER INNER JOIN SUPPLIERS =as c on c.SUPPLIERNUMBER =3D i.CURRENTSUPPLIERWHERE =l.SUPPLIERNAME LIKE '%MICROSOFT%'This is assuming you =can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If =you can have nulls then you'd have to change the INNER joins to OUTER joins =(left outer joins) so that you still get the ITEMNUMBER returned even if you =have a NULL last supplier or current supplier. Also the "LIKE ='%something%'" is not a fantastic search because it will require a complete index scan, =ie. it will have to search every single row in the index from start to end, =which is not terribly efficient.HTH
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_001D_01C56854.400BBDA0--|||This is a multi-part message in MIME format.
--040807050503000601030701
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER => i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
>>Hi all,
>>I cannot quite get my head around these outer/inner joins (if this is what i
>>need).
>>Basically I have 2 tables ie
>>TABLE ITEMS
>>PRIMARYKEY ITEMNUMBER
>>FOREIGNKEY LASTSUPPLIER
>>FOREIGNKEY CURRENTSUPPLIER
>>TABLE SUPPLIERS
>>PRIMARYKEY SUPPLIERNUMBER
>>SUPPLIERNAME
>>Currently I can get the information I need by using this sort of statement:
>>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =>>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>>ITEMS
>>However this is not how I really want to do it... because I would like to be
>>able to something like:
>>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>>Could anyone help me with this please?
>>Thanks
>>Gav
>>
>>
--040807050503000601030701
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>There's nothing wrong with the LIKE operator per se. It's just
when you you compare it to a string that begins with '%' (such as
'%MICROSOFT%'), which basically means this data could start with
anything and so could be anywhere in the index so I'd better scan the
entire index so we find all occurrences. Just make the search more
specific.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="miduaIEsjEaFHA.3384@.TK2MSFTNGP09.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2627" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Thanks for the reply... however you
say the 'LIKE' is not efficient, can you suggest a more efficient way
to do this?</font></div>
<div> </div>
<div><font face="Arial" size="2">Thanks again</font></div>
<div><font face="Arial" size="2">Gav</font></div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl">news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl</a>...</div>
<blockquote><tt>SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME</tt><br>
<tt>FROM ITEMS as i</tt><br>
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =i.LASTSUPPLIER</tt><br>
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt><br>
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt><br>
</blockquote>
<tt><br>
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or
current supplier. Also the "LIKE '%something%'" is not a fantastic
search because it will require a complete index scan, ie. it will have
to search every single row in the index from start to end, which is not
terribly efficient.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2">database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font
face="Tahoma" size="2"> +61 (2) 9296 3668 |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">F</font></b><font
face="Tahoma" size="2"> +61 (2) 9296 3885 |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">M</font></b><font
face="Tahoma" size="2"> +61 (408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
|</font><b><font face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=http://www.mallesons.com</a></font></span>">http://www.mallesons.com">http://www.mallesons.com</a></font></span>
</p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="midu0PpRd8ZFHA.2520@.TK2MSFTNGP09.phx.gbl"
type="cite">
<pre wrap="">Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--040807050503000601030701--
Thursday, March 8, 2012
Copying data from Query Analyser.
When copying data from QA, is it possible to copy the headers with the data?
I tried Select All then Copy but got just the data.
Regards
Thief_did you hear "Thief_" <thief_@.hotmail.com> say in
news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
> When copying data from QA, is it possible to copy the headers with the
> data? I tried Select All then Copy but got just the data.
> Regards
> Thief_
>
if we're talking about column headers with the data, if you are querying
using grid view that's the best you can get. If you switch to text view
or send the results to a file (CSV or text) you will get the column
headings.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hi ,
>From QA u will not be able to get the header name but if u tranfer ur
data to text file or excel file or access file u can get the table name
as well as the table structure.
HTH
from
Doller
Neil MacMurchy wrote:
> did you hear "Thief_" <thief_@.hotmail.com> say in
> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
>
> if we're talking about column headers with the data, if you are querying
> using grid view that's the best you can get. If you switch to text view
> or send the results to a file (CSV or text) you will get the column
> headings.
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs|||doller wrote:
> Hi ,
>
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
>
> Neil MacMurchy wrote:
>
If you use SQL Server Management Studio (Which is the "query analyzer"
that comes with SQL 2005), you can actually choose to include Column
Headers when you copy data from QA.
Regards
STeen|||did you hear "doller" <sufianarif@.gmail.com> say in
news:1127707753.325611.236790@.g43g2000cwa.googlegroups.com:
> Hi ,
>
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
I think we need to clear up the term 'header'. Are we speaking of column
headings? if we are choose "results in text" from the query menu in QA.
this will allow the full columns to be selected for editing (copy/paste)or
a report saved to a file. You can also choose the "results to file" option
to save the output to a csv file. you made need to modify the options for
your results to get them in the format you would like, but these can be
chosen from tools --> option --> results (for more details check "Managing
SQL Query Analyzer Windows" from books online).
the other type of header might be in reference to a backup. perhaps this
was in reference to extend properties of a database (the
fn_listextendedproperties), but my guess is that this is what I have
mentioned above.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
#HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
> If you use SQL Server Management Studio (Which is the "query analyzer"
> that comes with SQL 2005), you can actually choose to include Column
> Headers when you copy data from QA.
>
you can do that in 2000 as well. just don't do a "select *" ;)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil MacMurchy wrote:
> did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
> #HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
>
> you can do that in 2000 as well. just don't do a "select *" ;)
>
Where can you specify that in Query Analyzer?
Regards
Steen|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in
news:urWES4zwFHA.904@.tk2msftngp13.phx.gbl:
> Where can you specify that in Query Analyzer?
select col1, col2, col3...
(it was a joke buddy...)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
I tried Select All then Copy but got just the data.
Regards
Thief_did you hear "Thief_" <thief_@.hotmail.com> say in
news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
> When copying data from QA, is it possible to copy the headers with the
> data? I tried Select All then Copy but got just the data.
> Regards
> Thief_
>
if we're talking about column headers with the data, if you are querying
using grid view that's the best you can get. If you switch to text view
or send the results to a file (CSV or text) you will get the column
headings.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hi ,
>From QA u will not be able to get the header name but if u tranfer ur
data to text file or excel file or access file u can get the table name
as well as the table structure.
HTH
from
Doller
Neil MacMurchy wrote:
> did you hear "Thief_" <thief_@.hotmail.com> say in
> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
>
> if we're talking about column headers with the data, if you are querying
> using grid view that's the best you can get. If you switch to text view
> or send the results to a file (CSV or text) you will get the column
> headings.
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs|||doller wrote:
> Hi ,
>
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
>
> Neil MacMurchy wrote:
>
If you use SQL Server Management Studio (Which is the "query analyzer"
that comes with SQL 2005), you can actually choose to include Column
Headers when you copy data from QA.
Regards
STeen|||did you hear "doller" <sufianarif@.gmail.com> say in
news:1127707753.325611.236790@.g43g2000cwa.googlegroups.com:
> Hi ,
>
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
I think we need to clear up the term 'header'. Are we speaking of column
headings? if we are choose "results in text" from the query menu in QA.
this will allow the full columns to be selected for editing (copy/paste)or
a report saved to a file. You can also choose the "results to file" option
to save the output to a csv file. you made need to modify the options for
your results to get them in the format you would like, but these can be
chosen from tools --> option --> results (for more details check "Managing
SQL Query Analyzer Windows" from books online).
the other type of header might be in reference to a backup. perhaps this
was in reference to extend properties of a database (the
fn_listextendedproperties), but my guess is that this is what I have
mentioned above.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
#HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
> If you use SQL Server Management Studio (Which is the "query analyzer"
> that comes with SQL 2005), you can actually choose to include Column
> Headers when you copy data from QA.
>
you can do that in 2000 as well. just don't do a "select *" ;)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil MacMurchy wrote:
> did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
> #HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
>
> you can do that in 2000 as well. just don't do a "select *" ;)
>
Where can you specify that in Query Analyzer?
Regards
Steen|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in
news:urWES4zwFHA.904@.tk2msftngp13.phx.gbl:
> Where can you specify that in Query Analyzer?
select col1, col2, col3...
(it was a joke buddy...)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
Copying data from Query Analyser.
When copying data from QA, is it possible to copy the headers with the data?
I tried Select All then Copy but got just the data.
Regards
Thief_did you hear "Thief_" <thief_@.hotmail.com> say in
news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
> When copying data from QA, is it possible to copy the headers with the
> data? I tried Select All then Copy but got just the data.
> Regards
> Thief_
>
if we're talking about column headers with the data, if you are querying
using grid view that's the best you can get. If you switch to text view
or send the results to a file (CSV or text) you will get the column
headings.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hi ,
>From QA u will not be able to get the header name but if u tranfer ur
data to text file or excel file or access file u can get the table name
as well as the table structure.
HTH
from
Doller
Neil MacMurchy wrote:
> did you hear "Thief_" <thief_@.hotmail.com> say in
> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
> > When copying data from QA, is it possible to copy the headers with the
> > data? I tried Select All then Copy but got just the data.
> >
> > Regards
> >
> > Thief_
> >
> >
> if we're talking about column headers with the data, if you are querying
> using grid view that's the best you can get. If you switch to text view
> or send the results to a file (CSV or text) you will get the column
> headings.
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs|||doller wrote:
> Hi ,
>>From QA u will not be able to get the header name but if u tranfer ur
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
>
> Neil MacMurchy wrote:
>> did you hear "Thief_" <thief_@.hotmail.com> say in
>> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
>> When copying data from QA, is it possible to copy the headers with the
>> data? I tried Select All then Copy but got just the data.
>> Regards
>> Thief_
>>
>> if we're talking about column headers with the data, if you are querying
>> using grid view that's the best you can get. If you switch to text view
>> or send the results to a file (CSV or text) you will get the column
>> headings.
>> --
>> Neil MacMurchy
>> http://spaces.msn.com/members/neilmacmurchy
>> http://spaces.msn.com/members/mctblogs
>
If you use SQL Server Management Studio (Which is the "query analyzer"
that comes with SQL 2005), you can actually choose to include Column
Headers when you copy data from QA.
Regards
STeen|||did you hear "doller" <sufianarif@.gmail.com> say in
news:1127707753.325611.236790@.g43g2000cwa.googlegroups.com:
> Hi ,
>>From QA u will not be able to get the header name but if u tranfer ur
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
I think we need to clear up the term 'header'. Are we speaking of column
headings? if we are choose "results in text" from the query menu in QA.
this will allow the full columns to be selected for editing (copy/paste)or
a report saved to a file. You can also choose the "results to file" option
to save the output to a csv file. you made need to modify the options for
your results to get them in the format you would like, but these can be
chosen from tools --> option --> results (for more details check "Managing
SQL Query Analyzer Windows" from books online).
the other type of header might be in reference to a backup. perhaps this
was in reference to extend properties of a database (the
fn_listextendedproperties), but my guess is that this is what I have
mentioned above.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
#HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
> If you use SQL Server Management Studio (Which is the "query analyzer"
> that comes with SQL 2005), you can actually choose to include Column
> Headers when you copy data from QA.
>
you can do that in 2000 as well. just don't do a "select *" ;)
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil MacMurchy wrote:
> did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
> #HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
>> If you use SQL Server Management Studio (Which is the "query analyzer"
>> that comes with SQL 2005), you can actually choose to include Column
>> Headers when you copy data from QA.
> you can do that in 2000 as well. just don't do a "select *" ;)
>
Where can you specify that in Query Analyzer?
Regards
Steen|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in
news:urWES4zwFHA.904@.tk2msftngp13.phx.gbl:
> Where can you specify that in Query Analyzer?
select col1, col2, col3...
(it was a joke buddy...)
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
I tried Select All then Copy but got just the data.
Regards
Thief_did you hear "Thief_" <thief_@.hotmail.com> say in
news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
> When copying data from QA, is it possible to copy the headers with the
> data? I tried Select All then Copy but got just the data.
> Regards
> Thief_
>
if we're talking about column headers with the data, if you are querying
using grid view that's the best you can get. If you switch to text view
or send the results to a file (CSV or text) you will get the column
headings.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hi ,
>From QA u will not be able to get the header name but if u tranfer ur
data to text file or excel file or access file u can get the table name
as well as the table structure.
HTH
from
Doller
Neil MacMurchy wrote:
> did you hear "Thief_" <thief_@.hotmail.com> say in
> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
> > When copying data from QA, is it possible to copy the headers with the
> > data? I tried Select All then Copy but got just the data.
> >
> > Regards
> >
> > Thief_
> >
> >
> if we're talking about column headers with the data, if you are querying
> using grid view that's the best you can get. If you switch to text view
> or send the results to a file (CSV or text) you will get the column
> headings.
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs|||doller wrote:
> Hi ,
>>From QA u will not be able to get the header name but if u tranfer ur
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
>
> Neil MacMurchy wrote:
>> did you hear "Thief_" <thief_@.hotmail.com> say in
>> news:ODKOU6jwFHA.2656@.TK2MSFTNGP09.phx.gbl:
>> When copying data from QA, is it possible to copy the headers with the
>> data? I tried Select All then Copy but got just the data.
>> Regards
>> Thief_
>>
>> if we're talking about column headers with the data, if you are querying
>> using grid view that's the best you can get. If you switch to text view
>> or send the results to a file (CSV or text) you will get the column
>> headings.
>> --
>> Neil MacMurchy
>> http://spaces.msn.com/members/neilmacmurchy
>> http://spaces.msn.com/members/mctblogs
>
If you use SQL Server Management Studio (Which is the "query analyzer"
that comes with SQL 2005), you can actually choose to include Column
Headers when you copy data from QA.
Regards
STeen|||did you hear "doller" <sufianarif@.gmail.com> say in
news:1127707753.325611.236790@.g43g2000cwa.googlegroups.com:
> Hi ,
>>From QA u will not be able to get the header name but if u tranfer ur
> data to text file or excel file or access file u can get the table name
> as well as the table structure.
> HTH
> from
> Doller
I think we need to clear up the term 'header'. Are we speaking of column
headings? if we are choose "results in text" from the query menu in QA.
this will allow the full columns to be selected for editing (copy/paste)or
a report saved to a file. You can also choose the "results to file" option
to save the output to a csv file. you made need to modify the options for
your results to get them in the format you would like, but these can be
chosen from tools --> option --> results (for more details check "Managing
SQL Query Analyzer Windows" from books online).
the other type of header might be in reference to a backup. perhaps this
was in reference to extend properties of a database (the
fn_listextendedproperties), but my guess is that this is what I have
mentioned above.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
#HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
> If you use SQL Server Management Studio (Which is the "query analyzer"
> that comes with SQL 2005), you can actually choose to include Column
> Headers when you copy data from QA.
>
you can do that in 2000 as well. just don't do a "select *" ;)
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil MacMurchy wrote:
> did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in news:
> #HoIwUnwFHA.3740@.TK2MSFTNGP14.phx.gbl:
>> If you use SQL Server Management Studio (Which is the "query analyzer"
>> that comes with SQL 2005), you can actually choose to include Column
>> Headers when you copy data from QA.
> you can do that in 2000 as well. just don't do a "select *" ;)
>
Where can you specify that in Query Analyzer?
Regards
Steen|||did you hear "Steen Persson (DK)" <spe@.REMOVEdatea.dk> say in
news:urWES4zwFHA.904@.tk2msftngp13.phx.gbl:
> Where can you specify that in Query Analyzer?
select col1, col2, col3...
(it was a joke buddy...)
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
Subscribe to:
Posts (Atom)