All Pls help me writing SQL for the following scenario Table 1
COL1 Table 2
COL1
COL2 ----> FK to TAB1.COL1 (1 to many)
COL3
COL4
COL5
COL6 Table 3 Is a cross reference table (For a given TAB2.COL1 there may be many TAB4.COL1 values)
COL1 -------> Foreign key to TAB2.COL1
COL2 -------> Foreign Key to TAB4.COL1 Table 4
COL1
COL2
COL3
COL4
COL5
COL6
Now the requirement is for a given TAB1.COL1 I need to find out the count of TAB2.COL3 <> TAB4.COL3
TAB2.COL4 <> TAB4.COL4
TAB2.COL5 <> TAB4.COL5
TAB2.COL6 <> TAB4.COL6
In One query The output should be something like
COUNT(COL3) = 1
COUNT(COL4) = 1
COUNT(COL5) = 1
COUNT(COL6) = 1
2 1355
One possibility: -
Your four individual queries:
-
(select count(tab2.col3) a from tab2, tab4 where tab2.col3 <> tab4.col3)
-
(select count(tab2.col4) b from tab2, tab4 where tab2.col4 <> tab4.col4)
-
(select count(tab2.col5) c from tab2, tab4 where tab2.col5 <> tab4.col5)
-
(select count(tab2.col6) d from tab2, tab4 where tab2.col6 <> tab4.col6)
-
-
-
Combined query:
-
-
WITH
-
a (count1) AS
-
(select count(tab2.col3) a from tab2, tab4 where tab2.col3 <> tab4.col3
-
),
-
b (count2) AS
-
(select count(tab2.col4) b from tab2, tab4 where tab2.col4 <> tab4.col4
-
),
-
c (count3) AS
-
(select count(tab2.col5) c from tab2, tab4 where tab2.col5 <> tab4.col5
-
),
-
d (count4) AS
-
(select count(tab2.col6) d from tab2, tab4 where tab2.col6 <> tab4.col6
-
)
-
SELECT a.count1, b.count2, c.count3, d.count4 FROM a,b,c,d;
-
-
Results:
-
COUNT1 COUNT2 COUNT3 COUNT4
-
----------- ----------- ----------- -----------
-
0 0 0 0
-
-
1 record(s) selected.
-
Is this what you were looking for?
Regards
-- Sanjay
Sanjay,
Thank you so much this what exactly I am looking for
Sen
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dave |
last post by:
After following Microsofts admonition to reformat my system before doing a
final compilation of my app I got many warnings/errors upon compiling an rtf
file created in word. I used the Help...
|
by: Tom |
last post by:
A question for gui application programmers. . .
I 've got some GUI programs, written in Python/wxPython, and I've got
a help button and a help menu item. Also, I've got a compiled file
made with...
|
by: wukexin |
last post by:
Help me, good men. I find mang books that introduce bit "mang header
files",they talk too bit,in fact it is my too fool, I don't learn it, I have
do a test program, but I have no correct doing...
|
by: Colin J. Williams |
last post by:
Python advertises some basic service:
C:\Python24>python
Python 2.4.1 (#65, Mar 30 2005, 09:13:57) on
win32
Type "help", "copyright", "credits" or "license" for more information.
>>>
With...
|
by: Corepaul |
last post by:
Missing Help Files
When I enter "recordset" as the keyword and search the Visual Basic Help index,
I get many topics of interest in the resulting list. But there isn't any
information available...
|
by: Steve |
last post by:
I have written a help file (chm) for a DLL and referenced it using Help.ShowHelp
My expectation is that a developer using my DLL would be able to access this help file during his development time...
|
by: Mark |
last post by:
I have loaded Visual Studio .net on my home computer and my laptop, but my
home computer has an abbreviated help screen not 2% of the help on my laptop.
All the settings look the same on both...
|
by: JonathanOrlev |
last post by:
Hello everybody,
I wrote this comment in another message of mine, but decided to post it
again as a standalone message.
I think that Microsoft's Office 2003 help system is horrible, probably...
|
by: trunxnirvana007 |
last post by:
'UPGRADE_WARNING: Array has a new behavior. Click for more: 'ms-help://MS.VSCC.v80/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"'
'UPGRADE_WARNING: Couldn't resolve...
|
by: hitencontractor |
last post by:
I am working on .NET Version 2003 making an SDI application that calls MS Excel 2003.
I added a menu item called "MyApp Help" in the end of the menu bar to show Help-> About.
The application...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
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,...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |