CODEWITHSUNDEEP
mysqlsqldatabase
mrphrozenphoenix
mrphrozenphoenix

Reputation: 37

SELECT query on multiple table

I have two tables,

ID  NAME
-------------------
12  Jon Doe
4   Jane Doe
9   Sam Doe

AND

MemID   Cat#            DateChkOut      DateDue          DateRet
4   T 430.98 1956      15-Mar-2011      14-Jun-2011     31-May-2011
12  B 125.2 2013       15-Mar-2011      14-Jun-2011 
4   T 430.98 1956      27-Dec-2012      25-Mar-2013

Now I need to list the `members’ names who have never checked out a book.

SELECT Name FROM MEMBER, CHECKOUT WHERE ID != MemID;

did not work. Any suggestions?

Upvotes: 1

Views: 95

Answers (8)

Siyual
Siyual

Reputation: 16917

You need to use a LEFT JOIN in this case.

Try the following:

SELECT M.Name
FROM MembersTable M
LEFT JOIN CheckoutTable C On C.MemId = M.ID
WHERE C.MemId IS NULL

Edit:

LEFT JOIN works in this situation because it joins the two tables on a common key - in this case the MemId. Since this is a LEFT JOIN it will take everything that exists in the Left table (Members) and if a match is found, it will include everything on the Right table (Checkout). But if no match is found in the Checkout table, everything on that side will be NULL.

So, all you would need to do is check to see if the Right side is NULL.

Hope this makes sense :)

Upvotes: 1

RoughPlace
RoughPlace

Reputation: 1121

SELECT id,
       name
FROM Member
WHERE id NOT IN
    (SELECT memid
     FROM checkout)

or

SELECT id,
       name
FROM member m
LEFT OUTER JOIN checkout c ON m.id = c.memid
WHERE c.memid IS NULL

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156958

You have to use a where in and check for the DateChkOut:

SELECT Name
FROM   MEMBER
WHERE  ID not in
       ( select MemID
         from   CHECKOUT
         where  DateChkOut is not null
       )

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18737

Try this:

SELECT M.ID,M.Name 
FROM MEMBER M LEFT JOIN
     CHECKOUT C ON C.MemID=M.ID
WHERE C.DateRet IS NULL

It will select the user details whose DateRet is null.

Sample result:

ID  NAME
-------------------
12  Jon Doe
4   Jane Doe

Upvotes: 1

xQbert
xQbert

Reputation: 35323

Because you may want more fields returned an outer join is good method of accomplishing this.

SELECT Name 
FROM MEMBER M
LEFT JOIN CHECKOUT C
 on M.ID = C.MemID
WHERE C.MemID is null;

Upvotes: 1

Saurabh
Saurabh

Reputation: 73609

If you use NOT IN in the query, that can be slow for large queries, try instead following using JOIN, which will be way faster:

SELECT MEMBER.NAME FROM MEMBER 
LEFT JOIN CHECKOUT ON CHECKOUT.MemID = MEMBER.ID 
WHERE CHECKOUT.MemID IS NULL:

Upvotes: 1

Karl Kieninger
Karl Kieninger

Reputation: 9129

Use a correlated subquery with NOT EXISTS.

SELECT Name 
  FROM MEMBER a
 WHERE NOT EXISTS(SELECT 1 FROM CHECKOUT b WHERE b.MemID = a.ID)

See Optimizing Subqueries with EXISTS Strategy.

Upvotes: 1

mkubacki
mkubacki

Reputation: 585

SELECT name FROM member WHERE id NOT IN (SELECT MemID FROM checkout)

I think it should do the job

Upvotes: 1

Related Questions