×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How can I get a result from a second table
2

How can I get a result from a second table

How can I get a result from a second table

(OP)
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

RE: How can I get a result from a second table

(OP)
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

RE: How can I get a result from a second table

Quote:

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

RE: How can I get a result from a second table

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

CODE

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

CODE

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

CODE

SELECT workcenter FROM tableB
MINUS
SELECT workcenter FROM tableA;

RE: How can I get a result from a second table

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:

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

RE: How can I get a result from a second table

(OP)
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!
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close