5 Replies - 757 Views - Last Post: 01 October 2014 - 02:42 PM

#1 RandomlyKnighted   User is offline

  • D.I.C Lover
  • member icon

Reputation: 119
  • View blog
  • Posts: 1,384
  • Joined: 14-January 10

Getting data from tables when one table has no data on user?

Posted 01 October 2014 - 12:04 PM

I am trying to get information from 3 tables called usersInfo, session, and files. Natually usersInfo contains data about each user such as username, password (hash and salted of course), name, etc. Session contains the login and logout times of each user. Files contains a list of the uploaded files and the username of the user who uploaded the file. Using the query I've created below I'm able to get data for all users who have a uploaded a file but not for any users who have not uploaded a file.

SELECT ui.username, email, password, endtime, COUNT(owner)
FROM usersInfo ui JOIN session s
ON ui.username = s.username LEFT OUTER JOIN files f
ON ui.username = f.owner



All the information I'm trying to get from the files folder is a count of how many files the user has uploaded. I've been back and reviewed material from my database class last semester and I feel like I'm forgetting something really simple. Any suggestions?

Is This A Good Question/Topic? 0
  • +

Replies To: Getting data from tables when one table has no data on user?

#2 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2653
  • View blog
  • Posts: 7,869
  • Joined: 15-January 14

Re: Getting data from tables when one table has no data on user?

Posted 01 October 2014 - 12:21 PM

It looks like you're forgetting a group by clause.
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4271
  • View blog
  • Posts: 13,521
  • Joined: 08-June 10

Re: Getting data from tables when one table has no data on user?

Posted 01 October 2014 - 12:21 PM

COUNT() is an aggregate function (in this case) and thus needs an GROUP BY clause to work properly.

additionally, for the sake of good coding practice, all fields should be prefixed with their table name/alias. (Iíve done joins on more than a dozen tables Ö)
Was This Post Helpful? 0
  • +
  • -

#4 RandomlyKnighted   User is offline

  • D.I.C Lover
  • member icon

Reputation: 119
  • View blog
  • Posts: 1,384
  • Joined: 14-January 10

Re: Getting data from tables when one table has no data on user?

Posted 01 October 2014 - 12:26 PM

Like so Dormilich?

SELECT ui.username, ui.email, ui.password, s.endtime, COUNT(f.owner)
FROM usersInfo ui JOIN session s
ON ui.username = s.username LEFT OUTER JOIN files f
ON ui.username = f.owner
GROUP BY ui.username



Also is there anything else I might be missing? Sadly, I'm still not getting data for the user who has not uploaded a file to the application.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • a lovely bit of linq
  • member icon

Reputation: 6887
  • View blog
  • Posts: 28,493
  • Joined: 12-December 12

Re: Getting data from tables when one table has no data on user?

Posted 01 October 2014 - 02:33 PM

Does the user without an upload also not have any session details? Try two outer joins:
FROM usersInfo ui LEFT OUTER JOIN session s

Was This Post Helpful? 0
  • +
  • -

#6 RandomlyKnighted   User is offline

  • D.I.C Lover
  • member icon

Reputation: 119
  • View blog
  • Posts: 1,384
  • Joined: 14-January 10

Re: Getting data from tables when one table has no data on user?

Posted 01 October 2014 - 02:42 PM

Thanks! You were right, apparently the second user did not have any session details. Looks like I have a bug with storing that value.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1