mcrmg
asked on
Query syntax
Hi,
I have a query like this
SELECT Address, State, Zip, DealName, InitName From TableA Where ID = 123
Every field is via data importing except InitName, user has to enter manually based on the DealName. One Deal has multiple records, meaning users have to enter the same InitName over and over again.
My idea is to check if InitName has already been entered for that DealName, if there is, then display the InitName.
I came up with this
SELECT Address, State, Zip, DealName, (Select Top 1 InitName from TableA where DealName=XXX) AS InitName From TableA Where ID = 123
My question is how do I get XXX? (XXX is the DealName of ID=123)
I am trying to explain this as clear as possible......thank you
I have a query like this
SELECT Address, State, Zip, DealName, InitName From TableA Where ID = 123
Every field is via data importing except InitName, user has to enter manually based on the DealName. One Deal has multiple records, meaning users have to enter the same InitName over and over again.
My idea is to check if InitName has already been entered for that DealName, if there is, then display the InitName.
I came up with this
SELECT Address, State, Zip, DealName, (Select Top 1 InitName from TableA where DealName=XXX) AS InitName From TableA Where ID = 123
My question is how do I get XXX? (XXX is the DealName of ID=123)
I am trying to explain this as clear as possible......thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Standard (normalized) approach would be to have a separate table containing all combinations of InitName and DealName values. Then you could join this table into your query and obtain the result much faster than with a subquery:
SELECT a.Address, a.State, a.Zip, a.DealName, b.InitName
From TableA a
JOIN TableB b ON b.DealName = a.DealName
Where a.ID = 123ASKER
Thank you very much..
ASKER