January 7, 2008 at 8:35 pm
I can't seem to get my joins( inner, left) to produce the output I want.
TABLE1
PO #
-----
12
TABLE2
PO # LICENSE SERIAL
--------------------------
12 X1
12 X2
12 X3
12 X4
Result
INNER JOIN ( 4 rows)
I want only 1 row from the join (to indicate that PO# 12 exists on table 2)
January 7, 2008 at 8:58 pm
The following should return each po# in table1 that has at least one row in table2. It will only return each po# in table1 one time.
select po# from table1 where po# in (select po# from table2)
January 7, 2008 at 9:01 pm
Thank you so much for the help. That works!
January 7, 2008 at 9:05 pm
Glad I could assist.
January 7, 2008 at 9:24 pm
select top 1 * from table1 inner join table2 on table1.po# = table2.po#
"Keep Trying"
January 7, 2008 at 9:40 pm
Chirag (1/7/2008)
select top 1 * from table1 inner join table2 on table1.po# = table2.po#
This example only returns a single row, not all rows in table1 that have a matching value in table2.
January 8, 2008 at 5:01 am
i thought you just wanted an indication that po# 12 existed or not. I assumed there is only one row in table1 and this single row has the value 12.
Anyway you can use if exists also.
"Keep Trying"
January 8, 2008 at 5:08 am
January 8, 2008 at 8:01 am
What is better, IN or EXISTS? Is it a preference, does it depend on existing indexes, or does it depends on data volumne?
I use IN with 98 million rows on both sides and it works very fast. I end up killing the exists example because it looks like it is never going to complete.
January 8, 2008 at 8:06 am
January 8, 2008 at 8:43 am
More information on my query:
Tab1: I query by account. An Account has an average of 1000 POs.
Tab2: Company inventory POs ( about 2 millions POs)
I am doing a reconciliation ( ie do the account POs match with the company POs)?
So I do retrieve back column data from both tables Tab 1 and Tab2.
For example:
Once I know that Tab1.PO = Tab2.PO, I want to
get the Tab1.cost center Tab2.cost center to do the next comparison
step.
Thanks
January 8, 2008 at 10:38 pm
Hi
Exists will check for existance of a one record that satisfies the condition and then will return the records. i.e. if there is one record that satisfies the condition tab1.po = tab2.po
records from tab1 will be returned.
If u want to know whthr a PO exists in tab2
select distinct tab1.Po from tab1 inner join tab2 on tab1.po = tab2.po
this will return all PO's in tab1 that are there in tab2.
if u want cost center also
select distinct tab1.Po from tab1 inner join tab2 on tab1.po = tab2.po and tab1.costcenter = tab2.costcenter.
this will return all PO's whihc are there in tab2 and also whose costcenter in tab1 matches that in tab2.
"Keep Trying"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply