Help needed with joining two tables [message #624119] |
Wed, 17 September 2014 23:32 |
dusoo
Messages: 41 Registered: March 2007
|
Member |
|
|
Hi guys,
Are you able to help me with a query which should return records from table A, which have ALL records in table B with status 1? From the data example below, the result should be A_ID = 2. Unfortunately internal system which would run my query is allowing only JOIN (OUTER, INNER, ...), UNION operations, no COUNT() functions allowed. It's easy to write this query using Count(), but as the system does not recognize this function, I'm struggling to write one. Thanks a lot.
Table A
A_ID
1
2
3
Table B
A_ID B_ID STATUS
1 1 0
1 2 1
2 3 1
2 4 1
3 5 0
|
|
|
|
|
Re: Help needed with joining two tables [message #624122 is a reply to message #624119] |
Thu, 18 September 2014 00:31 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
dusoo wrote on Thu, 18 September 2014 10:02Are you able to help me with a query which should return records from table A, which have ALL records in table B with status 1? From the data example below, the result should be A_ID = 2. Unfortunately internal system which would run my query is allowing only JOIN (OUTER, INNER, ...), UNION operations, no COUNT() functions allowed.
Explaining the rules is one part, you should provide a test case which includes create statements, few insert statements as sample data and desired output, format your code and post it using code tags. For this time let me show you :
SQL> WITH DATA1 AS(
2 SELECT 1 A_ID FROM DUAL UNION ALL
3 SELECT 2 A_ID FROM DUAL UNION ALL
4 SELECT 3 A_ID FROM DUAL)
5 , DATA2 AS(
6 SELECT 1 A_ID, 1 B_ID, 0 STATUS FROM DUAL UNION ALL
7 SELECT 1, 2, 1 FROM DUAL UNION ALL
8 SELECT 2, 3, 1 FROM DUAL UNION ALL
9 SELECT 2, 4, 1 FROM DUAL UNION ALL
10 SELECT 3, 5, 0 FROM DUAL),
11 DATA3 AS(
12 SELECT a.*,
13 Row_number() over(PARTITION BY a_id, status ORDER BY a_id, status) rn
14 FROM data2 a)
15 --select * from data3
16 SELECT a.*
17 FROM data1 a,
18 data3 b
19 WHERE a.a_id = b.a_id
20 AND b.rn = 2
21 /
A_ID
----------
2
I have not used COUNT as you mentioned you can't use. Data1 and data2 are your two tables, you can replace the WITH clause for data1 and data2 by your tables. The output is A_ID = 2, is that what you wanted?
Regards,
Lalit
|
|
|
Re: Help needed with joining two tables [message #624123 is a reply to message #624121] |
Thu, 18 September 2014 00:33 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Littlefoot wrote on Thu, 18 September 2014 10:47while you use Microsoft SQL Server
I am sorry, I didn't realize the product mentioned in title and answered thinking it to a general Oracle DB question.
|
|
|
Re: Help needed with joining two tables [message #624128 is a reply to message #624123] |
Thu, 18 September 2014 00:49 |
dusoo
Messages: 41 Registered: March 2007
|
Member |
|
|
Hi Lalit,
Thanks for your help. Unfortunately I have not thought about all exclusions. The internally made system which has some proprietary sql wizard does not know anything about analytical functions. It allows me to use UNION, INNER JOIN, LEFT OUTER, RIGHT OUTER and simple conditions, e.g. WHERE TABLE2.STATUS = 2. Most probably there is no way to create such a query to retrieve ID=2 with such limited options.
|
|
|