Getting Record from Two tables

[arifsor]

Hello

i have two tables Clinic, Clicks

Clinic
====
CLINIC_ID
CLINIC_NAME

Clicks
====
CLINIC_ID
DATE_TIME

whenever visitor of the website click on the clinic name name my program insert clinic_id and date_time intto the Clicks table.




Issue:
=====
i want to display the report

Clinic Name Clicks
=======================
abc clinic 54
xyz clinic 45


[b]is this possible using only One query?[/b]

[wildteen88]

I think this should do it:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.*, b.* [color=green]FROM[/color] [color=orange]Clinc[/color] a, Clicks b [color=green]WHERE[/color] a.CLINIC_ID [color=orange]=[/color] b.CLINIC_ID [!--sql2--][/div][!--sql3--]

[arifsor]

[!--quoteo(post=365476:date=Apr 17 2006, 03:30 AM:name=wildteen88)--][div class=\'quotetop\']QUOTE(wildteen88 @ Apr 17 2006, 03:30 AM) [snapback]365476[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I think this should do it:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.*, b.* [color=green]FROM[/color] [color=orange]Clinc[/color] a, Clicks b [color=green]WHERE[/color] a.CLINIC_ID [color=orange]=[/color] b.CLINIC_ID [!--sql2--][/div][!--sql3--]
[/quote]

No this will not work.
let me clear
following is the schema of tables


CLINIC

CLINIC_ID CLINIC_NAME
==================================
10 Abc Clinic
11 Xyz Clinic

CLICKS

CLICK_ID CLINIC_ID DATE_TIME
====================================
1 10 04-17-2006
2 10 04-17-2006
3 10 04-17-2006
4 11 04-17-2006


I WANT OUT PUT LIKE

CLINIC_NAME CLICKS
=======================
Abc Clinic 3
Xyz Cinic 1

[b]using only one query[/b]

[wildteen88]

Oh, sorry I missunderstood. My MySQL Query knowledge is little vague but I think something like thi should do it:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.CLINIC_ID, a.CLINIC_NAME, [color=blue]COUNT[/color](b.CLINIC_ID), b.DATE_TIME [color=green]FROM[/color] [color=orange]`CLINIC`[/color] a, `Clicks` b [color=green]WHERE[/color] a.CLINIC_ID [color=orange]=[/color] b.CLINIC_ID GROUP BY a.CLINIC_NAME [!--sql2--][/div][!--sql3--]

[arifsor]

[!--quoteo(post=365484:date=Apr 17 2006, 04:39 AM:name=wildteen88)--][div class=\'quotetop\']QUOTE(wildteen88 @ Apr 17 2006, 04:39 AM) [snapback]365484[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Oh, sorry I missunderstood. My MySQL Query knowledge is little vague but I think something like thi should do it:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.CLINIC_ID, a.CLINIC_NAME, [color=blue]COUNT[/color](b.CLINIC_ID), b.DATE_TIME [color=green]FROM[/color] [color=orange]`CLINIC`[/color] a, `Clicks` b [color=green]WHERE[/color] a.CLINIC_ID [color=orange]=[/color] b.CLINIC_ID GROUP BY a.CLINIC_NAME [!--sql2--][/div][!--sql3--]
[/quote]

it is workingm, but showing only those clinics whihc have some clicks, if there is no click on any clinic than it should display 0

[arifsor]

[b]anybody can solve this[/b]