[SOLVED] MySQL join help

[rucia]

Hi There,

 

I'm helping a friend who has two databases, each DB has a table of the same name.  So in DB1 there is a table T1 and in DB2 there is also a T1 table.  There is a RecID in each table.  Both tables record different information.  So a RecID might exist in both tables or exist in one but not the other.  With me so far?

 

Table 1 in Database 1 is recording Clicks and Table 1 in Database 2 is recording Reads.

 

I know its odd  - but we need to get some stats for the time being until i can rearrange his tables into an actual sane order.

 

What i want to do is count the total Clicks and toatal reads in one query.  So i want to exclude nulls from the count on both columns and i don't seem to be able to do this - here is the query so far:

 

SELECT count(db1.click) AS TotClicks, count(db2.read) As TotReads
FROM all.fstr01 AS db1 INNER JOIN stats.fstr01 AS db2 ON db1.recid=db2.recid 
WHERE (db1.click>0 OR db2.read>0)

 

Any help much appreciated 

 

This will get the totals, but we'd also like to get unique counts as well - so have four columns with counts of total and unique clicks and total and unique reads.

 

Can this be done with one query?

 

[DEVILofDARKNESS]

Maybe this helps:

$query = "SELECT COUNT(all.fstr01.click) AS TotClicks, COUNT(stats.fstr01.read) AS TotReads FROM all.fstr01 INNER JOIN stats.fstr01 ON all.fstr01.recid = stats.fstr01.recid WHERE (all.fstr01.click>0 OR stats.fstr01.read>0)";

[rucia]

Thanks.  But that produces the same result it shows 86 total clicks and 3175 total reads.

 

Whereas i know the total clicks are only 35 and the total reads are 1583.

 

I understand that rows that do not have a match in the ON clause will have a null value in the result set.  but i want to count everything except for nulls on both columns.

 

[DEVILofDARKNESS]

With the >0 you will still count null,

I would try

... WHERE stats.fstr01.read IS NOT NULL

[rucia]

Unfortunately that still produces 86 total clicks and 3175 total reads i also tried this:

 

WHERE (all.fstr01.click IS NOT NULL OR stats.fstr01.read IS NOT NULL)

 

And that still gives the same incorrect figures 

 

[DEVILofDARKNESS]

And you are really sure it isn't that value?

 

btw why are you doing them together in one query?

 

Can't you just try it out with two different queries

[rucia]

To preserve sanity i started out with two queries and thats where i got the figures from:

35 total clicks using

select count(recid) from all.fstr01 where click>0

 

and then 1583 total reads using

select count(recid) from stats.fstr01 where read>0

[DEVILofDARKNESS]

I would stick with 2 queries instead of 1, if nobody else can answer this topic...

[fenway]

You'll need sub-queries for this.

[rucia]

OK thanks. I will research into sub queries - haven't used these before.

 

I assume i'll be able to have two fields (one from each table) in the returned result set?

[kickstart]

Hi

 

Do you have a full list of recids on a seperate table? Do you want the count split down by recids?

 

This should give you the basic data, but would not make a good basis if you want the counts to be further split down (ie, with no field specified to join the 2 subselects together, if either bring bck multiple records then a massive number of records will be returned):-

 

SELECT a.RecIdCnt AS clickCnt, b.RecIdCnt as readCnt

FROM (SELECT COUNT(recid) AS RecIdCnt FROM all.fstr01 WHERE click>0 AND click IS NOT NULL) a,

(SELECT COUNT(recid) AS RecIdCnt FROM stats.fstr01 WHERE read>0 AND read IS NOT NULL) b

 

All the best

 

Keith

[fenway]

Hmm... I would have thought you'd use those in the column list?  Not that it really matters in this case.

[rucia]

Hi Keith, the query you provided was exactly what i was aiming for!  Thanks so much  .  I had a look at sub selects, but obvisouly didn't get as far as being able to do something like the query you provided.  Ta

 

thanks also to fenway and DEVILofDARKNESS for you time and assistance