Jump to content


Photo

Getting Record from Two tables


  • Please log in to reply
5 replies to this topic

#1 arifsor

arifsor
  • Members
  • PipPipPip
  • Advanced Member
  • 50 posts
  • LocationPakistan

Posted 17 April 2006 - 08:21 AM

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


is this possible using only One query?



---------------------------
Power Demand Respect
Arif Sorathia

#2 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 17 April 2006 - 08:30 AM

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.* FROM Clinc a, Clicks b WHERE a.CLINIC_ID = b.CLINIC_ID [!--sql2--][/div][!--sql3--]

#3 arifsor

arifsor
  • Members
  • PipPipPip
  • Advanced Member
  • 50 posts
  • LocationPakistan

Posted 17 April 2006 - 09:14 AM

[!--quoteo(post=365476:date=Apr 17 2006, 03:30 AM:name=wildteen88)--][div class=\'quotetop\']QUOTE(wildteen88 @ Apr 17 2006, 03:30 AM) View Post[/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.* FROM Clinc a, Clicks b WHERE a.CLINIC_ID = 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

using only one query
---------------------------
Power Demand Respect
Arif Sorathia

#4 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 17 April 2006 - 09:39 AM

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, COUNT(b.CLINIC_ID), b.DATE_TIME FROM `CLINIC` a, `Clicks` b WHERE a.CLINIC_ID = b.CLINIC_ID GROUP BY a.CLINIC_NAME [!--sql2--][/div][!--sql3--]

#5 arifsor

arifsor
  • Members
  • PipPipPip
  • Advanced Member
  • 50 posts
  • LocationPakistan

Posted 17 April 2006 - 10:00 AM

[!--quoteo(post=365484:date=Apr 17 2006, 04:39 AM:name=wildteen88)--][div class=\'quotetop\']QUOTE(wildteen88 @ Apr 17 2006, 04:39 AM) View Post[/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, COUNT(b.CLINIC_ID), b.DATE_TIME FROM `CLINIC` a, `Clicks` b WHERE a.CLINIC_ID = 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
---------------------------
Power Demand Respect
Arif Sorathia

#6 arifsor

arifsor
  • Members
  • PipPipPip
  • Advanced Member
  • 50 posts
  • LocationPakistan

Posted 17 April 2006 - 12:29 PM

anybody can solve this
---------------------------
Power Demand Respect
Arif Sorathia




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users