joining with subquery

[laceysnr]

Hi guys, I've got a problem that's driving me nuts and have no idea of what's going on.

Essentially I've got a query which in the select clause includes a sub query which gets customer_ids with the earliest associated datetime, this is because each customer can have several entries and I am required to do some analysis on the data from the earliest entry for each one only.

So I do the subquery in select like: (select customer_id as custid, min(thetime) as custtime from thetable) as info ....

then in the WHERE clause I do:

WHERE table.customer_id = info.custid and table.thetime = info.custtime

I've run the subquery on it's own and it works as expected returning 11 rows out of a possible 16 (it's removed later duplicates). The main query also does it's job and returns all 16 (without the subquery check). If I put the WHERE clause in and join the two I only get 8 rows back!

I've checked the results of the two queries against each other and in the 16 from the main query are the 11 the sub query returns, they appear to be identical so I have no idea where they're going. If I change the where clause and just "where table.thetime in (info.custtime)" I seem to get 9 results :S

Is there some change in the data format between the table and the subquery results? Both queries operate on the same table, I really can't see where this is losing the other 3 rows.

 

[Bilbo_Baggins_Esq]

can you post a sample of this database?

 

[Rabbie]

Can you post the SQL for your queries and also the data in your table. It will be easier to explain what is happening if we can see the data

 

[laceysnr]

Unfortunately it's sensitive data, I'll try and create the same issue using random data today if I get the chance!

Thanks

 

[Rabbie]

Just reread your original post. There was one thing that puzzled me

So I do the subquery in select like: (select customer_id as custid, min(thetime) as custtime from thetable) as info ....

I was under the impression that you could only use a subquery to return a single field value but your one seems to be returning two fields.

 

[laceysnr]

I know if you do it as part of the WHERE clause using in then you can only return one field, I thought what I have was ok though.

I might be wrong, it's been a while since I last played with a database and that was Informix.

 

[laceysnr]

I've been having a play and have so far been unable to recreate the situation in a new mockup database... guess I'll just have to keep chipping away at it for now!

 

[Bilbo_Baggins_Esq]

That is a good sign that the cause of the issue is actually in the data and not in your structure.

Good Job!