Sql statement return with zero result

Question

I am trying to choose the row where

1)list.ispublic = 1
2)userlist.userid='aaa' AND userlist.listid=list.listid

I need 1)+2)

There is a row already but this statement can not get that row, is there any problem?

List table:

ListID  ListName    Creator IsRemindSub IsRemindUnSub   IsPublic    CreateDate  LastModified    Reminder
1       test2       aaa     0           0               1           2012-03-09  NULL            NULL

user_list table (No row):

UserID ListID UserRights

My test version

SELECT * 
FROM list l 
INNER JOIN user_list ul ON ul.ListID = l.ListID 
WHERE l.IsPublic = 1 AND ul.UserID = '09185346d'

This is the Result when there are two list in user_list has aaa , and one list is public in list, so will this cause double retrieve of that one public list in list if i get it in php ?

ListID    ListName    Creator    IsRemindSub    IsRemindUnSub    IsPublic    CreateDate    LastModified    Reminder    UserID    ListID    UserRights
1         test2       aaa        0              0                1           2012-03-09    NULL                        aaa       1         read
2         t2          aaa        0              0                1           2012-03-09    NULL                        aaa       2         read

Implicit (Oracle style) joins are "inner" joins.

Marcus Adams
– Marcus Adams

2012-03-08 18:28:34 +00:00
Commented Mar 8, 2012 at 18:28 — Marcus Adams
– Marcus Adams, Commented Mar 8, 2012 at 18:28

Mark Byers · Accepted Answer · 2012-03-08 18:23:43Z

1

If your user_list table contains no rows, that query will return no results.

You want a LEFT JOIN.

SELECT list.col_1, list.col_2 -- etc...
FROM list
LEFT JOIN user_list ON user_list.ListID = List.ListID
WHERE list.IsPublic = 1  
   OR user_list.UserID = 'aaa'

answered Mar 8, 2012 at 18:23

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WWW Over a year ago

He wants an INNER JOIN according to his specs above: "userlist.userid='aaa' AND userlist.listid=list.listid". The fact that the list ID's have to match would indicate an inner join.

Marcus Adams Over a year ago

I think the question is flawed. :)

WWW · Accepted Answer · 2012-03-08 18:56:16Z

0

First, try to only SELECT the fields you actually need. Second, write your JOINS explicitly - it helps readability. For example:

SELECT l.*, ul.*
FROM list l
INNER JOIN user_list ul ON ul.ListID = l.ListID
WHERE l.IsPublic = 1
  AND ul.UserID = 'aaa'

Incidentally, if you have no data in your user_list table, then you have no way to meet the requirements you have set. If as you put it "userlist.listid=list.listid" is necessary and the user_list table is empty, you will always get zero rows returned.

Edit: And no, it won't cause rows to be retrieved double. Whatever results you get with your SQL query will be the same results you get in a PHP script - the mechanism for retrieving the data is the same.

edited Mar 8, 2012 at 18:56

answered Mar 8, 2012 at 18:28

WWW

9,8782 gold badges32 silver badges33 bronze badges

1 Comment

WWW Over a year ago

@user782104: I updated my answer. If you're adding any more data to your initial question, please don't copy/paste tabs into the question as it doesn't display correctly.

Collectives™ on Stack Overflow

Sql statement return with zero result

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related