look at the sql to join two table?

[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

[Kenneth Hobson]

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

[winniewang11]

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

[Kenneth Hobson]

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

You can attach an example if you need more help.

[winniewang11]

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

[Kenneth Hobson]

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.

[winniewang11]

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

[winniewang11]

I want to join the following table.

The condition should be like this. The test1.name should include test2.name.
please refer to the attachment.
test1.xls
test2.xls

But when I want to run my following query in MSQuery, it always pop up a dialog and ask me to "enter parameter value" for sheet$. But it can return the result I want to after I click OK and without inputing any value. Could you please tell me how to do next and let the MSQuery don't pop up any dialog? thanks a lot

SELECT `test1$`.No, `test1$`.Name, `test1$`.Age, `Sheet1$`.Name AS 'name2' FROM `C:\test\test2`.`Sheet1$` `Sheet1$`, `C:\test\test1`.`test1$` `test1$` WHERE (`test1$`.Name Like '%'+`Sheet1$`.Name+'%')


Thanks
Winnie

[T-J]

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:

Please Login or Register  to view this content.

Not tested but should work.

[winniewang11]

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...

Try the following as your SQL SELECT statement:

Please Login or Register  to view this content.

Not tested but should work.

Thanks
Winnie

[T-J]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

[winniewang11]

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

Thanks
Winnie

[winniewang11]

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

[T-J]

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.

[winniewang11]

Thanks a lot anyway ^_^
You have given lots of help for me.
I will find some workaround way to solve my question.
I just want to find a way which I can convert anything to string, even though it is NULL. As far as I know, CStr() function will throw exception when the cell is NULL......

Thanks
Winnie