内连接与左连接

Question

我有4张表如下所述：

auth_user_profiles (包含用户详细信息的表)

CREATE TABLE IF NOT EXISTS `auth_user_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `birthdate` int(11) NOT NULL,
  `relationship` smallint(6) NOT NULL COMMENT '1-single 2-married 3-Engaged 4- Separated 5- Divorced 6-Other',
  `address` varchar(255) NOT NULL,
  `city` varchar(50) NOT NULL,
  `zipcode` int(11) NOT NULL,
  `phone_no` char(12) NOT NULL,
  `country` int(11) NOT NULL,
  `work` varchar(255) NOT NULL,
  `registeredip` char(15) NOT NULL,
  `registerdate` int(11) NOT NULL,
  `profileimage` varchar(200) NOT NULL DEFAULT 'default',
  `gender` smallint(6) NOT NULL,
  `profession` varchar(50) NOT NULL,
  `aboutme` varchar(250) NOT NULL,
  `referral_balance` decimal(10,2) NOT NULL,
  `website` varchar(255) DEFAULT NULL,
  `google_open_id` varchar(256) DEFAULT NULL,
  `yahoo_open_id` varchar(256) DEFAULT NULL,
  `facebook_id` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;



INSERT INTO `auth_user_profiles` (`id`, `user_id`, `first_name`, `last_name`, `birthdate`, `relationship`, `address`, `city`, `zipcode`, `phone_no`, `country`, `work`, `registeredip`, `registerdate`, `profileimage`, `gender`, `profession`, `aboutme`, `referral_balance`, `website`, `google_open_id`, `yahoo_open_id`, `facebook_id`) VALUES
(1, 1, 'dfdfa', 'ddfm', 638649000, 1, 'fghfllgdfg', 'kdddd', 9887888, '25896589518', 67, 'NNNooo', '127.0.0.1', 1393409056, 'cind.jpg', 1, '', 'sssssssppp', 34.03, NULL, 'https://www.google.com/accounts/o8/id?id=AItOawlUhpWgJhYxjlgg8UjhsKR0u40IvYQrGZ0', 'https://me.yahoo.com/a/Yl_iKDxqkIJIvZ7y5KHTBdkNDw2L#79ed5', NULL),
(2, 2, 'Kichu', 'K', 0, 0, '', '', 0, '8958698565', 20, '', '127.0.0.1', 1394014161, 'default', 0, '', '', 0.00, NULL, NULL, NULL, NULL);

视频(存储视频细节的表格)

CREATE TABLE IF NOT EXISTS `video` (
  `videoid` int(11) NOT NULL AUTO_INCREMENT,
  `videocode` varchar(20) NOT NULL,
  `title` varchar(250) NOT NULL,
  `videotype` smallint(6) NOT NULL COMMENT '0-main or 1-trailer',
  `category` int(11) NOT NULL,
  `length` int(11) NOT NULL,
  `associatedvideo` int(11) NOT NULL COMMENT 'trailer link',
  `videolink` varchar(255) NOT NULL,
  `videothumbnail` varchar(250) NOT NULL,
  `uploaderid` int(11) NOT NULL,
  `description` text NOT NULL,
  `views` int(11) NOT NULL,
  `likes` int(11) NOT NULL,
  `dislikes` int(11) NOT NULL,
  `permission` smallint(6) NOT NULL COMMENT '0-pending 1-approved 2-rejected',
  `genre` int(11) NOT NULL,
  `language` int(11) NOT NULL,
  `keywords` varchar(256) NOT NULL,
  `ticket_price` varchar(50) NOT NULL,
  `added_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`videoid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


INSERT INTO `video` (`videoid`, `videocode`, `title`, `videotype`, `category`, `length`, `associatedvideo`, `videolink`, `videothumbnail`, `uploaderid`, `description`, `views`, `likes`, `dislikes`, `permission`, `genre`, `language`, `keywords`, `ticket_price`, `added_on`) VALUES
(1, '', 'Video 1', 0, 0, 10, 0, 'teamwork.mp4', 'images (1).jpg', 1, 'The first video uploaded.', 0, 0, 0, 1, 11, 2, 'cartoon', '', '2014-04-28 12:16:31'),
(2, '', 'dddddd', 0, 0, 10, 0, 'video.mp4', 'images (6).jpg', 1, 'vc fdsdf sdfsdf dfgdf sdfd ghg.', 0, 0, 0, 1, 6, 2, 'dfgfdg sd fsdf', '', '2014-04-28 12:46:17'),
(3, '', 'asdasfsd', 0, 0, 10, 5, 'video.mp4', 'images_(6).jpg', 1, 'dg', 282, 1, 0, 1, 5, 2, 'tdgfg', '11.2', '2014-04-28 12:54:17'),
(4, '', 'asedas sfgv', 0, 0, 66, 0, 'teamwork.mp4', 'images (8).jpg', 1, 'hdgh xdg fg dfgdf', 11, 0, 0, 1, 11, 2, 'video ', '', '2014-04-28 13:00:46'),
(5, '', 'sdfftgesgsdfgsd', 1, 0, 33, 3, 'test.mkv', 'images (2).jpg', 0, 'fgdfg', 242, 0, 0, 1, 5, 2, 'fdgdfg,video', '12.99', '2014-04-29 06:18:32');

票证(储存门票细节以供录影之用的表格)

CREATE TABLE IF NOT EXISTS `ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `video_id` int(11) NOT NULL,
  `ticket_key` varchar(100) NOT NULL,
  `attempt` smallint(6) NOT NULL,
  `generated_on` int(11) NOT NULL,
  `status` smallint(6) NOT NULL COMMENT '0-new 1-expired',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;


