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!

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

Jobs

To get data in one table that is unmatched in multiple other tables

To get data in one table that is unmatched in multiple other tables

To get data in one table that is unmatched in multiple other tables

(OP)
Table1
ColoraddedJan
Red
Yellow
Green

Table2
ColoraddedFeb
Purple
Orange

Table3
ColoraddedMar
White
Black

CODE

ComparingTable
Coloradded   dateadded   dateused
Red          1/1/2017    3/1/2017
White        2/4/2017    4/4/2017
Purple       1/1/2017    5/1/2017
Pink         2/3/2017    4/3/2017 

I need the following result set from the tables above:

CODE

ColorName   dateused   colormonth
Yellow      ""         Jan
Green       ""         Jan
Orange      ""         Feb
Black       ""         Mar
Pink        4/3/2017   "" 
so far i have this query that does not work correctly:

CODE

Select ColoraddedJan as ColorName, '' as dateused, 'Jan' as colormonth
from Table1
where ColoraddedJan not in (select Coloradded from ComparingTable)
union
Select ColoraddedFeb as ColorName, '' as dateused, 'Feb' as colormonth
from Table2
where ColoraddedFeb not in (select Coloradded from ComparingTable)
union
Select ColoraddedMar as ColorName, '' as dateused, 'Mar' as colormonth
from Table3
where ColoraddedMar not in (select Coloradded from ComparingTable)
union
Select Coloradded as ColorName, dateused, '' as colormonth
from ComparingTable
where (Coloradded not in (select ColoraddedJan from Table1) 
and Coloradded not in (select ColoraddedFeb from Table2)
and Coloradded not in (select ColoraddedMar from Table3)) 

Please help. thanks.



RE: To get data in one table that is unmatched in multiple other tables

You have used Table1 three times and haven't referenced Table2 or Table3. Copy/Paste error probably.

RE: To get data in one table that is unmatched in multiple other tables

(OP)
Thanks Dave. yes i made a copy/paste error. my first 3 queries seems to work but 4th query is not returning any data.

RE: To get data in one table that is unmatched in multiple other tables

Rosie,

Just based on what you have given us I think you can do everything you need with just 1 table.

Put all you data in this table.

ComparingTable
Coloradded dateadded dateused

Then you can use the MONTH function or the DATEPART function to give you the month.

Simi

RE: To get data in one table that is unmatched in multiple other tables

(OP)
Thanks I figured out what I was doing wrong in the query. Thanks for all of your help.

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!

Resources

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