prevent duplicate record returning from query
Results 1 to 15 of 15

Thread: prevent duplicate record returning from query

  1. #1
    Member
    Join Date
    Mar 2013
    Posts
    32

    prevent duplicate record returning from query

    Hi all,

    I have the following tables "PH001_Valid" this table holds static data (it never changes).

    Then I have "PH001_House", this table is dynamic and new records are added all the time. In both tables there is a field named "Room" which links the tables together using an Left Outer Join. "PH001_House" also has a field which contains the date and time the record was inserted.

    Because the "PH001_House" table can hold multiple records with the same room number the query returns duplicates. Is there a way I can return all the records from "PH001_Valid" and only the latest record from "PH001_House"


    PHP Code:

    SELECT DISTINCT
    PH001_House
    .RecordID,
    PH001_House.Room,
    PH001_House.FaultCode,
    PH001_House.FaultDesc,
    PH001_House.UserNameF,
    PH001_House.UserNameL,
    PH001_House.`Date`,
    PH001_House.AttendedTime,
    PH001_House.FinishedTime,
    PH001_House.AttendedBy,
    PH001_House.ClearedTime,
    PH001_House.ClearedBy,
    PH001_House.RoomStatus,
    PH001_House.Active,
    PH001_Valid.Room
    FROM
    PH001_Valid
    Left Outer Join PH001_House ON PH001_House
    .Room PH001_Valid.Room
    ORDER BY PH001_Valid
    .Room ASC 
    Any advice would be great.

    Many thanks in advance,

    Blackbox

  2. #2
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,723
    Probably not too efficient, but if the ON clause contained a subquery?

    Code:
    ON PH001_Valid.Room = (select PH001_House.Room from PH001_House order by PH001_House.RecordID DESC Limit 1) ORDER BY PH001_Valid.Room ASC
    Not tested; dunno if it will work ...
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  3. #3
    Member
    Join Date
    Mar 2013
    Posts
    32
    Quote Originally Posted by dalecosp View Post
    Probably not too efficient, but if the ON clause contained a subquery?

    Code:
    ON PH001_Valid.Room = (select PH001_House.Room from PH001_House order by PH001_House.RecordID DESC Limit 1) ORDER BY PH001_Valid.Room ASC
    Not tested; dunno if it will work ...
    Hi,

    I have tried this below but the result is no records:

    PHP Code:
    SELECT DISTINCT
    PH001_House
    .RecordID,
    PH001_House.Room,
    PH001_House.FaultCode,
    PH001_House.FaultDesc,
    PH001_House.UserNameF,
    PH001_House.UserNameL,
    PH001_House.`Date`,
    PH001_House.AttendedTime,
    PH001_House.FinishedTime,
    PH001_House.AttendedBy,
    PH001_House.ClearedTime,
    PH001_House.ClearedBy,
    PH001_House.RoomStatus,
    PH001_House.Active,
    PH001_Valid.Room
    FROM
    PH001_Valid
    Left Outer Join PH001_House ON PH001_House
    .Room PH001_Valid.Room
    WHERE PH001_House
    .`Date`= (select PH001_House.Room from PH001_House order by PH001_House.RecordID DESC Limit 1ORDER BY PH001_Valid.Room ASC 

    I have tried all ways to do this but so far not the right one.

    Cheers for your reply.

    Blackbox

  4. #4
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,787
    WHERE PH001_House.`Date`= (select PH001_House.Room from PH001_House order by PH001_House.RecordID DESC Limit 1)
    Do you really expect data in a column named 'Date' to match data in a column named 'Room' ?

    Also, if any column is different distinct will consider it a unique row even if you don't. Maybe you're looking for something more like a GROUP BY clause? Can you give an example of a return set that is unexpected (it has 'duplicates')?
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  5. #5
    Member
    Join Date
    Mar 2013
    Posts
    32
    Quote Originally Posted by Derokorian View Post
    Do you really expect data in a column named 'Date' to match data in a column named 'Room' ?

    Also, if any column is different distinct will consider it a unique row even if you don't. Maybe you're looking for something more like a GROUP BY clause? Can you give an example of a return set that is unexpected (it has 'duplicates')?
    Hi,

    I have changed the field name from "Date" to "InsertDate".

    Running my original query it returns the following:

    PHP Code:
    SELECT DISTINCT 
    PH001_House
    .RecordID
    PH001_House.Room
    PH001_House.FaultCode
    PH001_House.FaultDesc
    PH001_House.UserNameF
    PH001_House.UserNameL
    PH001_House.InsertDate,
    PH001_House.AttendedTime
    PH001_House.FinishedTime
    PH001_House.AttendedBy
    PH001_House.ClearedTime
    PH001_House.ClearedBy
    PH001_House.RoomStatus
    PH001_House.Active
    PH001_Valid.Room 
    FROM 
    PH001_Valid 
    Left Outer Join PH001_House ON PH001_House
    .Room PH001_Valid.Room 
    ORDER BY PH001_Valid
    .Room ASC 
    -----------------------------------------------------------------------
    | RecordID | PH001_House.Room | FaultCode | FaultDesc |
    |-----------------------------------------------------------------|
    | 114 ----- | 1105 ------------------| 2 -----------|------------|
    |-----------------------------------------------------------------|
    | 102 ----- | 1105 ------------------|21 ----------|------------|
    |-----------------------------------------------------------------|
    | 101 ----- | 1105 ------------------| 4 -----------|------------|
    ------------------------------------------------------------------|

    The above is the best I can do to display the return I am getting.

    Many thank for your time.

    Blackbox

  6. #6
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,665
    Quote Originally Posted by Derokorian View Post
    if any column is different distinct will consider it a unique row even if you don't
    Quote Originally Posted by blackbox View Post
    Code:
    ---------------------------------------------------------------
    | RecordID   |  PH001_House.Room  |  FaultCode  |  FaultDesc  |
    |-------------------------------------------------------------|
    |  114  ----- |    1105 ------------------| 2 -----------|----|                            
    |-------------------------------------------------------------|
    |  102  ----- |    1105 ------------------|21 ----------|-----|                             
    |-------------------------------------------------------------|
    |  101  ----- |    1105 ------------------| 4 -----------|----|                            
    --------------------------------------------------------------|
    FaultCodes: 2, 21, 4 are NOT the same. The rows are not identical => they are all distinct from the others

  7. #7
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,665
    Quote Originally Posted by blackbox View Post
    Is there a way I can return all the records from "PH001_Valid" and only the latest record from "PH001_House"
    Something along the lines of
    Code:
    SELECT stuff
    FROM PH001 AS p
    LEFT OUTER JOIN PH001 AS pmax ON p.recordid = pmax.recordid AND  pmax.insertdate > p.insertdate
    INNER JOIN PH001_VALID AS pv ON pv.room = p.room
    WHERE pmax.insertdate IS NULL

  8. #8
    Member
    Join Date
    Mar 2013
    Posts
    32
    Hi Johnafm,

    Many thanks for taking the time to look at this.

    I have used your code but the result is, only records where the room is equal. The query does not show all the rooms from the PH001_Valid table and shows duplicates from the PH001_house table.

    Your code with a little change on table names:
    PHP Code:
    SELECT *
    FROM PH001_House AS p
    LEFT OUTER JOIN PH001_House 
    AS pmax ON p.RecordID pmax.RecordID AND  pmax.InsertDate p.InsertDate
    INNER JOIN PH001_Valid 
    AS pv ON pv.Room p.Room
    WHERE pmax
    .InsertDate IS NULL 
    Is there anythng I am doing wrong?

    Again, many thanks for your time.

    Cheers,

    Blackbox

  9. #9
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,901
    I could be way off base here, but a LEFT OUTER JOIN may or may not be what you are actually after. I'd recommend reviewing joins and then maybe focusing more specifically on your query. It seems to me that you are unclear what fields connect between the different tables and what the query actually means. Having skimmed this post, I haven't seen any clear description of what the rooms are or what the query is supposed to accomplish or what the data fields are.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  10. #10
    Member
    Join Date
    Mar 2013
    Posts
    32
    Hi sneakyimp,

    Many thanks for your input.

    The two field that are prescent in both tables is named "Room". In table "PH001_Valid" hold 200 room numbers, the data never decreases or increases in the number of rows.

    Table "PH001_House" holds data which is inserted by the user on a daily basis. The object of the query is to display all the records from the "PH001_Valid" table in a grid pattern, this part works.

    In the "PH001_House" table I have records containing room number, each record also has a status code. As an example, if the status code of room number 100 is 11, on the grid display it would be displayed in a different colour background, this works fine.

    Problem example: if room number 100 is inserted in to the PH001_House table and inserted again later making two instances of the same room number both with different status code then the grid display show the same room number twice. what I want the query to to is only bring back the record with the latest date.

    I hope I have explained this better than before.

    Many thanks for any help you or others can provide.

    Blackbox

  11. #11
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,787
    so you probably want some kind of subquery, along the lines of:
    Code:
    SELECT * 
    FROM house h
    JOIN valid v ON v.room = h.room
    WHERE h.date = (SELECT MAX(date) FROM house where id=h.id)
    Its psuedo'd code, but see if you can't modify it to your needs.
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  12. #12
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,901
    Yeah if you only want one date to be displayed, your primary query should specify what date you want to look at.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

  13. #13
    Member
    Join Date
    Mar 2013
    Posts
    32
    Hi all,

    Again I would like to thank you all for the time spent looking at my issue.

    I have attatched an image showing what I am trying to do along with some text and the query that returns the result.

    The normal state of table PH001_Valid never changes, does not have any new records inserted. if no records are in table PH001_House all the grid cells are white.

    If a new record is inserted in to table PH001_house then the appropriate cell truns "red", this work fine. If at a later time another record is inserted in to table PH001_House with the same room number two cells are displayed with the same room number.

    What I am trying to do is display the last record by "InsertDate" and not both the room numbers while still displaying all the records from the PH001_Valid table. My image should show you this in a better way.

    PH001_grid.jpg

    Many thanks again for all your time and input.

    I am beginning to think that this is not possible because I have tried so many ways of changing the query.

    Regards,

    Blackbox

  14. #14
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,665
    Quote Originally Posted by blackbox View Post
    The query does not show all the rooms from the PH001_Valid table and shows duplicates from the PH001_house table.
    Yes, the house tables should be joined on room_id, not record_id.

    Code:
    LEFT OUTER JOIN PH001_House AS pmax ON p.roomid = pmax.roomid AND  pmax.InsertDate > p.InsertDate
    [/QUOTE]

  15. #15
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,901
    Quote Originally Posted by blackbox View Post
    I have attatched an image showing what I am trying to do along with some text and the query that returns the result.
    That is helpful.

    Quote Originally Posted by blackbox View Post
    The normal state of table PH001_Valid never changes, does not have any new records inserted. if no records are in table PH001_House all the grid cells are white.
    OK so your JOIN should list PH001_Valid first I think. If you do this:
    Code:
    SELECT * FROM table_1 t1 LEFT JOIN table_2 t2 ON t2.column=t1.column
    Then you are going to get all the records in table 1 regardless of what records are in table 2. If there are no records in table 1, you'll get nothiSounds to me like you want to list PHP001_Valid first in your JOIN.

    Quote Originally Posted by blackbox View Post
    If a new record is inserted in to table PH001_house then the appropriate cell truns "red", this work fine. If at a later time another record is inserted in to table PH001_House with the same room number two cells are displayed with the same room number.
    As I said before, you need to adjust your query so that it would exclude that other record, probably based on the date. If your table can have more than one record per room for a given date, it sounds like you have a problem: it would be possible to double-book rooms.

    Quote Originally Posted by blackbox View Post
    I am beginning to think that this is not possible because I have tried so many ways of changing the query.
    It is certainly possible to show only one record per PH001_VALID record, but either your database needs to be inherently limited to one record per valid record OR you need to alter your query to limit the number of HOUSE records that can be considered for the JOIN, probably by altering your query to only search for a single date. If your system lets people enter multiple HOUSE records for each VALID record for a given date, then you probably have a problem.
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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