Back to General and Gameplay Programming

SQL - conditional join - or something along those lines

General and Gameplay Programming Programming

Started by Structural February 08, 2005 06:34 AM

2 comments, last by Michalson 19 years, 2 months ago

Structural

328

Author

February 08, 2005 06:34 AM

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?

STOP THE PLANET!! I WANT TO GET OFF!!

Michalson

1,657

February 08, 2005 07:01 AM

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.

Structural

328

Author

February 08, 2005 07:12 AM

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.

STOP THE PLANET!! I WANT TO GET OFF!!

Michalson

1,657

February 08, 2005 07:50 AM

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)

SQL - conditional join - or something along those lines

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

SQL - conditional join - or something along those lines

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Reticulating splines