prevent duplicate record returning from query

blackbox

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"


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

dalecosp

Probably not too efficient, but if the ON clause contained a subquery?

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 ...

blackbox

dalecosp;11039151 wrote:
Probably not too efficient, but if the ON clause contained a subquery?
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:

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 1) ORDER 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

Derokorian

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')?

blackbox

Derokorian;11039157 wrote:
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:

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

johanafm

Derokorian;11039157 wrote:
if any column is different distinct will consider it a unique row even if you don't

blackbox;11039159 wrote:
[code]

| RecordID | PH001_House.Room | FaultCode | FaultDesc |
|-------------------------------------------------------------|
| 114 ----- | 1105 ------------------| 2 -----------|----|

|-------------------------------------------------------------|
| 102 ----- | 1105 ------------------|21 ----------|-----|

|-------------------------------------------------------------|
| 101 ----- | 1105 ------------------| 4 -----------|----|

--------------------------------------------------------------|
[/code]

FaultCodes: 2, 21, 4 are NOT the same. The rows are not identical => they are all distinct from the others

johanafm

blackbox;11039147 wrote:
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

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

blackbox

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:

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

sneakyimp

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.

blackbox

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

Derokorian

so you probably want some kind of subquery, along the lines of:

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.

sneakyimp

Yeah if you only want one date to be displayed, your primary query should specify what date you want to look at.

blackbox

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.

[ATTACH=CONFIG]5061[/ATTACH]

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

PH001_grid.jpg

johanafm

blackbox;11039175 wrote:
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.

LEFT OUTER JOIN PH001_House AS pmax ON p.roomid = pmax.roomid AND  pmax.InsertDate > p.InsertDate

[/QUOTE]

sneakyimp

blackbox;11039251 wrote:
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.

blackbox;11039251 wrote:
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:

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.

blackbox;11039251 wrote:
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.

blackbox;11039251 wrote:
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.