SQL Basics

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesSQL Basics

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old November 21st, 2012, 09:35 AM
andygill andygill is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 13 andygill User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 46 m 32 sec
Reputation Power: 0
Joining Two tables

Hi
Can anyone help me with a query two join two tables.

I have

Bud

Month code Budget
7 465318 300
9 465318 300


Fore

Month code Forecast
5 465318 400
6 465318 100
7 465318 200

I need to combine both tables (and in this case selecting month 5 from the Fore table) however in some cases there will be a bud without a Fore and visa versa

In the example above I want the result to be.

Month code Budget Forecast
7 465318 300 400
9 465318 300 400


I have tried everything.

The problem is when there is no month 5 (my selected month) in Fore. I still need it to show the data in the Bud for that code.

Hope this makes sense.

Thanks

Last edited by andygill : November 21st, 2012 at 09:45 AM. Reason: error

Reply With Quote
  #2  
Old November 21st, 2012, 11:44 AM
Kenny_FSW Kenny_FSW is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 4 Kenny_FSW User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 39 m 45 sec
Reputation Power: 0
Hi Andy,

You require a full Join, I rarely use them, but I believe this is what it should look like.

SELECT Fore.Month, Fore.code, Fore.Forecast, Bud.Budget
FROM Fore
FULL JOIN Bud
ON Fore.Month=Bud.Month and Fore.code=Bud.code

Im relativly new to this, but hope that helps

Reply With Quote
  #3  
Old November 23rd, 2012, 05:18 AM
andygill andygill is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 13 andygill User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 46 m 32 sec
Reputation Power: 0
Thanks for your reply it worked a treat !!

However I now have another problem.

This works fine if I don't use any criteria.

However I want to be able to select a month from each table (the month may be different) for example month 7 in the table Bud and month 5 in table Fore

If I use where Bud = 7 and Fore = 5 it only gives me data if there is data for month 7 and month 5.

I want it to show null if no data in one of the tables and the correct data in the other.

Reply With Quote
  #4  
Old November 23rd, 2012, 05:24 AM
Kenny_FSW Kenny_FSW is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 4 Kenny_FSW User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 39 m 45 sec
Reputation Power: 0
Hi Andy,

I dont see that working, basically the query makes the database believe that the month wether it be for bud or fore are now the same.

So you would need to use:

Where bud.month =7 OR bud.month =5

You could also use

Where fore.month =7 OR fore.month =5

If combined with the rest of the query, both those lines will return the same results.

-Kenny

Reply With Quote
  #5  
Old November 23rd, 2012, 05:31 AM
andygill andygill is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 13 andygill User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 46 m 32 sec
Reputation Power: 0
Hi Kenny

Thanks for the fast reply.

If I used 'or' I would also get month 5 data for Bud and month 7 data for Fore which I don't want.

I could create two views that would select the relevant month for each table and join them in an other.

However I don't want to hard code the month as I plan to link the view to excel and get the user to select their required months.

Reply With Quote
  #6  
Old November 23rd, 2012, 05:44 AM
Kenny_FSW Kenny_FSW is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 4 Kenny_FSW User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 39 m 45 sec
Reputation Power: 0
Hi Andy,

There is an alternative way to presenting this data, I'd mentioned earlier that I don't regularly use full joins. This is because I use a Union instead.

Basically if you have a field within both tables that distinguishes it as either bud or fore you could use the following

Select fore.month, fore.code, fore.forecast, fore."Unique"
Union all
Select bud.month, bud.code, bud.dudget, bud."Unique"

This will then display

Month Code Fore/bud "Unique"
7 475832 200 Forecast
7 475832 210 Budget
8 695832 300 Forecast
8 695832 350 Budget
9 475832 220 Forecast
12 475832 371 Budget

I dont know how your data tables are set out, but Im sure there must be a field that has a 0 in bud and a 1 in fore or maybe a letter or something else that you could use to identify which one it is.

For me, this enables me to use pivot tables, or summary sheets to better display the data as all the data is in a list.

-Kenny

Reply With Quote
  #7  
Old November 24th, 2012, 07:41 PM
fungui fungui is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 3 fungui User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 42 m 6 sec
Reputation Power: 0
mis-post

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesSQL Basics > Joining Two tables


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap