Geek Answers Handbook

Select people who have a column value of 1 rather than 4

the question arises:

List the names of the cartridges who purchased the tickets in performance identifier 1, but not 4.

corresponding table 1) performance

SaleId > SaleTime > Total > PerformanceId > PatronId
1   2014-02-12 12:10:11 100.00  1   1
2   2014-02-12 12:10:11 40.00   1   2
3   2014-02-12 12:10:11 30.00   1   3
4   2014-02-12 12:10:11 30.00   1   null
5   2014-02-12 12:10:11 100.00  4   1
6   2014-02-12 12:10:11 40.00   4   5
7   2014-02-12 12:10:11 30.00   4   3
8   2014-02-12 12:10:11 30.00   4   null
9   2014-02-12 12:10:11 100.00  7   1
10  2014-02-12 12:10:11 40.00   7   4
11  2014-02-12 12:10:11 30.00   7   3
12  2014-02-12 12:10:11 30.00   7   null

2) Cartridge

PatronId > lastName > firstName

1   Paul      Smith
2   Linda     Odom
3   Gigi      Koo
4   Kailee    Jefferson
5   Kimberly  Heart
6   boyle     Heart
7   Kimberly  Beetle
8   boyle     Beetle
9   Joe       Junior
10  Jane      Junior
11  Junior    Junior
12  Kar       Kargoolie

my SQL code is:

select distinct lastName, firstName
from Patron p, ticketsale t1, ticketsale t2
where p.PatronId = t1.PatronId
and p.PatronId = t2.PatronId
and t1.PerformanceId = 1
and t2.PerformanceId <> 4;

This does not work as it contains 3 names (paul, linda, gigi) and I should only receive (Linda)

+4
source share
3 answers

Query

Using subqueries with EXISTSand vice versa (does not exist):

SELECT
  pt.lastname, pt.firstname
FROM
  patron pt
WHERE
  EXISTS ( 
           SELECT 1 
           FROM performance pf 
           WHERE pt.patronid = pf.patronid AND pf.performanceid = 1 
         )
  AND NOT EXISTS ( 
           SELECT 1 
           FROM performance pf 
           WHERE pt.patronid = pf.patronid AND pf.performanceid = 4 
         )

Testing

See a live example in SQL Fiddle

I use only those who are in your data samples in table performance (others are not needed to prove this):

create table patron(patronid int, lastname varchar(50), firstname varchar(50));
insert into patron values 
(1, 'Paul', 'Smith'),
(2, 'Linda', 'Odom'),
(3, 'Gigi', 'Koo'),
(4, 'Kailee', 'Jefferson'),
(5, 'Kimberly', 'Heart');

create table performance(performanceid int, patronid int);
insert into performance values
(1,1),(1,2),(1,3),(1,null),(4,1),(4,5),(4,3),(4,null),(7,1),(7,4),(7,3),(7,null);

My query returns:

lastname | firstname
---------+----------
Linda    | Odom
+2
source

Here you go:

SELECT firstName,
  lastName
FROM patron pat
INNER JOIN performance per1
  ON pat.PatronID = per1.PatronID AND per1.PerformanceID = 1
LEFT OUTER JOIN performance per2
  ON pat.PatronID = per2.PatronID AND per2.PerformanceID = 4
WHERE per2.PerformanceID IS NULL

ConsiderMe sqlfiddle. per2.PerformanceID , , , , 1, 7, ..

, , per1.PerformanceID per2.PerformanceID, , , , :

  • ( , )
  • , , ,
+2

, where

 select distinct p.lastName, p.firstName
 from Patron p
 inner join ticketsale as t2 (on p.PatronId = t2.PatronId and t2.PerformanceId = 1)
 where p.PatronId not in  (select distinct t1.PatronId 
 from ticketsale as t1  where t1.PerformanceId <> 4)
+1

More articles:


All Articles