"The two row problem"

[lunetics]

Hi!
I have a system for a dog competition and this is how it (should) works. When I add a new dog to the system it also creates two judge protocols. This two protocol has the same dog-ID in the database but is saved as two new rows. The both protocol must be passed by two different judges. Each protocol has an status column in the database. When a judge passes a protocol it will get the status = 1. When it´s done it will automatically showed on the secretary page. The problem is that I don´t want to show the protocol summary from the two judges until both of them has passes their protocol.

If the database look likes this I don´t want to show the protocol summary for dog_id = 12:


|----------------|
| dog_id | status|
|----------------|
| 12 | 1 |
|----------------|
| 12 | 0 |
|----------------|
But if the database look likes this I will show the protocol summary for dog_id = 12:


|----------------|
| dog_id | status|
|----------------|
| 12 | 1 |
|----------------|
| 12 | 1 |
|----------------|
dog_id only returns twice in the table because there are just two protocol. Hope my description is not that confused. :look:

Summary: I only want to show the summary list where both protocol - with the same dog_id - has status = 1.

[foodpk]

Could you list out the tables you have? I've got a feeling the database is not really built normalized and according to relational guidelines if you have to select data like that.

[eirche]

you should redesign the database. use fields dog_id, status_a, status_b

[bwh2]

i think the table structure is fine (assuming you also have judge_id column that you're not showing now).

this should work:


SELECT
dog_id,
status
FROM myTable
WHERE status = 1
GROUP BY
dog_id,
status
HAVING COUNT(*) = 2


but you should have a judge_id column as well, at which point i would modify the query:


SELECT
dog_id,
status
FROM myTable
WHERE status = 1
GROUP BY
dog_id,
status
HAVING COUNT(DISTINCT judge_id) = 2

[lunetics]

Have been thinking of other table layouts that should make this more easiy but "the easy way isn´t always the best way". Yes I have a judge_id. I´ll try this as soon as I can.

[lunetics]

Hello again! Yeeees it works perfect. Thanks bwh2 for your help and all others who answered this messy question. =)