SQL multiple table joint query two query instances

SQL multiple table joint query two query instances

First look at the common query

Two table structures are different
Select m.*, n.* from t1 m, t2 n where m.id = N.id
and n.date = (select Max (date) from t2 where id = n.id)

Select m.*, n.* from t1 m, t2 n where m.id = N.id
And NOT EXISTS (select 1 from t2 where id = n.id and date > N.date)

Select m.*, n.* from t1 m, t2 n where m.id = N.id
and n.date = (select min (date) from t2 where id = n.id)

Select m.*, n.* from t1 m, t2 n where m.id = N.id
And NOT EXISTS (select 1 from t2 where id = n.id and date < n.date)

Instance

2>
3> CREATE TABLE Stores (
4> stor_id char (4) Not NULL,
5> stor_name varchar () NULL,
6> stor_address varchar () NULL,
7> City varchar (a) NULL,
8> State char (2) NULL,
9> Zip char (5) NULL
10>)
11> Go
1> Insert Stores values (' 1 ', ' B ', ' 567 Ave. ', ' Tustin ', ' CA ', ' 92789 ')
2> Insert Stores values (' 2 ', ' N ', ' 577 St. ', ' Los Gatos ', ' CA ', ' 96745 ')
3> Insert Stores values (' 3 ', ' T ', ' 679 St. ', ' Portland ', ' OR ', ' 89076 ')
4> Insert Stores values (' 4 ', ' F ', ' ' St. ', ' Fremont ', ' CA ', ' 90019 ')
5> Go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> CREATE TABLE Discounts (
4> discounttype varchar () not NULL,
5> stor_id char (4) NULL,
6> lowqty smallint NULL,
7> highqty smallint NULL,
8> Discount Dec (4,2) Not NULL
9>)
10> Go
1>
2> Insert Discounts values (' Initial Customer ', NULL, NULL, NULL, 10.5)
3> Insert Discounts values (' Volume Discount ', NULL, 100, 1000, 6.7)
4> Insert Discounts values (' Customer Discount ', ' 8042 ', NULL, NULL, 5.0)
5> Go

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> SELECT stor_id as "Store ID", stor_name as "store Name"
2> from stores
3> WHERE stor_id < SOME
4> (SELECT stor_id from discounts WHERE stor_id are not NULL)
5> Go
Store ID Store Name
-------- ----------------------------------------
1 B
2 N
3 T
4 F

(4 rows affected)
1>
2> drop table stores;
3> drop table discounts;
4> Go
1>

Some syntax

4> SELECT stor_id as "Store ID", stor_name as "store Name"
5> from stores
6> WHERE stor_id!= SOME
7> (SELECT stor_id from discounts WHERE stor_id are not NULL)
8> Go

. CREATE TABLE #A (id int) Go insert to #A S (1) insert INTO #A s (2) insert to #A S (3) insert INTO #A s (4) Go--all:
All the data are satisfied and the whole condition is established.
For example: 5 is greater than all returned IDs
SELECT * from #A where 5>all (select IDs from #A) go--any:
As long as there is one piece of data to satisfy the condition, the whole condition is established,
For example: 3 is greater than 1,2 select * from #A where 3>any (select IDs from #A) go

--some is the same as any

With

--group to take one of the smallest fields, to repeat
If object_id (' [TB] ') is not null drop table [TB]
Go
CREATE TABLE [TB] ([EID] varchar (2), [OID] varchar (2), [Value] int)
Insert [TB]
Select ' E1 ', ' O1 ', 4 UNION ALL
Select ' E2 ', ' O2 ', UNION ALL
Select ' E3 ', ' O1 ', 5 UNION ALL
Select ' E4 ', ' O2 ', 8 UNION ALL
Select ' E5 ', ' O1 ', 3 UNION ALL
Select ' E6 ', ' O3 ', 9

Select t1.* from TB T1
where EID = (
Select top 1 T2. EID from TB T2
where T2. Value = (
Select min (T3. Value) from TB T3
where T2. Eid=t3. EID
) and T1. Oid=t2. Oid
)
and T1. EID in (' E1 ', ' E2 ', ' E4 ')