|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 3rd, 2004, 12:19 PM
|
|||
|
|||
|
Complex INNER JOIN Tables
I have a couple tables that I am exporting data from.
I have one table (call it TABLE1) that contains abbreviations instead of actual words. For example instead of having the full word "New York City", the column would just contain "NYC". In another table (call it TABLE2) it has all the abbreviations and the actual words. So in TABLE2 one column name is FieldName, another Abbreviation and another is Value. So on a web site, to display that actual name I do something like this Code:
SELECT CityBoxlabel AS City
FROM TABLE1 INNER JOIN
TABLE 2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') = CityBox.[Value]
WHERE CityBox.FieldName = 'City'
This is working great except that some of these columns contain more than one abbreviation such as "NYC,WDC" which would stand for "New York City" and "Washington DC" The items that have more than one abbreviation are not being pulled across because my query is looking for an "NYC,WDC" in TABLE2 to INNER JOIN on but it won't be in there. But "NYC" and "WDC" by themselves are in there. I tried messing with the INNER JOIN statement by saying something like TABLE2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') IN CityBox.[Value] INNER JOIN But it wasn't allowing that. I can write a program to do this, but I would rather keep it in T-SQL. Hope this wasn't too confusing!  Any help is greatly appreciated!!
|
|
#2
November 3rd, 2004, 02:13 PM
|
|||
|
|||
|
It's your database design that is the cause of your problem. You should never store multiple values in one column. Break it up into separate records.
|
|
#3
November 3rd, 2004, 02:21 PM
|
|||
|
|||
|
I didn't design the database, it was already made by a different company. I am just trying to get the values out to use on a different website.
I really can't break up the record values in the main database.  Is there a way in T-SQL to do an if statement where it splits the record value at the commas and then I could just look at each one of those values? Last edited by jmlsgateway : November 3rd, 2004 at 02:27 PM.
|
|
#4
November 3rd, 2004, 03:04 PM
|
|||
|
|||
|
http://www.sqlteam.com/item.asp?ItemID=2652
|
|
#5
November 3rd, 2004, 03:08 PM
|
|||
|
|||
|
PATINDEX, SUBSTR(ING) or LIKE might be your friends
Code:
... TABLE 2 AS CityBox ON CityBox.[Value] LIKE '%' + ISNULL(TABLE1.City, 'NYC') + '%' Code:
.. TABLE 2 AS CityBox ON CityBox.[Value] =
-- 1. Part
SUBSTRING(TABLE1.City, PATINDEX('%,%', TABLE1.City) + 1, LEN(TABLE1.City))
-- 2. Part (if any)
OR CityBox.[Value]
SUBSTRING(TABLE1.City, 0, PATINDEX('%,%', TABLE1.City))
)
But that's ugly, slow and use at your own risk 8-)
|
|
#6
November 9th, 2004, 12:17 PM
|
|||
|
|||
|
I tried messing with the 2nd part using the substring method, and it worked except for the fact that if there was only one value and a comma couldn't be found, then it didn't return the information for the ID. Also there could be an undertermind amount of comma seperated values, so I couldn't really use that method.
Thanks for the reply!!!
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Complex INNER JOIN Tables |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
