October 20, 2010 at 3:55 am
Hi all
I urgently need to know if it is possible to lookup a value that is found in a table and searched for in another table without having a link between the tables.
For example in table1 i have column1 containing Amounts ($100). I want to take this amount and see how many times it appears in table2 column1 also containing amount and return BOTH tables rows... Like i said there is no link between the tables... :sick:
Next step if the first one is possible at all i want to compare the first amount in table1 to the amount in table2 where the Amount of table1 is BETWEEN the amount+1 and amount-1 in table2??
Any way that this is possible?
Thanks all :laugh:
October 20, 2010 at 4:03 am
Sure it's possible. Links (I assume you're referring to foreign keys) are not required in order to join tables in a query.
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
October 20, 2010 at 4:26 am
Awesome so how should a compare the two amounts? I tried the following to search for the values 1 on 1 and got it working but how can i display table2 data and how should i search for the value in table1 between a value in table2?
(SELECT *
FROM [table1]
WHERE [table1].Amount IN (SELECT [Amount] FROM table2))
October 20, 2010 at 4:28 am
IN is for exact matches, not a between or range search.
Please post table definitions, sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/
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
October 20, 2010 at 4:41 am
sorry for that new to all
October 20, 2010 at 11:04 am
wazlow (10/20/2010)
sorry for that new to all
No need to apologise for anything. I do however need to see the things I asked for to be able to help you further.
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
October 20, 2010 at 11:09 am
Try something like this:
SELECT *
FROM [table1]
inner join [table2]
on [table1].[Amount] between [table2].Amount -1 and [table2].Amount +1;
You can refine the query from there, but that should get you started.
Join math can be on any columns, it doesn't have to be on some sort of pre-defined "link". As Joe mentioned, there really aren't "links" in SQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 11:09 pm
Awesome... thank you all for all the advise and help... really learning alot...
Will work on the posting skills and terminology used... 😀
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply