Environment is a VB6 use ACCESS database
There are 3 tables
The table name CPFileList
CpFileNo cpState
F1 A
F2 A
F3 B
F4 B
F5 C
F6 C

The table name CPOutList1
CpOutNo cpClient
D1 customer 1
D2 customer 2
D3 client 3

The table name CPOutList2
CpOutNo cpFileNo
D1 f1
D1 f2
D2 f3
D2 f4
D3 F5
D3 f6

How about in SQL according to the given cpState value and find out the corresponding cpOutNo cpClient value are
I now use is such a query
The SELECT cpOutNo FROM CPOutList1 WHERE cpClient='1' clients AND cpOutNo IN (SELECT cpOutNo FROM CPOutList2 WHERE cpFileNo IN (SELECT cpFileNo FROM CPFileList WHERE cpState='A'))
Because I am a novice, for the use of the SQL also is not very good
Don't know if it used IN the method of query is reasonable, isn't it more regular usage
Want to know about the, everyone in this table is how to check more,
Which way is more commonly used, query speed, high efficiency,

CodePudding user response:

If a small amount of data, use in there is no problem, if the data amount reaches a certain level, such as more than 1 w, had better not use in

Don't write specific code, you can see the inner join, assistance in SQL query analyzer has.

CodePudding user response:

SELECT CPOutList1 cpOutNo As Final_OutNo, CPFileList. CpFileNo, CPOutList2. CpOutNo, CPFileList2. CpFileNo FROM CPOutList, CPOutList1, CPOutList2 WHERE CPOutList1. CpOutNo=CPOutList2. CpOutNo And CPFileList. CpFileNo=CPFileList2. CpFileNo And cpClient='1' customers And cpState='A'

CodePudding user response:

Hello of123
Want to ask
SELECT a. *, a. * FROM a INNER JOIN ON a.a 1 b=a.a 1
SELECT a. *, a. * FROM the WHERE a.a=b.b 1
What's the difference between the two kinds of query methods, ah, with INNER JOIN and the WHERE condition, which is faster

CodePudding user response:

http://download.csdn.net/detail/veron_04/1644211

CodePudding user response:

http://download.csdn.net/detail/veron_04/1644211
Here didn't find the content about SQL

CodePudding user response:

The

reference 3 floor shi998 response:

hello of123
Want to ask
SELECT a. *, a. * FROM a INNER JOIN ON a.a 1 b=a.a 1
SELECT a. *, a. * FROM the WHERE a.a=b.b 1
What's the difference between the two kinds of query methods, ah, with INNER JOIN and the WHERE condition, which is faster?

To be honest, I don't know, I always assume that they are in Jet Engine platform is also performed, may be associated with the implementation of the database Engine,

CodePudding user response:

Now and find new problems
I use two kinds of INNER joins and WHERE conditions are tried, just will find out the same duplicate records
For example,
SELECT * FROM CPOutList1 INNER JOIN (CPOutList2 INNER JOIN CPFileList ON CPFileList. CpFileNo=CPOutList2. CpFileNo) ON CPOutList2. CpOutNo=CPOutList1. CpOutNo WHERE CPFileList. CpState='A'
This query, I want to should be
The table name CPOutList1
CpOutNo cpClient
D1 customer 1
In this article d1 record
But in the actual record set is 2 same
D1 customer 1
D1 customer 1
This is not what I want, because I want is just a record of CPOutList1, and cannot have repeating