ms-access:从另一个查询中选择 [英] ms-access: select from another query

问题描述

我正在连接对 mysql 数据库的访问.

i am connecting access to a mysql db.

我需要将两个语句放在一起并将它们合二为一.

i need to put together two statements and make them into one.

例如:

SELECT 
  users.id,
  users.first,
  users.last,
  chavrusas.luser_type AS user_type,
  chavrusas.id, 
  users.title, 
  users.city, 
  users.state, 
  users.home_phone, 
  users.email
FROM users
INNER JOIN chavrusas 
  ON Users.id=chavrusas.luser_id
WHERE     ((chavrusas.ruser_id)='3166' and chavrusas.ended=false) 
  AND     (chavrusas.luser_type) <> (chavrusas.ruser_type)
  AND NOT ((chavrusas.luser_type)='teacher' AND (chavrusas.ruser_type)='student')
  AND NOT ((chavrusas.ruser_type)='teacher' AND (chavrusas.luser_type)='student'); 
UNION
SELECT  
  users.id, 
  users.first, 
  users.last, 
  chavrusas.ruser_type AS user_type, 
  chavrusas.id, 
  users.title, 
  users.city, 
  users.state, 
  users.home_phone, 
  users.email
FROM users
INNER JOIN chavrusas 
  ON Users.id=chavrusas.ruser_id
WHERE     ((chavrusas.luser_id)='3166' and chavrusas.ended=false)
  AND     (chavrusas.luser_type) <> (chavrusas.ruser_type)
  AND NOT ((chavrusas.luser_type)='teacher' AND (chavrusas.ruser_type)='student')
  AND NOT ((chavrusas.ruser_type)='teacher' AND (chavrusas.luser_type)='student')
ORDER BY 4;

Users 是一个查询，它是:

Users is a query which is:

SELECT 
  tblusers.*,
  tblusershliach.*,
  tbluserstudent.*,
  tbluserstudentteacher.*,
  tbluserteacher.*
FROM
(
  (
    (tblusers 
     LEFT JOIN tblusershliach 
     ON tblusers.id = tblusershliach.shliach_user_id
    )
  LEFT JOIN tbluserstudent 
  ON tblusers.id = tbluserstudent.student_user_id
  )
LEFT JOIN tbluserstudentteacher
ON tblusers.id = tbluserstudentteacher.student_teacher_user_id
) 
LEFT JOIN tbluserteacher 
ON tblusers.id = tbluserteacher.teacher_user_id;

我不想在第一个语句中使用Users"，而是想将它们放在一个语句中

instead of using "Users" in the first statement, i just want to put them together into one statement

我该怎么做?

推荐答案

您的 SQL 语句在 UNION 关键字之前包含一个分号.我不确定 Jet/ACE 如何处理它，但我一直认为分号意味着语句结束".丢弃它，看看你的结果是否有任何不同.我完全不相信这会解决您的问题，但让我们确保它不会造成任何影响.

Your SQL statement includes a semi-colon before the UNION keyword. I'm not sure how Jet/ACE treats it, but I always thought the semi-colon meant "end of statement". Discard it and see if your results are any different. I'm not at all confident that will fix your problem, but let's make sure it's not contributing.

更新:我做了一些测试，看起来 Jet/ACE 只是忽略了 UNION 中的分号.我叫错了树.

Update: I did some tests, and it looks like Jet/ACE just ignores a semi-colon within a UNION. I was barking up the wrong tree.

这篇关于ms-access:从另一个查询中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！