help with joining 2 tables

Question

help with joining 2 tables

Coriolan

SSCommitted

Points: 1931
More actions
January 7, 2008 at 8:35 pm

#202052

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)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Tony Mungor SSC Eights! Points: 850 More actions · Answer 1

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)

Coriolan SSCommitted Points: 1931 More actions · Answer 2

Coriolan

SSCommitted

Points: 1931

January 7, 2008 at 9:01 pm

#765559

Thank you so much for the help. That works!

Tony Mungor SSC Eights! Points: 850 More actions · Answer 3

Tony Mungor

SSC Eights!

Points: 850

January 7, 2008 at 9:05 pm

#765560

Glad I could assist.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

select top 1 * from table1 inner join table2 on table1.po# = table2.po#

"Keep Trying"

Tony Mungor SSC Eights! Points: 850 More actions · Answer 5

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.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

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"

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 7

select * from table1 a

where exists

(select 1 from table2

where table2.po# = a.po#)

If something's hard to do, then it's not worth doing.

Tony Mungor SSC Eights! Points: 850 More actions · Answer 8

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.

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 9

exists is faster as many resources says.

i tend to use exists not in.

If something's hard to do, then it's not worth doing.

Coriolan SSCommitted Points: 1931 More actions · Answer 10

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 11

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"