sql server db: 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> 
<tt>FROM ITEMS as i</tt> 
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER</tt> 
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt> 
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt> 
</blockquote>
<tt> 
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 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
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> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
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>Thanks for the reply... however you
say the 'LIKE' is not efficient, can you suggest a more efficient way
to do this?</div>
<div> </div>
<div>Thanks again</div>
<div>Gav</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> 
<tt>FROM ITEMS as i</tt> 
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =i.LASTSUPPLIER</tt> 
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt> 
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt> 
</blockquote>
<tt> 
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 
</tt>
<div class="moz-signature">
-- 
mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907 
E <a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
| W <a href="http://links.10026.com/?link=http://www.mallesons.com</a>">http://www.mallesons.com">http://www.mallesons.com</a>

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

Tuesday, March 27, 2012

Correct SELECT Statement via joins

No comments:

Post a Comment

sql server db

Blog Archive

About Me