Jump to content
  • Advertisement
Sign in to follow this  

SQL - conditional join - or something along those lines

This topic is 4908 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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.


SELECT Work.Name, Factor.Factor FROM Work INNER JOIN ON Work.WorkID=Factor.WorkID
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
Sign in to follow this  

  • Advertisement

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!