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:

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:
>

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--

Copying variables from another package

HI, we have something like 120 packages that need to be "upgraded" to a newer version of a template. Basically, we need to add a bunch of variables of various types (12-15 variables). Is there a way to open the package in a script task and add those variables programmatically? Or is there another way to do it (e.g. modify the dtsx file)?

Thank you,

Ccote

There's no way to do this using the Designer. However a package is just an XML document so you try adding a new variable by processing those XML documents using XQuery.

If you work out how to do that - reply here and let me know

-Jamie

|||

HI Jamie, I have been able to achieve something interesting by using a script component with the test code below:

Public Sub Main()
Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim packagename As Object = Dts.Connections("ChildPackage").AcquireConnection(Nothing)
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)
If Not package.Variables.Contains("VarTestNum") Then
package.Variables.Add("VarTestNum", False, "User", 0)
application.SaveToXml(packagename.ToString, package, Nothing)
End If

Dts.TaskResult = Dts.Results.Success

End Sub

The basis of the code I am using comes from Brian Knight web site.So far, I am not able to specify the type of the variable; It seems that SSIS resolve it using the Value method parameter (last one). I do not know if it will bw able to parse a date value correctly. But at least, if all I have to do is to change a type of a couple of variables in all package, the worst is done.

Ccote

|||

You're right about the variable type It is determined by the type of the object that you pass in that parameter.

-Jamie

Copying files from a sharepoint location to local machine using SSIS

I have to copy files from a sharepoint or extranet location (basically https://.....) location to my local server using SSIS.

Any kind of early help would be really great.

Search for Sharepoint Web Services. This is the recommended way (at least, what's been recommended to me in the past) to extract data from Sharepoint. You can use a Web Service task to access the Sharepoint web services.

http://www.developer.com/tech/article.php/3104621

copy table from a server to another server

hi guys,
I have a question about copying a table's data from one server to
another server. I want the fastest way to do it. basically, I'm using DTS
wizard to copy but it takes more than 10 minutes because there are a lot of
rows. I wonder if I use BCP by copy data to a file and do bulk insert from a
file, then will it be faster? I 'm not sure because it seems like I'm doing
2 steps instead of 1 step via DTS wizard.
I want to copy data over as fast as possible. Wondering what you guys do in
my situation.
thanks
KevinIs the server on the same network? If so, maybe try this:
INSERT [server].[database].[owner].[table]
SELECT [columns] FROM [table]
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23O7r4yEwFHA.2864@.TK2MSFTNGP10.phx.gbl...
> hi guys,
> I have a question about copying a table's data from one server to
> another server. I want the fastest way to do it. basically, I'm using
> DTS wizard to copy but it takes more than 10 minutes because there are a
> lot of rows. I wonder if I use BCP by copy data to a file and do bulk
> insert from a file, then will it be faster? I 'm not sure because it
> seems like I'm doing 2 steps instead of 1 step via DTS wizard.
> I want to copy data over as fast as possible. Wondering what you guys do
> in my situation.
> thanks
> Kevin
>|||BCP should be faster
I mean try it out see what the time difference is
http://sqlservercode.blogspot.com/
"Kevin" wrote:

> hi guys,
> I have a question about copying a table's data from one server to
> another server. I want the fastest way to do it. basically, I'm using DT
S
> wizard to copy but it takes more than 10 minutes because there are a lot o
f
> rows. I wonder if I use BCP by copy data to a file and do bulk insert from
a
> file, then will it be faster? I 'm not sure because it seems like I'm doi
ng
> 2 steps instead of 1 step via DTS wizard.
> I want to copy data over as fast as possible. Wondering what you guys do
in
> my situation.
> thanks
> Kevin
>
>|||yes, the same network.
but why is your way different from dts or better than DTS?
I don't think it's faster.
"Yosh" <yoshi@.nospam.com> wrote in message
news:eTpbw1EwFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Is the server on the same network? If so, maybe try this:
> INSERT [server].[database].[owner].[table]
> SELECT [columns] FROM [table]
>
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23O7r4yEwFHA.2864@.TK2MSFTNGP10.phx.gbl...
>|||I thought there are some kinds of table setting I can tweak so I can avoid
transaction log. I know one thing, I can remove indexes to speed up the
copying, but what other tricks do you have?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:487F4412-7B52-4B5A-A8C4-8B57A6512E15@.microsoft.com...
> BCP should be faster
> I mean try it out see what the time difference is
> http://sqlservercode.blogspot.com/
>
> "Kevin" wrote:
>