INSERT INTO `ticket` (`id`, `user_id`, `video_id`, `ticket_key`, `attempt`, `generated_on`, `status`) VALUES
(1, 1, 5, '0b9d4d5aee', 0, 1399262972, 0),
(2, 1, 5, '1f5de2cde3', 0, 1399263032, 0),
(3, 1, 5, 'ba2376bb21', 0, 1399263036, 0),
(4, 1, 2, 'd571360f37', 0, 1399267971, 0),
(5, 1, 5, '99d98364e4', 0, 1399276280, 0),
(6, 1, 5, '290486a5f5', 1, 1399281061, 0),
(7, 1, 5, 'cccc1c72ab', 1, 1399281148, 0);

寄送票(当票赠给朋友时存储详细信息的表)

CREATE TABLE IF NOT EXISTS `sendticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticketid` int(11) NOT NULL,
  `sendby_id` int(11) NOT NULL,
  `sendto_id` varchar(60) NOT NULL,
  `send_on` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;



INSERT INTO `sendticket` (`id`, `ticketid`, `sendby_id`, `sendto_id`, `send_on`) VALUES
(1, 2, 1, '10', 1399276280),
(2, 4, 2, 'sdasd@fjjgf.com', 1399276280);

我需要的是弄到给我的票的细节。我使用以下查询，但即使我没有收到任何门票，它是返回记录。

$userid=1;

$query=$this->db->query("select s.send_on,s.sendby_id,t.ticket_key,t.video_id,a.first_name,a.last_name,v.title,v.videothumbnail from sendticket as s LEFT JOIN ticket as t ON s.ticketid=t.id 
        LEFT JOIN auth_user_profiles as a ON a.user_id=s.sendby_id LEFT JOIN video as v ON t.video_id=v.videoid and s.sendto_id=$userid order by s.send_on desc");

在这里，我期待的是获得门票的细节，视频细节购买的门票和用户谁给了我的门票，这是sendby_id在发送票表的细节。目前我没有任何天才的票，所以它应该返回零记录。但相反，它现在返回的记录，不符合我的要求。

我知道左加入在这种情况下是行不通的。我试着用内连接和左连接。但没起作用。有人能帮我解决这个问题吗？提前谢谢。

Answer 1

您正在使用ON()子句(和s.sendto_id=$userid)过滤记录，这将只对表进行筛选，也就是说，它不会过滤整个结果集，所得到的结果是由于您使用的是左联接，对于您关心的结果集，您需要使用WHERE子句。

SELECT 
  s.send_on,
  s.sendby_id,
  t.ticket_key,
  t.video_id,
  a.first_name,
  a.last_name,
  v.title,
  v.videothumbnail ,
  s.sendto_id
FROM
  sendticket AS s 
  LEFT JOIN ticket AS t 
    ON s.ticketid = t.id 
  LEFT JOIN auth_user_profiles AS a 
    ON a.user_id = s.sendby_id 
  LEFT JOIN video AS v 
    ON t.video_id = v.videoid 
    WHERE s.sendto_id = 1
ORDER BY s.send_on DESC 

因此，如果我检查s.sendto_id = 1，没有为id 1发送票证，但是如果您想测试现有的票证，可以尝试使用s.sendto_id = 10

关于send票证的表结构，有一件事令人困惑，我在列sendto_id (10，sdasd@fjjgf.com)中看到了值，您保存电子邮件和ids，我认为这不是一个好主意，如果您从请求中获得用户ids，那么我建议您使用codeigniter的活动记录库。

从注释编辑

我想要买到的票是我自己买的，还没有送给其他人，还有那些在一次查询中送给我的票。

以下是两种方法，但这两种解决方案都使用UNION

不存在的解1

第一个查询是简单地从sendticket获得由id为1且没有记录的用户购买的票，其中sendby_id来自同一个用户，联合之后的查询将这些票送给该用户。

SELECT * FROM `ticket` t 
WHERE NOT EXISTS (
SELECT 1 FROM `sendticket` s 
    WHERE  s.`ticketid` = t.`id` 
    AND  s.`sendby_id` =1
)
AND  t.user_id =1
UNION 
SELECT t.* FROM `ticket` t 
JOIN sendticket s ON(s.`ticketid` = t.`id`)
WHERE s.`sendto_id` =1

仅使用联接的解决方案2

SELECT t.* FROM `ticket` t 
LEFT JOIN sendticket s ON(s.`ticketid` = t.`id` AND s.`sendby_id` =1)
WHERE s.`sendby_id` IS NULL AND  t.user_id =1
UNION 
SELECT t.* FROM `ticket` t 
JOIN sendticket s ON(s.`ticketid` = t.`id`)
WHERE s.`sendto_id` =1