Thread: look at the sql to join two table?

Hi everyone,

Could you please tell me how to join two tables which their relationship is one column in A table inclue another column in B table.

for example:
Table A:
ColumnA1
ColumnA2

Table B:
ColumnB1
ColumnB2

I want to join Table A and Table B, and the where clause should be A.ColumnA1 include B.ColumnB1.

Like the value of A.ColumnA1 is "Abc\1234 test", and the value of B.ColumnB1 is "1234", and then we can join the TableA and Table B

Thanks a lot^_^
Winnie

By tables, do you mean named ranges in an xls file? Do a Data Query and use MSQuery.

Sorry, I don't understand what you asked clearly.

I just want to get a sql query or use Microsoft query which it should satisfy the what I saied above.

e.g.

Select A.ColumnA1, B.ColumnB1,B.ColumnB2, A.ColumnA2
from A,B
Where A.ColumnA1 like '%B.ColumnB1%'

I don't know how to write the where clause above. I think the it should be OK if the B.ColumnB1 if a string like 'abc','fff'.
But I don't know how to write that if it is a column. like B.ColumnB1

Thanks
Winnie

You can use MSQuery to build your query. See this site for an example.

You can attach an example if you need more help.

Thanks a lot Kenneth

But I think the difficulty for me is the how to write the query. Following is the sql query I write in MSQuery. And it didn't report any error when I want to execute it. But I can't get the result I expected.

e.g.

`SAP$`.Product : includes the the string like '1182/45833 test'
`Sheet1$`.Product : include the string like '45833'
and then I can't get any result when I run followng query. So I think there should be something wrong with the where clause......

Could you please help me chekk it? Thanks a lot^_^

SQL query:
SELECT `SAP$`.Product, `SAP$`.`Prod#hierarchy`, `SAP$`.Country, `SAP$`.`Profit Center`, `SAP$`.`Sales Order`, `Sheet1$`.Product AS 'pro'
FROM `C:\data\Book1`.`SAP$` `SAP$`, `C:\data\Report`.`Sheet1$` `Sheet1$`
WHERE (`SAP$`.Product Like '% `Sheet1$`.Product%')

Thanks
Winnie

I guess this comes from an external data source?

Try importing a few rows into an xls. We can subset it from there to test. Import the 2 "tables" and make a sheet that shows what you wanted. You can obfuscate the data if you need to. Attach the xls to get specific help.

Do you mean that There is no error for my Where clause syntax?

I'm afraid that the MSQuery recognize the `Sheet1$`.Product in where clause as a string, not a parameter. because it should work if I change the where clause as below:

WHERE (`SAP$`.Product Like '%45833%')

Thanks
Winnie

Originally Posted by winniewang11

Hi everyone,

Could you please tell me how to join two tables which their relationship is one column in A table inclue another column in B table.

for example:
Table A:
ColumnA1
ColumnA2

Table B:
ColumnB1
ColumnB2

I want to join Table A and Table B, and the where clause should be A.ColumnA1 include B.ColumnB1.

Like the value of A.ColumnA1 is "Abc\1234 test", and the value of B.ColumnB1 is "1234", and then we can join the TableA and Table B

Thanks a lot^_^
Winnie

Try the following as your SQL SELECT statement:

"SELECT TableA.* FROM TableA INNER JOIN TableB ON TableA.ColumnA1 = TableB.ColumnB1"

Not tested but should work.

Thanks for your reply.

But please refer to the condition i need.

I want to join Table A and Table B, and the where clause should be A.ColumnA1 include B.ColumnB1.Like the value of A.ColumnA1 is "Abc\1234 test", and the value of B.ColumnB1 is "1234", and then we can join the TableA and Table B

I think TableA.ColumnA1 should include TableB.ColumnB1, not equal...

Originally Posted by T-J

Try the following as your SQL SELECT statement:

"SELECT TableA.* FROM TableA INNER JOIN TableB ON TableA.ColumnA1 = TableB.ColumnB1"

Not tested but should work.

Thanks
Winnie

Try this type of SQL statement, using the INSTR function to check if one string is within another:

"SELECT TableA.* FROM TableA INNER JOIN TableB ON INSTR(TableA.ColumnA1, TableB.ColumnB1) > 0"

Testing on your 2 tables

Test1
No Name Age
1 Allen pas 35
2 Hellen pas 67
3 toe 88
4 fag 55
5 fau 44
6 gfg 33

Test2
Name Part
Pas SO
ret pas DO
dfdf OP

This SQL statement

"SELECT Test1.*, Test2.* FROM Test1 INNER JOIN Test2 ON INSTR(Test1.Name, Test2.Name) > 0"

returns the following results:
No Test1.Name Age Test2.Name Part
1 Allen pas 35 Pas SO
2 Hellen pas 67 Pas SO

Thanks a lot T-J ^_^
It works for me

Thanks
Winnie

BTW, I found it is so slow to use IIF in my where clause, is there any other method I can impliment the following condition?

select A.*, B.*
from A, B
where IIF(IsNull(A.[SO No#]), 'Null', CStr(A.[SO No#])) = IIF(IsNull(B.[Sales Order]), 'Null', CStr(B.[Sales Order]))


For the where clause, I just want to implement the A.[SO No#] = B.[Sales Order], but sometimes, the data user input is not in a uniform format. and sometimes, it include both text and nemeric. So I have to convert them into string.

Thanks
Winnie

Sorry I don't know the answer to that. Does anyone else know?

You might be better off asking the question in a dedicated SQL forum, like one of the Google Groups.

Cheers.