SQL - conditional join (?)

Structural · #1 (**permalink**) 02-15-05, 02:30

I have a bit of a difficult problem that has been bugging me for two weeks. I need to join two tables together, but if there is no matching right-hand record it needs to select a "default record".

It's for a work registration package where I need to join on a "work factor" table. I have about 200 kinds of work, and about 10 of them have a factor. The rest should link to a default factor. The table with the factors holds a foreign key to the table with the actual work.

Code:

-- Work --	-- Factor --
Name	WorkID	WorkID	Factor
Type 1	1	0	1	// DEFAULT
Type 2	2	1	1.5
Type 3	3	2	1.2
		3	1.1
Type 4	4

Resulting in

Code:

Type 1	1.5
Type 2	1.2
Type 3	1.1
Type 4	1	// DEFAULT

So, if there is no matching factor for the work, it needs to join on a "default" factor.

This expands to a table that contains the actual work done on the workfloor, but if I can get this going that won't be much of a problem.

I hope someone can help me with this.

Thanks

[edit] I should mention that I'm using the JET engine [/edit]

Structural · #2 (**permalink**) 02-15-05, 04:08

I came up with the following so far:

Code:

SELECT Work.work_name, factor.factor
FROM work LEFT JOIN factor ON factor.work_id = work.work_id OR factor.work_id = 0
WHERE factor.work_id = (SELECT MAX(factor.work_ID) FROM factor WHERE factor.work_id = work.work_id OR factor.work_id = 0);

But with the subquery and all I have the feeling this is not particularly one of the most efficient methods.

namliam · #3 (**permalink**) 02-15-05, 05:15

I created it in Access, you will need to change the nz and cdbl to your equivalent:

Query1:

Code:

SELECT Work.Name, Factor.Factor
FROM [Work] LEFT JOIN Factor ON Work.WorkID = Factor.WorkID;

Final Query (using query1):

Code:

SELECT Query1.Name, CDbl(nz([Factor],[deffactor])) AS Factors
FROM Query1, (select Factor as DefFactor from factor where workid = 0) AS [Default];

Regards

jay82 · #4 (**permalink**) 02-23-05, 10:26

I am not sure whether Jet supports Coalesce function or not.... but if it does then following query will work for you

SELECT Name, Coalesce(factor2.factor, factor1.factor)
FROM work
LEFT OUTER JOIN factor AS factor1 ON factor1.workId = 0
LEFT OUTER JOIN factor AS factor2 ON factor2.workId = work.workId

Good Luck...

r937 · #5 (**permalink**) 02-23-05, 11:03

no, jay, access doesn't support coalesce

you must also parenthesize your joins if there are more than two tables

and the first one should be a cross or inner join, not a left join

jay82 · #6 (**permalink**) 02-24-05, 06:29

I have tested this query in SQL server 2000 and works fine with LEFT OUTER JOIN...

my apologies, I didn't understand when "Structural" said he is using Jet...

And for sure work without Parenthesis

r937 · #7 (**permalink**) 02-24-05, 06:49

yes it will work without parentheses in sql server

no it will not work without parentheses in access

Internet Services

Web Development

Digital Marketing

Consumer Tech