下面是我的两张表。
我需要连接两个表数据并相应地获取结果
对于EX-
在方案主表中,有8行具有不同的收据编号
在收据分录表中,仅创建了2个收据。。。
所以我只需要显示那些来自方案主表的余额收据,哪本书和收据不存在于收据分录表中。
表名-scheme_master
book_no2 receipt_no createddate
401 10 15-03-2015
401 11 15-03-2015
401 12 15-03-2015
401 13 15-03-2015
403 25 15-03-2015
403 26 15-03-2015
403 27 15-03-2015
403 28 15-03-2015
405 35 15-03-2015
405 36 15-03-2015
405 37 15-03-2015
405 38 15-03-2015
表名-Receipt_entry
book_no receipt_no
401 10
403 26
我只需要获取收据条目表中不存在的收据
预期输出
Book No Balance Receipt createddate
401 11 15-03-2015
401 12 15-03-2015
401 13 15-03-2015
403 25 15-03-2015
403 27 15-03-2015
403 28 15-03-2015
SQL:
SELECT DISTINCT SM.receipt_no AS Receipt,SM.book_no2 AS book,SM.created AS printedDate,SM.city AS cityname FROM scheme_master SM
LEFT JOIN receipt_entry RE ON RE.receipt_no = SM.receipt_no WHERE
SM.book_no2 BETWEEN :book_no AND :book2 AND RE.receipt_no IS NULL
首先,如果在book_no
和receipt_no
上都正确加入,则不需要distinct
。其次,where
子句中的条件是将left join
转换为inner join
,因为NULL
的值无法通过比较。这个条件应该在第一张表上,而不是在第二张表上。
所以,试试这个:
SELECT SM.book_no2 AS book, SM.created AS printedDate,
SM.receipt_no AS Receipt, RE.bookingdate AS bookingdate, RE.surveyor_name FROM scheme_master SM LEFT JOIN
receipt_entry RE
ON RE.book_no = SM.book_no2 AND RE.receipt_no = SM.receipt_no
WHERE SM.book_no = 401;
SELECT SM.book_no2 AS book,SM.created AS printedDate,SM.receipt_no AS Receipt,RE.bookingdate AS bookingdate,RE.surveyor_name
FROM scheme_master SM
INNER JOIN Receipt_entry RE
ON RE.book_no = SM.book_no2 AND RE.receipt_no = SM.receipt_no
WHERE RE.book_no = 401;
这将是您最初的查询:
SELECT DISTINCT SM.book_no2 AS book,SM.created AS printedDate,SM.receipt_no AS Receipt,RE.bookingdate AS bookingdate,RE.surveyor_name
FROM scheme_master SM
LEFT JOIN receipt_entry RE ON RE.book_no = SM.book_no2
你可以像这样修改它来满足你的需求:
SELECT SM.book_no2 AS book, SM.created AS printedDate,SM.receipt_no AS Receipt, RE.bookingdate AS bookingdate, RE.surveyor_name
FROM scheme_master SM
LEFT JOIN receipt_entry RE
ON RE.book_no = SM.book_no2
AND RE.receipt_no = SM.receipt_no;
我们将这两个语句左联接,以便查询将在same row
中输出具有相同book_number
和receipt_number
的值,然后显示具有或不具有数据的booking date
和surveyor name
。
编辑:
请检查以下
SELECT x.book,x.printedDate,x.Receipt,x.bookingdate,x.surveyor_name
FROM
(
select SM.book_no2 AS book, SM.created AS printedDate,SM.receipt_no AS Receipt, RE.bookingdate AS bookingdate, RE.surveyor_name,if(RE.book_no is null,0,RE.book_no) book_no,if(RE.receipt_no is null,0,RE.receipt_no) receipt_no
FROM scheme_master SM
LEFT JOIN receipt_entry RE
ON RE.book_no = SM.book_no2
AND RE.receipt_no = SM.receipt_no
union
select if(SM.book_no2 is null,0,SM.book_no2) AS book, SM.created AS printedDate,if(SM.receipt_no is null,0,SM.receipt_no) AS Receipt, RE.bookingdate AS bookingdate, RE.surveyor_name,RE.book_no,RE.receipt_no
FROM scheme_master SM
RIGHT JOIN receipt_entry RE
ON RE.book_no = SM.book_no2
AND RE.receipt_no = SM.receipt_no
) x
WHERE x.book != x.book_no
AND x.Receipt != x.receipt_no
您可以尝试以下查询:
select s.book_no2 as "Book No", s.receipt_no as "Balance Receipt", s.createddate
from scheme s join receipt r on (s.book_no2 = r.book_no)
where s.receipt_no not in (select receipt_no from receipt);
这里可以看到一个演示结果:SQLFiddle