December 11, 2008 at 5:56 am
We have an ERP system. I have been asked to extract (for migration purposes) Products & Structures where the Parent ProductId begins with 'DME_'
So........
1) I have been asked to extract Products from dbo.Products that begin with 'DME_%'.
2) I must then extract 'Structures' that relate to these Products from dbo.Structures
I have a query that uses table alias:
SELECT
Products.ProductId AS ParentProductId,
Products.ProductDescription AS ParentDescription,
Components.ProductId AS ComponentProductId,
Components.ProductDescription AS ComponentDescription
FROM Structures
INNER JOIN Products ON Structures.Product = Products.Product
INNER JOIN Products AS Components ON Structures.Component = Components.Product
ORDER BY Products.Product
However I am unable to use the WHERE clause when using a table alias i.e.
WHERE ParentProductId LIKE 'DME_%'
Any ideas on how I might get the desired results?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 11, 2008 at 6:02 am
Philip Horan (12/11/2008)
We have an ERP system. I have been asked to extract (for migration purposes) Products & Structures where the Parent ProductId begins with 'DME_'So........
1) I have been asked to extract Products from dbo.Products that begin with 'DME_%'.
2) I must then extract 'Structures' that relate to these Products from dbo.Structures
I have a query that uses table alias:
SELECT
Products.ProductId AS ParentProductId,
Products.ProductDescription AS ParentDescription,
Components.ProductId AS ComponentProductId,
Components.ProductDescription AS ComponentDescription
FROM Structures
INNER JOIN Products ON Structures.Product = Products.Product
INNER JOIN Products AS Components ON Structures.Component = Components.Product
ORDER BY Products.Product
However I am unable to use the WHERE clause when using a table alias i.e.
WHERE ParentProductId LIKE 'DME_%'
Any ideas on how I might get the desired results?
Many Thanks,
Phil.
That should be Products.ProductId LIKE 'DME_%' then. However, I would rewrite the query like this:
SELECT
P.ProductId AS ParentProductId,
P.ProductDescription AS ParentDescription,
C.ProductId AS ComponentProductId,
C.ProductDescription AS ComponentDescription
FROM Structures AS S
INNER JOIN Products AS P ON S.Product = P.Product
INNER JOIN Products AS C ON S.Component = C.Product
WHERE P.ProductId LIKE 'DME_%'
ORDER BY P.Product
But that's just me, long aliases confuse me.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
December 11, 2008 at 6:09 am
Thanks Ronald. I was having a blonde moment.
Initially I tried:
WHERE Products.ProductId LIKE 'DME_'
The returned zero.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 11, 2008 at 6:09 am
Philip Horan (12/11/2008)
However I am unable to use the WHERE clause when using a table alias i.e.
WHERE ParentProductId LIKE 'DME_%'
That's a column alias, not a table alias. The select clause, including column aliases, is evaluated after the where clause is. Hence in the where clause you must reference the columns by their original names, not by aliases.
Order by is the only clause where you can use the column's alias.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 11, 2008 at 6:34 am
Thanks Gail.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply