Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: check a table to see whether it has nothing in it (was "Help with An SQL statement!")

    i am currently designing a study resource site for a college, as part of this site i have to design a personal calendar for the students to be able to insert diary entries and view them when the time is right.
    Here comes my problem.

    I need a statement that will check a database table to see whether it has nothing in it. I have a table with 3 fields that contain no data( username, day_id, message. I need my code to check that the teo fields username and day_id have no data in them. I need this so that i can insert data into that record when these two fields are empty and update the fields when there is data in them.

    Sorry for the longwinded message but i have been fussing over this all day and it has got more complicated by the hour.

  2. #2
    Join Date
    Aug 2005
    Posts
    140
    Im not sure if this is what you want:

    select count(*) from tablename where
    username is not null and day_id is not null;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    anyone who writes "An SQL statement!" instead of "A SQL statement!" surely deserves a reply

    under which circumstances will you find a row where both username and day_id are empty but message isn't? whose message would it be, and for which day?

    perhaps if you would kindly show us the CREATE TABLE statement that you used to create the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2003
    Location
    england
    Posts
    95
    Quote Originally Posted by r937
    anyone who writes "An SQL statement!" instead of "A SQL statement!" surely deserves a reply

    under which circumstances will you find a row where both username and day_id are empty but message isn't? whose message would it be, and for which day?

    perhaps if you would kindly show us the CREATE TABLE statement that you used to create the table?
    Basically, i wasnt searching the message field because the contents of it are irrelivant. I am running a check as to whether a record is in the database for that username and that day. Example username :gr203 example day_id: 1(monday)
    The check is necessary as it determines whether i need to run an insert or update statement. I cannot have two entries in the table for the same username and day hence if both are null then the entry does not already exist so insert the data, else there is already data for that entry so update the specified record.
    Thankyou both for your replies so far.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my point is: if the given user has no entry for the given day, there will not be a row with NULL in those two columns, there won't be a row at all!!

    see the difference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •