December 23, 2004 at 2:33 pm
Ok hopefully this question won't be too long!
I have 3 tables that I am joining.
directory (contains id,company name,city id,state id,country id (city/state/countryid refences to xref table's id with a description of what it is (chicago,illinois,USA)
directory_role (contains directory_roletype_id which is a reference of what the directory entry is (shipper,customer,etc) in the xref table.)
This is their relationships
directory.id =directory_role.diretory_id,
xref.id = directory.city_id, directory.state_id,directory.country_id and directory_role.directory_roletype_id
This is my query so far:
SELECT d.company,d.address1,d.address2,d.city_id,d.state_id,d.country_id,d.zip,dr.directory_roletype_id,x.eng_itemdesc
FROM T_MTSTM_DIRECTORY d INNER JOIN T_MTSTM_DIRECTORY_ROLE dr
ON d.ID = dr.DIRECTORY_ID
JOIN T_MTSTM_XREF x ON x.ID = dr.directory_roletype_id
So far that query works but what I am trying to do is join the city,state, and country id columns from the the directory table and take their descriptions from the xref table and have them populate. I can only get one Join from the xref table not all 4 at the same time. JOIN T_MTSTM_XREF x ON x.ID = dr.directory_roletype_id AND x.id = d.city_id AND x.id = d.state_id AND x.id = d.country_id. Any ideas??? I have been in and out of the BOL and I am still pretty new to this so any responses would be appreciated. Thanks in advance!
December 23, 2004 at 4:20 pm
As far as I can see, you have solved your own problem:
SELECT
d.company, d.address1, d.address2, d.city_id, d.state_id, d.country_id, d.zip, dr.directory_roletype_id, x.eng_itemdesc
FROM
T_MTSTM_DIRECTORY d INNER JOIN
T_MTSTM_DIRECTORY_ROLE dr ON d.ID = dr.DIRECTORY_ID JOIN
T_MTSTM_XREF x ON x.ID = dr.directory_roletype_id AND x.id =
d.city_id AND x.id = d.state_id AND x.id = d.country_id
If this is correct, the architecture of this db is a bit unusual - particularly the xref table.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 24, 2004 at 5:41 am
It looks like you need to join to the xref table once for each column you are looking up. EG:
SELECT d.company,d.address1,d.address2,d.city_id
,d.state_id,d.country_id,d.zip
,case dr.directory_roletype_id when 1 then 'shipper' when 2 then 'customer' else 'unknown' end AS roletype_desc
,xcity.eng_itemdesc, xstate.eng_itemdesc, xcountry.eng_itemdesc
FROM T_MTSTM_DIRECTORY d INNER JOIN T_MTSTM_DIRECTORY_ROLE dr
ON d.ID = dr.DIRECTORY_ID
JOIN T_MTSTM_XREF xcity ON xcity.ID = d.city_id
JOIN T_MTSTM_XREF xstate ON xstate.ID = d.state_id
JOIN T_MTSTM_XREF xcountry ON xcountry.ID = d.country_id
Of course, this is substantially different from the joins in your originally query and I'm not clear if this is what you are looking for, but consider it. This "xref" table does not follow the usual relational integrity rules for a table, however, because the same column is used for different kinds of data.
December 24, 2004 at 6:11 am
Aaron you pretty much nailed it. The xref is a table that contains entries that can be used to describe customers (ie Shipper,Bill To,Carrier, or locations state,city,country codes) so in the directory table I can specify the city by inputing an ID that references the entry in the xref and has the description for that location, same with state,country and a role type that tells what that customer is (shipper etc...). I didn't design the DB so I don't know if this was a "wise" setup or not. Trying to get those directory columns that all have an entry from the xref table wouldn't work with my join, I could only get one of the columns to work. Thanks a bunch I am going to goof around with this today!
December 27, 2004 at 1:42 am
I think Aaron will have it right. Our IS has a similar table called "subjects", which contains only internal ID and a few more columns that are common to almost all tables (like ID number shown to users, name, time of last update and person who last updated the record, and also table where the actual record is located). It is used to assign ID's that are unique across the whole DB, not only in a certain table. This helps in certain situations, but complicates work in other. It allows to use one foreign key in a table to establish relationships to several different tables easily - e.g. new member can be recruited by existing member, by shop assistant, by recruitment campaign or by recruiter; each of these has a record in a different table, but I only need one column for it in members table. The relationship that is created is in fact to subjects table, but the IS is able to translate it and find the record in a specific table when necessary. When displaying the record, it only has to join and look into subjects table for the "name" column, not locate and join the corresponding table. On the other hand, manipulating the data outside the IS, using Query Analyzer, is sometimes quite a tough job... because you have to take care of two records - one in the actual table, one in the subjects table.
I'm not commenting on whether it is correct DB design or not, and also I'm not a developer of the system - just administrator and user. I'm just trying to explain, what is probably the reason for such construct, and that multiple joins to one table from absolutely unrelated columns (joining the table under several aliases), as in Aaron's solution, are something I work with every day.
HTH, Vladan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply