Home » SQL & PL/SQL » SQL & PL/SQL » Help needed with joining two tables (ms sql 2008)
Help needed with joining two tables [message #624119] Wed, 17 September 2014 23:32 Go to next message
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 #624120 is a reply to message #624119] Wed, 17 September 2014 23:38 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi,

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Thanks,
Anil MK
Re: Help needed with joining two tables [message #624121 is a reply to message #624120] Thu, 18 September 2014 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is an Oracle forum, while you use Microsoft SQL Server. You should search for help elsewhere.
Re: Help needed with joining two tables [message #624122 is a reply to message #624119] Thu, 18 September 2014 00:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
dusoo wrote on Thu, 18 September 2014 10:02
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.


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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Littlefoot wrote on Thu, 18 September 2014 10:47
while 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 Go to previous message
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.
Previous Topic: Can an {interval quantifier} contain a REGEXP_INSTR expression
Next Topic: find the sql query based on the timestamp.
Goto Forum:
  


Current Time: Fri Mar 29 02:44:36 CDT 2024