August 5, 2009 at 11:22 am
Hi,
I am having a problem comparing column values between two tables. I'll put the scenario below:
Table1
Field:X (place names)
Field:Y
Table2
Field: A (place names, place2 names)
Field: B
I want to put the values from Table2, Field B into a new table where Table1, Field X values are LIKE Table2 Field A values.
I understand using the LIKE clause with the '%' to get similar values when you are talking specific values in a column. But I want to compare ALL of the values in column X with ALL of the values in Column A, pulling out all of the values from Column B where X is LIKE A.
Please help? Thanks!
August 5, 2009 at 11:30 am
Are you saying that the field contains more than one place name per row, in that column? That's what I'm reading it as, but I'm not certain I'm understanding it correctly.
(If so, that's a really bad idea in database design. Should be one value per row per column, at the very least. Take a look at "Normal Forms" online if you aren't sure what I mean or why I mention it.)
If that's the case, you should be able to use a string parsing function to break out the distinct values into separate rows, and then use those to join the tables together.
I'd need to see some sample data (preferably in the form of table creation scripts and insert statements) before I can get more specific or provide any code that might help. Is that something you can provide?
- 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
August 5, 2009 at 11:32 am
Yep, I'll post something here in a few minutes. Thanks!
August 5, 2009 at 11:44 am
Table A
Place Number
Fisher, BC| 22
Paxtonsville, QS, RT| 15
Table B
Place Product
Fisher| Rice
Paxton| Water
Names and Fields changed for confidentiality, but, problem is the same.
So I'd like to run a Query creating a table in which the result comes out like this:
Place Number
Fisher| 22
Paxton| 15
So, I want to take the values of the Place column from Table B and the Number column from Table A, where the Place column from Table B is fully included in the Place column from Table A.
Does this make more sense?
August 5, 2009 at 12:03 pm
Would "Paxton" be considered a valid "is contained by" for "Paxtonville"?
If so, then use something like:
select *
from TableA
inner join TableB
on TableA.Place like '%' + TableB.Place + '%';
That'll get any substring match.
Does that do what you need?
- 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
August 5, 2009 at 3:31 pm
That worked great. Thanks a lot!
August 6, 2009 at 7:10 am
You're welcome.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply