php - 内连接和左连接

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);

video(存储视频详细信息的表)

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');

ticket(存储视频票证详细信息的表)

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);

sendticket(将票赠送给 friend 时存储详细信息的表)

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");

这里我希望获得票的详细信息、购买票的视频详细信息以及赠送我票的用户的详细信息，即 sendticket 中的 sendby_id em>表。目前我没有任何礼品票，所以它应该返回空记录。但它现在返回的记录与我的要求不符。

我知道左连接在这种情况下不起作用。我尝试使用 INNER JOIN 和 LEFT JOIN。但它没有用。谁能帮我解决这个问题？提前致谢。

Answer 1

您正在使用 ON() 子句(和 s.sendto_id=$userid)过滤您的记录，这将只为表过滤记录，即在正确的位置它不会过滤整个结果集，您得到的结果是由于您使用的是 LEFT 连接，对于您关注的结果集，您需要使用 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

one thing is confusing regarding your table structure for sendticket ,i saw values in column sendto_id i.e(10 ,sdasd@fjjgf.com) you are saving emails and ids which i guess is not a good idea ,also if you are getting the user ids from request then i recommend you to use codeigniter's Active record library

根据评论编辑

I want to get the tickets which are purchased by me and not yet gifted to any other and also those tickets that are gifted to me in a single query

下面是两种方式，但两种解决方案都使用 UNION

NOT EXISTS 的解决方案 1

第一个查询只是简单地获取id为1的用户购买的门票，并且没有来自sendticket的记录，其中sendby_id来自同一用户，union之后的查询是赠送那些赠送给该用户的门票

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