|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
June 30th, 2009, 09:17 AM
|
|||
|
|||
|
Confused in writing a join for two tables
hi,
i have two tables, kwds and clks. clks PK is clid and it has other columns as (sales,se) kwds table has columns as (refkwd,clid(FK of clks table)) now what i want to do is count the number of 1 - distinct refkwds from kwds table 2 - number of total records in clks table based on kwds.clid=clks.clid 3 - number of total records based on kwds.clid=clks.clid AND WHERE clks.sale=1 i tried this query, Code:
select refkwds, count(c.clid) from clks c,kwds k where k.clid=c.clid group by refkwds but i am kind of suspicious about the results i am getting for their accuracy. for sales, i am thinking to execute a duplicate query as above but with addition of (AND sale=1) is this the right way to make this query or it can be different? thank you
|
|
#2
June 30th, 2009, 10:53 AM
|
||||
|
||||
|
Code:
SELECT kwds.refkwds
, COUNT(CASE WHEN clks.sale = 1
THEN 'count me! count me!'
ELSE NULL END) AS sale_clids
, COUNT(clks.clid) AS total_clids
FROM kwds
INNER
JOIN clks
ON clks.clid = kwds.clid
GROUP
BY kwds.refkwds
|
|
| Viewing: Dev Shed Forums > Databases > MySQL Help > Confused in writing a join for two tables |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
