April 20, 2007 at 2:06 pm
I have parent, child table relationship. The child table has two joins to "lookup" tables, and I am wondering what the correct syntax is for this kind of view.
Parent
to Child
Child FK 1 --> Lookup Table 1
Child FK 2 --> Lookup Table 2
select parent.*, lookup2.cField2, lookup1.cField1
FROM Parent
INNER JOIN child
on child.fk = parent.pk
inner join lookup1
on child.fk1 = lookup1.pk
inner join lookup2
on child.fk2 = lookup2.pk
April 23, 2007 at 3:26 am
Hi. That query would return all the rows where there is data in BOTH lookup tables. Consider using LEFT JOIN; check out the difference by running the below:
CREATE TABLE #parent (parentPK INT, childFK INT)
CREATE TABLE #child (childPK INT, lookup1FK INT, lookup2FK INT)
CREATE TABLE #lookup1 (lookup1PK INT, cField1 VARCHAR(10))
CREATE TABLE #lookup2 (lookup2PK INT, cField2 VARCHAR(10))
INSERT INTO #parent VALUES (1, 1)
INSERT INTO #parent VALUES (2, 2)
INSERT INTO #parent VALUES (3, 3)
INSERT INTO #child VALUES (1, 1, 1)
INSERT INTO #child VALUES (2, 1, 2)
INSERT INTO #child VALUES (3, 3, 4)
INSERT INTO #lookup1 VALUES (1, 'OneOne')
INSERT INTO #lookup1 VALUES (2, 'OneTwo')
INSERT INTO #lookup1 VALUES (3, 'OneThree')
INSERT INTO #lookup1 VALUES (4, 'OneFour')
INSERT INTO #lookup2 VALUES (1, 'TwoOne')
INSERT INTO #lookup2 VALUES (2, 'TwoTwo')
INSERT INTO #lookup2 VALUES (3, 'TwoThree')
SELECT #parent.*, #lookup2.cField2, #lookup1.cField1
FROM #parent
INNER JOIN #child
ON #parent.childFK = #child.childPK
INNER JOIN #lookup1
ON #child.lookup1FK = #lookup1.lookup1PK
INNER JOIN #lookup2
ON #child.lookup2FK = #lookup2.lookup2PK
SELECT #parent.*, #lookup2.cField2, #lookup1.cField1
FROM #parent
INNER JOIN #child
ON #parent.childFK = #child.childPK
LEFT JOIN #lookup1
ON #child.lookup1FK = #lookup1.lookup1PK
LEFT JOIN #lookup2
ON #child.lookup2FK = #lookup2.lookup2PK
DROP TABLE #parent
DROP TABLE #child
DROP TABLE #lookup1
DROP TABLE #lookup2
April 25, 2007 at 10:31 am
Mark,
I'm going to guess that the above is not necessarily helpful. My guess is based on the names of your columns and assumes that there are no rows in child that do not join on rows in lookup1 or lookup2.
I believe that the answer to your problem is not straightforward and is, in fact, a design issue in your schema. An application I work on has a similar quandry. The distribution of data is such that selective de-normalization is a reasonable workaround. The path chosen was to qualify rows in one lookup table by values in the second lookup table so that only a single join was necessary.
I can't presume that this is sufficient for your task. I don't know what your data looks like. However, I don't believe that there's an optimization for the join that will reliably function through all passes of the query plan generator. Altering your structure may give you what you need.
Jeff Cochran
April 25, 2007 at 11:53 am
May or may not make any difference but nesting joins for related objects can sometimes improve performance. But it might be an idexing or other issue. I would look at the Execution Plan to see what is happening during the execution that might point to a reason. Looks for thinsg like table and index scans.
select parent.*, lookup2.cField2, lookup1.cField1
FROM
Parent
INNER JOIN
child
inner join
lookup1
on
child.fk1 = lookup1.pk
inner join
lookup2
on
child.fk2 = lookup2.pk
on
child.fk = parent.pk
April 25, 2007 at 4:14 pm
Jeff you are right that all the tables have matching records.
Indexes have been verified lots of times over.
The "parent" table has over a million records, and the two children each have 3,000 records each.
I use Visual Foxpro, so I perform one main select with one child lookup, I then get the 2nd child lookup, and then in VFP link them. Not efficient, but faster than doing it in one SELECT in SQL.
I am a littl stumped ..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply