Office Access

ひとつテーブルの内部結合 †

[edit]

SELECT
 t2.id as id , 
 t2.categoryname as categoryname,
 t1.id as parentid,
 t1.categoryname as parent
FROM
 categorytable as t1 inner join categorytable as t2 on t1.id = t2.parentid";

複雑なクエリ文サンプル †

[edit]

三つのテーブルの結合 †

[edit]

SELECT 
producttable.productname,
suppliertable.suppliername,
sockingtable.quote

FROM 
(sockingtable inner join suppliertable on sockingtable.supplierid = suppliertable.id ) -- ここの括弧を注意
inner join producttable on sockingtable.productid = producttable.id;

サンプル：

SELECT 
   producttable.id,
   producttable.productname,
   categorytable.categoryname,
   suppliertable.suppliername,
   sockingtable.quote as sockingquote
FROM 
   (
       (
           (sockingtable inner join suppliertable on sockingtable.supplierid = suppliertable.id )
           inner join producttable on sockingtable.productid = producttable.id
       )
       inner join categorytable on producttable.categoryid = categorytable.id
   )

WHERE suppliertable.id = 2 and categorytable.id = 2  and sockingtable.quote between 100 and 600

二つテーブルの結合に、片方のテーブルの重複を非表示にする †

[edit]

select  
   usertable.id,
   usertable.pname,
   (select top 1 groupname from grouptable where grouptable.userid = usertable.id) as gname
from 
   usertable

二つの列の合併 †

[edit]

select col1 + col2 as newcolum From xxxtable

行を列にする †

[edit]

Transform nz( kasu, "- ")
select distinct userid from rctable
group by userid,  kasu, rcname
pivot rcname

お名前:

(画像の文字列を入力して下さい)