Mysql select - certain rows with duplicate column

[gold1234]

Hi

 

I need some help with a query not sure if it's simple or not lol.

 

I have a table with is an association with another table.

 

The layout of this table is like so:

 

| assoc_id | person_id |

+---------+----------+

 

The assoc_id is not unique, it has multiples of the same Id.

 

What i want to do is select certain person_id's as long as the assoc_id is the same.

 

So for example:

 

I tried -

SELECT * FROM assoc WHERE person_id=3 AND person_id=5 AND person_id=10

 

However, even though those Ids exist, mysql returns 0 records found.

 

Also how can i do a query to find the records with the same assoc_id ?

 

All help is appreciated, please enlighten me! lol.

[Mchl]

You can't have a value be 3 AND 5 AND 10 at the same time.

 

Check this:

 

 

SELECT * FROM assoc WHERE person_id = 3 OR person_id = 5 OR person_id = 10

 

or even better

 

SELECT * FROM assoc WHERE person_id IN (3,5,10)

 

 

 

 

[gold1234]

Brilliant.  That did well, but what if i wanted the assoc_id to all match?  Or would php be the way to calculate it ... ?

[Mchl]

Could you give example of what results you'd expect?

[gold1234]

Sure,

 

 

| person_id | assoc_id |

+----------+---------+

| 2            |  3        |

| 2            |  7        |

| 2            |  9        |

 

 

And there should be no other assoc_ids with the person_id of 2.

 

Its quite a complex query i think?

[Mchl]

SELECT * FROM assoc WHERE assoc_id IN (3,7,9) AND person_id = 2

[gold1234]

But that's where it gets a bit more complicated.  Because when doing the query i wont know what the person ID is.

 

I want to get certain rows as long as the assoc_id match up.  And from there find out what the assoc id is.  So if i were to ask for person_id's 5,10,2,9,7 and in the assoc table those person_ids exist with the same assoc_id and no other person_id exists with the same assoc_id, then it should return as true with the person id.

 

Otherwise it be fault.

 

Too complex?

[Mchl]

<scratches his head>

 

Ok...

What data do you have at the beginning? One person_id? Several person_ids?

 

If for example you have person_id = 5, do you want to get all other person_ids with same assoc_id (2,7,9,10) ?

[gold1234]

This is where it gets confusing lol.

 

Im selecting certain person_ids, and i want to see if those person_ids have the same assoc_id or if there are multiple person_ids with the same assoc_ids.  But only making sure those person_ids have the same assoc_id and no other person_id within the database has the same assoc_id as well.

 

Its like a strict match up. 

 

Example:

 

I want to see if these person_ids have the same assoc_id, and no other row has the same assoc_id.

1,5,10,3,2

 

So i would do a query to get a list of assoc_ids with the above person_id's.

 

But i want to also make sure that if there is a match and there is the same assoc_id in the above person_ids, that no other row has the same assoc_id within the table.

 

Its like a strict search.  Hope it makes sense now? lol

[Mchl]

Yeah

 

I'm just wondering if you can do this with one query....

 

Still thinking on it... it's wicked

[gold1234]

While your doing that lol... i'm thinking of this method but the load time may increase slightly and i'm a load time - less code enthusiast lol.

 

SELECT * FROM assoc WHERE person_id IN (......)

 

then return a result

 

LOOP RESULT

- See if there are other records within table with the same assoc_id

++ SELECT * FROM assoc WHERE assoc_id = ....

   

THEN COUNT RECORDS OF SAME assoc_id

 

THEN ARRAY COUNT of results return at first

 

SEE IF COUNTS MATCH, IF THEY DONT THEN REMOVE THESE RECORDS WITH THESE ASSOC

END LOOP RESULT

 

then check if there are still duplicate assoc_id results still, if there are, split array, then do a search within each array to see if they match up to the person_ids previously requested... then use that assoc_id otherwise return false....

 

damn.  thats too much?  this is on top of my head!! lol.

[Mchl]

I was thinking about something similar.

 

You could probably do both counts and compare them in one query with subqueries...

[gold1234]

I will have a look around for sub query examples... any examples you could share?  Thanks for your help btw, but i think we both come to the conclusion what needs to be done cannot be done in one simple query!

[Mchl]

How about this

 

SELECT person_id, assoc_id FROM assoc WHERE assoc_id IN (SELECT assoc_id FROM assoc WHERE person_ID IN (1,2,3));

[gold1234]

That just returns 0 results, i will also try the array method mentioned earlier.

[fenway]

Where are we now? Just catching up....

[gold1234]

Erm, not the best... heres what i have...

 

function get_person_id($elements)
{
	$query = $this->db->query("SELECT * FROM ".TBL_ASSOC." WHERE element_id IN (".implode(',',$elements).")");

	foreach($query->result() AS $element)
	{
		$assoc = $this->db->query("SELECT * FROM ".TBL_PERSON." WHERE person_id='".$element->person_id."'");

		if($assoc->num_rows() == count($elements))
		{
			foreach($elements AS $a => $eid)
			{
				$p = $this->db->query("SELECT * FROM ".TBL_ASSOC." 
				WHERE person_id='".$element->person_id."' AND element_id='".$eid."'");
				if($p->num_rows() == 0) return false;
			}
			return $element->person_id;
		}
	}	
	return false;
}

 

It's still returning false.

 

You do still remember what the objective was right? lol.

[fenway]

No, not really... but I see 3 selects than can be done with a single join.