September 17, 2012 at 10:35 am
There are two tables: ORDER1(ORDERID, DATE) and ORDER2(ORDERID, CITY)
How to code to combine two tables as query which will including all records in ORDER1 and ORDER2?
(ORDERID, DATE, CITY)
(Can not link in ORDERID because ORDERID in ORDER1 may have ORDERID in ORDER2, vise versa)
September 17, 2012 at 11:09 am
adonetok (9/17/2012)
There are two tables: ORDER1(ORDERID, DATE) and ORDER2(ORDERID, CITY)How to code to combine two tables as query which will including all records in ORDER1 and ORDER2?
(ORDERID, DATE, CITY)
(Can not link in ORDERID because ORDERID in ORDER1 may have ORDERID in ORDER2, vise versa)
What does this mean:
(Can not link in ORDERID because ORDERID in ORDER1 may have ORDERID in ORDER2, vise versa)
September 17, 2012 at 11:16 am
Because I do not know which way to join these two tables.
September 17, 2012 at 11:24 am
adonetok (9/17/2012)
Because I do not know which way to join these two tables.
Ask yourself this questions:
1- For each ORDER, do I have one row in ORDER1 and one row in ORDER2 tables?
2- If (1) is TRUE, do the ORDERID value is the same on ORDER1 and ORDER2 for a particular order?
Please let us know.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 17, 2012 at 11:25 am
can you please provide some sample data...it would be helpful in understanding your problem
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 17, 2012 at 11:49 am
In ORDER1
ORDERID---DATE
111---------2/5/2012
222---------3/8/2012
333---------8/5/2012
In ORDER2
ORDERID---CITY
111----------LONDON
333----------NEW YORK
444----------PARIS
FINAL:
ORDERID---DATE------CITY
111---------2/5/2012--LONDON
222---------3/8/2012
333---------8/5/2012--NEW YORK
444---------------------PARIS
September 17, 2012 at 11:55 am
adonetok (9/17/2012)
In ORDER1ORDERID---DATE
111---------2/5/2012
222---------3/8/2012
333---------8/5/2012
In ORDER2
ORDERID---CITY
111----------LONDON
333----------NEW YORK
444----------PARIS
FINAL:
ORDERID---DATE------CITY
111---------2/5/2012--LONDON
222---------3/8/2012
333---------8/5/2012--NEW YORK
444---------------------PARIS
A full outer join might be what you are looking for
select isnull(o1.orderid, o2.orderid) orderid, o1.[date], o2.city
from order1 o1
full outer join order2 o2 on o2.orderid = o1.orderid
September 17, 2012 at 12:44 pm
adonetok (9/17/2012)
In ORDER1ORDERID---DATE
111---------2/5/2012
222---------3/8/2012
333---------8/5/2012
In ORDER2
ORDERID---CITY
111----------LONDON
333----------NEW YORK
444----------PARIS
FINAL:
ORDERID---DATE------CITY
111---------2/5/2012--LONDON
222---------3/8/2012
333---------8/5/2012--NEW YORK
444---------------------PARIS
Teacher/Trainer has been talking about "JOIN", isn't it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply