Sign in to follow this  
Structural

SQL - conditional join - or something along those lines

Recommended Posts

I have a bit of a difficult problem. 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.
-- 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
Type 1	1.5
Type 2	1.2
Type 3	1.1
Type 4	1	// DEFAULT
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. Is this possible with SQL?

Share this post


Link to post
Share on other sites
I'd have to really think to come up with an effecient solution, but off the top of my head a union should work.

i.e.

SELECT Work.Name, Factor.Factor FROM Work INNER JOIN ON Work.WorkID=Factor.WorkID
UNION
SELECT Work.Name, Factor.Factor FROM Work, Factor WHERE Factor.WorkID=0 AND NOT Work.WorkID IN (SELECT Factor.WorkID FROM Factor)

Two questions though:

Is the default value hardcoded, or does it have to be a value in a table (though if it was expensive enough we could just do two queries, the first to pull out the value and then hardcode the actual query).

What database are you using? MySQL 3, MySQL 4 or a database that will support most of the SQL standard.

Share this post


Link to post
Share on other sites
The default value is always 0. That assumption is safe to make.
I forgot to mention that I'm stuck to a JET database (MS Access). I will check if the UNION keyword is known by that DBMS, but to my experience most of these things work with JET.

Share this post


Link to post
Share on other sites
JET is fairly full featured, as least as far as embedded databases are concerned. It only really lacks advanced features like stored procedures. UNION is fully supported (the only database I know that lacks UNION is MySQL 3, which is why I was specifically asking if you where using MySQL, which tends to be on the shallow side of SQL feature support no matter what the version)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this