Hi,
I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.
I need to write a query which should give me records :
1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.
2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.
Please help me to resolve this complicated query.
Thanks
Nick 2 2057
Something along these lines should do it.
SELECT COALESCE(A.Exc_ Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <'
End as Compare,
A.OtherCol1, B.OtherCol1,
....
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <B.OtherCol1
OR ...
OR A.OtherCol9 <B.OtherCo9
This assumes that Exc_Ref is the unique key to both tables.
Roy Harvey
Beacon Falls, CT
On Thu, 06 Sep 2007 10:29:30 -0700, Nick
<na************ *****@gmail.com wrote:
>Hi,
I have two tables Trade table and Cons table. Records are inserted in both the tables independent of each other. There are fields like Exc_Ref, Qty, Date in both the tables.
I need to write a query which should give me records :
1. Where there is missing Exc_Ref value in either of the table. i.e. If Trade table has a Exc_Ref value but missing in Cons table then that record should be displayed. Similarly if Cons has a Exc_Ref value which is not found in Trade table then that too should be displayed.
2. In case where both the tables have matching Exc_Ref data then it should display the record only when the remaining column does not match like Qty or Date.
Please help me to resolve this complicated query.
Thanks Nick
On 6 Sep, 20:06, Roy Harvey <roy_har...@sne t.netwrote:
Something along these lines should do it.
SELECT COALESCE(A.Exc_ Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <'
End as Compare,
A.OtherCol1, B.OtherCol1,
....
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <B.OtherCol1
OR ...
OR A.OtherCol9 <B.OtherCo9
This assumes that Exc_Ref is the unique key to both tables.
Roy Harvey
Beacon Falls, CT
On Thu, 06 Sep 2007 10:29:30 -0700, Nick
<nachiket.shirw al...@gmail.com wrote:
Hi,
I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.
I need to write a query which should give me records :
1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.
2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.
Please help me to resolve this complicated query.
Thanks
Nick- Hide quoted text -
- Show quoted text -
Thanks Roy ! Your solution is too perfect.
Thanks This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Chris |
last post by:
Hello all-
Fairly new to SQL and I need to issue a pretty complex query (complex
being a relative term here :) ).
To dumb down my example for display purposes, I have two tables in my
schema
a Customer Table :
|
by: jqq |
last post by:
SQL2K on W2Kserver
I need some help revamping a rather complicated query. I've given the
table and existing query information below. (FYI, changing the
database structure is right out.)
The current query lists addresses with two particular types
('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
contact for address type 'AM39DK3KD9' and then (2) if the contact has
type 'AM39DK3KD9' select types ('AM39DK3KD9',...
|
by: Norma |
last post by:
I am trying to make a query pull data from between the dates I enter
in the parameter but also look back 'in time' to see where 2 other
fields have null values, and only pull data into the query if those 2
fields are null prior to the beginning date of my parameter.
The reason for this (to help make this a little clearer)is to pull
production into a query if it is a 'new' for the month. That is, never
run before the dates entered. I have 2...
|
by: d.p. |
last post by:
I need to create a conditional expression that's not a simple one.
I need the expression to be in the field of a table, that depends on another
field (different column) in that same table. It should be something like
this:
ColumnA
-----------
valueA = X
valueB = Y
valueC = Z
|
by: Jeff |
last post by:
Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB
I have a table in my DB called members. In that table, I have 2 tables I
will be using "username" and "points"
Now, I also have a table called all_matches. This table contains every match
report. Over 25,000 of them. I have a "username" field an "outcome" field an
"username1" field and "extra_match" field.
| |
by: Matthew Crouch |
last post by:
i suck so much that i don't even know if this is a JOIN or a subquery or
who-knows what. Here's the idea:
I want to select two things at the same time (form one table)
average for columnX
and
average for columnX where columnY=Z
so i started of course with
select avg(columnX) as avg1, avg(columnX) as avg2 from table where columnY=Z
|
by: norm10115 |
last post by:
I have a single MySQL table named "monthly" that contains the following fields:
Id, Name, Interface, Status, MonYear
I want to query "Status" for the past 3 months, of each "Device" and "Interface"
so the result might look like this:
"Name" "Interface" "Status of MonYear=Feb2007" "Status of MonYear=Mar2007" "Status of MonYear=Apr2007"
Device01 Enet1 Active Inactive Inactive...
|
by: Nick |
last post by:
Hi,
I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.
I need to write a query which should give me records :
1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
|
by: MarkNeumann |
last post by:
I'm coming from a Corel paradox background and moving into an Access environment. So I'm struggling with something that I think is probably way simpler than I'm making it out to be.
Access 2007
WindowsXP SP2
What I have is a table in a subform that tracks dollars spent per project
There is sub sub form that breaks down each dollar amount per fund. For example: sewer/water/streets/parks. In the sub sub form the fields I'm having...
|
by: jonceramic |
last post by:
Hi All,
I need to know the best way to set up a datawarehouse/materialized
view for doing statistics/graphs in Access. My crosstabs and unions
are getting too complicated to crunch in real time.
Should I use a make-table I run after each update?
Thanks,
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |