How can I get a result from a second table 2

[mhurkmans]

Dear,

Maybe someone can help me with this one:

I have one table i.e. A.

A is populated with next records.

Date, Workcenter
20091204, 9
20100105, 10
20100124, 11
20100127, 12
20100131, 13
20100205, 14

and table B.

Workcenter

9
10
11
12
13
14

I want to select a date range between 20100124 and 20100130
from table A.

But I want to see besides workcenters 11 and 12 also the next workcenters:

9
10
13
14

The problem is now when I join table A and B is only get the result with records which are in table A and B (is this case only workcenter 11 and 12.
I want alo 9,10,13 and 14. The reason is that I need to divide the result with all workcenters.

Thanks for your help,

Mario

 

[PHV]

The problem is now when I join table A and B
Use an OUTER JOIN.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhurkmans]

Thanks for your quick responce,

But it is not that easy. Maybe I need to be more specific i.e.:

Table A:

20091204, 9
20091215, 9
20091215, 10
20100105, 10
20091227, 11
20100124, 11
20100127, 12
20100131, 13
20100205, 14

and table B.

Workcenter

9
10
11
12
13
14

The problem is when you choose for a left outer join and you fill in a date range it only result in 1 on 1 combination. The selection date does not exist in the B table. I don't know for sure but I think more in using a NULL value like "OR date NULL".

Maybe you can help me?

Mario

But maybe

 

[r937]

The problem is when you choose for a left outer join and you fill in a date range ...

this sounds like a familiar problem, failing to put the date range test into the ON clause of the join instead of the WHERE clause


please show your query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[carp]

There are probably several paths to where you want to be (although I'm still not sure what you want your final results to look like).

SELECT a.event_date, b.workcenter
  FROM (SELECT * 
          FROM tableA 
         WHERE event_date BETWEEN  20100124 and 20100130) a
       RIGHT OUTER JOIN tableB b
       ON a.workcenter = b.workcenter;

will give you all workcenters.
On the other hand, if you are looking for workcenters that are NOT in tableA, you could try something like

SELECT b.workcenter 
FROM tableB b
WHERE NOT EXISTS (SELECT a.workcenter 
                    FROM tableA a
                   WHERE a.workcenter = b.workcenter);

Finally, I don't believe it's ANSI Standard, but if your RDBMS supports it, you could go with

SELECT workcenter FROM tableB
MINUS
SELECT workcenter FROM tableA;

 

[JarlH]

You have to be more specific. Do you mean you want ALL workcenters to be returned? Or just the ones specified (9 - 14)?

If you want all workcenters, use a right outer join:

[tt]SELECT a.*
FROM a RIGHT OUTER JOIN b ON a.wc = b.wc
AND a.datecol BETWEEN date1 AND date2[/tt]

 

[mhurkmans]

Thanks,

I have a good start now.

I searched in the red book from IBM and found the correct statement.
Indeed it was an outer join (I thought too difficult)

select b.workcenter,a.date from B left outer join A
on B.workcenter=A.workcenter and date between 20100124 and 20100130 and ttype=
'M' and B.workcenter between 9 and 18
order by B.workcenter
Thanks you all!