访问查询以从多个表中获取记录

本文关键字：获取 记录 查询 访问 | 更新日期: 2023-09-27 18:36:09

我正在尝试从 5 个表中获取记录，最后我已经做到了，但现在我想要唯一的记录，那么我应该在查询中更改什么？

SELECT
  t.*,
  sh.invoice_Number AS sale_inv_no,
  sh.invoice_date AS sale_inv_date,
  sh.from_Date AS sale_from,
  sh.to_Date AS sale_to,
  sh.number_Of_Months AS sale_nom,
  c.cName AS cust_name,
  h.hName AS ho_name,
  [hSize1] & 'X' & [hSize2] & '=' & [hSize_SQF] AS ho_size,
  h.hLocation AS ho_loc,
  c.cMobile AS cust_mob,
  f.firmName AS firm_name
FROM
  (
    (
      (
          adv_tbl_transactions AS t
       RIGHT JOIN
          adv_tbl_Sale_Hoardings AS sh
       ON t.tra_code = sh.transaction_code
      ) LEFT JOIN adv_tbl_Hoardings AS h
        ON sh.hid = h.ID
    ) LEFT JOIN tbl_Firms AS f
      ON sh.e_fid = f.ID
  ) LEFT JOIN tbl_Customers AS c
    ON sh.cid = c.ID
WHERE
  (((t.e_fid)=1));

它给了我如下所示的输出

srNo | Hoarding    | customers   |  transaction details  | advance  | balance | total
1    | Name: h1    | Cust1       |  inv no : 1           | 6000     | 1000    | 70000
2    | Name: h1    | Cust1       |  inv no : 1           | 6000     | 1000    | 70000
3    | Name: h2    | Cust2       |  inv no : 2           | 3000     | 1000    | 40000
4    | Name: h2    | Cust2       |  inv no : 2           | 3000     | 1000    | 40000

我想输出如下

srNo | Hoarding    | customers   |  transaction details  | advance  | balance | total
1    | Name: h1    | Cust1       |  inv no : 1           | 6000     | 1000    | 70000
2    | Name: h2    | Cust2       |  inv no : 2           | 3000     | 1000    | 40000

我想要 Distict 专栏entry_no

访问查询以从多个表中获取记录

使用distinct关键字：

select distinct t.*,
       sh.invoice_Number AS sale_inv_no,
       sh.invoice_date AS sale_inv_date,
       sh.from_Date AS sale_from,
       sh.to_Date AS sale_to,
       sh.number_Of_Months AS sale_nom,
       c.cName AS cust_name,
       h.hName AS ho_name,
       [hSize1] & 'X' & [hSize2] & '=' & [hSize_SQF] AS ho_size,
       h.hLocation AS ho_loc,
       c.cMobile AS cust_mob,
       f.firmName AS firm_name
FROM (((adv_tbl_transactions AS t 
        RIGHT JOIN adv_tbl_Sale_Hoardings AS sh 
        ON t.tra_code = sh.transaction_code)
        LEFT JOIN adv_tbl_Hoardings AS h
        ON sh.hid = h.ID)
        LEFT JOIN tbl_Firms AS f ON sh.e_fid = f.ID)
        LEFT JOIN tbl_Customers AS c
        ON sh.cid = c.ID
WHERE (((t.e_fid)=1)) ;
相关文章：