User Name
Password

Go Back   Planetarion Forums > Non Planetarion Discussions > Programming and Discussion

Reply
Thread Tools Display Modes
Unread 7 Feb 2005, 14:56   #1
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
SQL - conditional join - or something like that

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".

The following dataset illustrates the problem

Code:
 -- Table 1 --			      -- Table 2 --
student	courseID		courseID	name
1	1			0		Unknown
1	2			1		Maths
2	3			2		Physics
2	4			3		Course 3
2	5			4		Course 4
Which, after the join, should result in

Code:
Student	Course
1	Maths
1	Physics
2	Course 3
2	Course 4
2	Unknown
Is this even possible with SQL ?
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 7 Feb 2005, 15:09   #2
meglamaniac
Born Sinful
 
meglamaniac's Avatar
 
Join Date: Nov 2000
Location: Loughborough, UK
Posts: 4,059
meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: SQL - conditional join - or something like that

There is probably a good reason why you haven't done so, but my immediate thought would be to make courseID default to a value of zero (please note I mean numerical zero, not NULL).
So unless you told it otherwise during the insert, the default courseID would link to "unknown".

Or am I missing something?
__________________
Worth dying for. Worth killing for. Worth going to hell for. Amen.
meglamaniac is offline   Reply With Quote
Unread 7 Feb 2005, 15:40   #3
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

Quote:
Originally Posted by meglamaniac
There is probably a good reason why you haven't done so, but my immediate thought would be to make courseID default to a value of zero (please note I mean numerical zero, not NULL).
So unless you told it otherwise during the insert, the default courseID would link to "unknown".

Or am I missing something?
You'd be right if the example was the real deal. The example is a tad simplified for what I need, but it illustrates the point.

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
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.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 7 Feb 2005, 15:49   #4
meglamaniac
Born Sinful
 
meglamaniac's Avatar
 
Join Date: Nov 2000
Location: Loughborough, UK
Posts: 4,059
meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: SQL - conditional join - or something like that

In that case: hmmmmmm.
There probably is a way, but I'm not competant enough with SQL to know it...
__________________
Worth dying for. Worth killing for. Worth going to hell for. Amen.
meglamaniac is offline   Reply With Quote
Unread 8 Feb 2005, 00:01   #5
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: SQL - conditional join - or something like that

With a right join you can get all the records from table1:
Code:
SELECT Table1.student, Table1.courseID, Table2.courseID, Table2.name
FROM Table2 RIGHT JOIN Table1 ON Table2.courseID = Table1.courseID;
You get NULL values for the last records in the last 2 fields.
I don't know My SQL, but I assume there will be a function to replace NULL values.
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 8 Feb 2005, 05:07   #6
Raging.Retard
Street Tramp
 
Raging.Retard's Avatar
 
Join Date: Apr 2000
Location: Street Gutter
Posts: 341
Raging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant futureRaging.Retard has a brilliant future
Re: SQL - conditional join - or something like that

You have not stated what DBMS you are using. The following is what I would do using SQL Server (Using your initial example data).

Code:
SELECT student, COALESCE(name, 'Unkown') As course
FROM table1 LEFT OUTER JOIN table2 ON table1.courseID = table2.courseID

p.s I've just checked and MySQL supports the COALESCE function too. So you are probably in luck regardless of DBMS.
__________________
Chimney Pots.
Raging.Retard is offline   Reply With Quote
Unread 8 Feb 2005, 08:12   #7
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

I'm using Access/JET. Your COALESCE proposal led me to the JET ISNULL() function. I'm reading here that JET doesn't have COALESCE.
Still, it's not 100% what I wanted, but it's coming close.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 14 Feb 2005, 15:19   #8
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

