|
7 Feb 2005, 14:56
|
#1
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
7 Feb 2005, 15:09
|
#2
|
Born Sinful
Join Date: Nov 2000
Location: Loughborough, UK
Posts: 4,059
|
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.
|
|
|
7 Feb 2005, 15:40
|
#3
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
7 Feb 2005, 15:49
|
#4
|
Born Sinful
Join Date: Nov 2000
Location: Loughborough, UK
Posts: 4,059
|
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.
|
|
|
8 Feb 2005, 00:01
|
#5
|
Commander
Join Date: Sep 2001
Location: Netherlands
Posts: 146
|
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.
|
|
|
|
8 Feb 2005, 05:07
|
#6
|
Street Tramp
Join Date: Apr 2000
Location: Street Gutter
Posts: 341
|
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.
|
|
|
8 Feb 2005, 08:12
|
#7
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
14 Feb 2005, 15:19
|
#8
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
14 Feb 2005, 15:33
|
#9
|
Commander
Join Date: Sep 2001
Location: Netherlands
Posts: 146
|
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.
|
|
|
|
14 Feb 2005, 15:43
|
#10
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
14 Feb 2005, 15:57
|
#11
|
Commander
Join Date: Sep 2001
Location: Netherlands
Posts: 146
|
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.
|
|
|
15 Feb 2005, 08:23
|
#12
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
15 Feb 2005, 09:46
|
#13
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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.
|
|
|
15 Feb 2005, 10:14
|
#14
|
Commander
Join Date: Sep 2001
Location: Netherlands
Posts: 146
|
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.
|
|
|
|
15 Feb 2005, 10:27
|
#15
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
15 Feb 2005, 10:48
|
#16
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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.
|
|
|
15 Feb 2005, 10:51
|
#17
|
Commander
Join Date: Sep 2001
Location: Netherlands
Posts: 146
|
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.
|
|
|
|
15 Feb 2005, 11:07
|
#18
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
16 Feb 2005, 08:34
|
#19
|
Commander
Join Date: Sep 2001
Location: Netherlands
Posts: 146
|
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.
|
|
|
|
16 Feb 2005, 13:10
|
#20
|
Rawr rawr
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
|
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"
|
|
|
17 Feb 2005, 00:58
|
#21
|
Commander
Join Date: Sep 2001
Location: Netherlands
Posts: 146
|
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.
|
|
|
|
17 Feb 2005, 08:37
|
#22
|
Commander
Join Date: Sep 2001
Location: Netherlands
Posts: 146
|
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.
|
|
|
|
Thread Tools |
|
Display Modes |
Linear Mode
|
|
All times are GMT +1. The time now is 11:02.
| |