Crap... I still haven't solved this and it's still an issue (secretly changing the user requirements didn't work).
Is there a way of replacing the NULL values even IN the SQL statement. I'm really not interested in rewriting a load of incomprehensible functions.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 14 Feb 2005, 15:33   #9
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: SQL - conditional join - or something like that

Use NZ(value, replacevalue). In Access, ISNULL() only checks if a field is null and returns true or false (unlike most other db's)
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 14 Feb 2005, 15:43   #10
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

I which order does NZ work? I need to perform a JOIN on the value that is replaced by the NZ function. For example, if NZ puts a '0' in that field, I need to JOIN that '0' with another table.

I found a possible solution with a UNION, but somehow that does not come up with the correct results because the entire operation is awfully complicated.
Code:
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)
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 14 Feb 2005, 15:57   #11
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: SQL - conditional join - or something like that

I think this is what you are looking for:
Code:
SELECT nz(Work.Name, 0) AS Name, Factor.Factor
FROM Factor LEFT JOIN Work ON Factor.WorkID = Work.WorkID;
The Left Join tells your db to get all rows from Factor, nz replaces the NULL value from Field Work.Name to 0

Edit: I think that this isn't exactly what you want, I thought WorkID is th PK in Work, it is a FK isn't it?

In your query, WorkID 0 gets replaced by WorkID 4 (with Name Type 4) why is that?
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.

Last edited by Caesar2; 14 Feb 2005 at 16:17.
Caesar2 is offline   Reply With Quote
Unread 15 Feb 2005, 08:23   #12
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

Quote:
Originally Posted by Caesar2
I think this is what you are looking for:
Code:
SELECT nz(Work.Name, 0) AS Name, Factor.Factor
FROM Factor LEFT JOIN Work ON Factor.WorkID = Work.WorkID;
The Left Join tells your db to get all rows from Factor, nz replaces the NULL value from Field Work.Name to 0

Edit: I think that this isn't exactly what you want, I thought WorkID is th PK in Work, it is a FK isn't it?

In your query, WorkID 0 gets replaced by WorkID 4 (with Name Type 4) why is that?
It's the other way around. Because there is no appropiate factor for WorkID 4, it takes the factor of workID 0. In essence I need to be able to select a "default" factor if there is no matching factor. This default factor is identified by an ID of 0.
I just got handed the "IF statement" as a possible solution from my boss, but since he's no expert either I'm looking for possible uses of it.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 15 Feb 2005, 09:46   #13
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

Growing desperate I came up with the following:

SELECT Work.Name, HPA_Factor FROM Work INNER JOIN Factor ON (Work.ID = Factor.ID OR Factor.ID = 0) WHERE Factor.WorkID =
(SELECT MAX(Factor.workID) FROM Factor WHERE Factor.WorkID = a OR Factor.WorkID = 0)

Fetch the largest found Factor.WorkID with a subquery.

** sets up a test database

[edit]
I tested this in access:
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);
It works (hurray) but as usual I'm sceptical because I know this operation needs to be performed on a few ten-thousand records and I'm worried about performance (with the subquery and all). So I'm still looking out for a better solution.
Oh well, it works, so my boss'll be happy after I've rewritten the whole SQL statements in the app.
[/edit]
__________________
"Yay"

Last edited by Structural Integrity; 15 Feb 2005 at 10:22.
Structural Integrity is offline   Reply With Quote
Unread 15 Feb 2005, 10:14   #14
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: SQL - conditional join - or something like that

If the default factor is always 1, then you can use:
Code:
SELECT Work.Name, nz(factor.Factor,1) AS Factor
FROM [Work] LEFT JOIN factor ON Work.WorkID = factor.WorkID;
If you want the default factor to be the factor correspondeing with workID 0 then you can use:
Code:
SELECT Work.Name, nz(factor.Factor,(select factor from factor where workid=0)) AS Factor
FROM [Work] LEFT JOIN factor ON Work.WorkID = factor.WorkID;
I hope this what you wanted
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 15 Feb 2005, 10:27   #15
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

The second query results in what I want. And because its syntax simpler than what I posted above and because it doesn't have a MAX operator I think I'll go with your version.

__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 15 Feb 2005, 10:48   #16
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

Noooo


undefined function 'nz' in expression.
in: Microsoft JET Dataabse Engine

[edit] Phew [/edit]
__________________
"Yay"

Last edited by Structural Integrity; 15 Feb 2005 at 11:04.
Structural Integrity is offline   Reply With Quote
Unread 15 Feb 2005, 10:51   #17
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: SQL - conditional join - or something like that

How do you use it? In the query designer everything works fine.
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 15 Feb 2005, 11:07   #18
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

Quote:
Originally Posted by Caesar2
How do you use it? In the query designer everything works fine.
See edit.
In my query designer it work fine too, but I'm using it in a VB application. Seems like there's something with the drivers. I changed it to use IIF(IsNull(... etc etc which works.
Now to fix the damn queries correctly.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 16 Feb 2005, 08:34   #19
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: SQL - conditional join - or something like that

If you look in the VB menu under Project --> References is there an ISMISSING reference? Can you paste your code?
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 16 Feb 2005, 13:10   #20
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: SQL - conditional join - or something like that

Nope, no ISMISSING here.

What code would you need? It's a rather big project you know.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 17 Feb 2005, 00:58   #21
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: SQL - conditional join - or something like that

I just did a try myself and it doens't seem to work here either. When I try it in an Access module it works however!
I'm afraid that only google will help you to work around that, or you use the more complicated query you wrote
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 17 Feb 2005, 08:37   #22
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: SQL - conditional join - or something like that

Using Iif(FIELDNAME is null, 0, FIELDNAME) seems to work.
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Forum Jump


All times are GMT +1. The time now is 11:02.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2002 - 2018