Solved

Trouble with the coldfusion & mysql Code

Posted on 2010-08-22
66
337 Views
Last Modified: 2013-12-24
SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,
  COUNT(companycategories.catID) as Total
  FROM categories inner join companycategories on companycategories.catID = categories.categoryid
  where recently_searched LIKE "%#arguments.keyword#%"
  OR recently_searched LIKE %#arguments.searchwhere#%"
  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched  
  ORDER BY catorder

recently_searched is only existing in the categories table!

my counts are giving me incorrect results! i want that it shouw counts only if the value exists in the recently_searched
0
Comment
  • 33
  • 32
66 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33496170
i think there is quote missing or somthng.

secondly it requires single quotes(') not double quoets(")
SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,
  COUNT(companycategories.catID) as Total
  FROM categories inner join companycategories on companycategories.catID = categories.categoryid
  where recently_searched LIKE '%#arguments.keyword#%'
  OR recently_searched LIKE '%#arguments.searchwhere#%'
  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched  
  ORDER BY catorder

Open in new window

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33498359

Is it showing double the entries? try this

SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,
  COUNT(distinct companycategories.catID) as Total
  FROM categories inner join companycategories on companycategories.catID = categories.categoryid
  where recently_searched LIKE "%#arguments.keyword#%"
  OR recently_searched LIKE %#arguments.searchwhere#%"
  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched  
  ORDER BY catorder
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33503966
it is showing the counts as incorrect! i want that if certain tags exists in recently_searched it should only show that much counts not all counts for that specific category

currentky it is showing all the counts

please tell me if i am wrong!
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33504079
Ok...

do one thing.
bifurcate ur query.
first execute this part
SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,
  COUNT(companycategories.catID) as Total
  FROM categories inner join companycategories on companycategories.catID = categories.categoryid
  where recently_searched LIKE '%#arguments.keyword#%'
  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched  
  ORDER BY catorder

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33504096

after that this one.
there could be problem with OR condition.
u may getting extra records.

SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,
  COUNT(companycategories.catID) as Total
  FROM categories inner join companycategories on companycategories.catID = categories.categoryid
  where recently_searched LIKE '%#arguments.searchwhere#%'
  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched  
  ORDER BY catorder

Open in new window

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33507888
u mean to say one query and QoQ after it of two queries and how do i fetch the results!
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33508372

Actually its seems that your OR condition making duplicate records.

where recently_searched LIKE '%#arguments.keyword#%'  
  OR recently_searched LIKE '%#arguments.searchwhere#%'

For just debugging purpose split your query so you can get some idea whats wrong going.

One more possibilities is that "companycategories" table may contain many rows against one categoryid.

u can put distinct also to getting distinct count.



SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,  

  COUNT(distinct companycategories.catID) as Total  

  FROM categories inner join companycategories on companycategories.catID = categories.categoryid  

  where recently_searched LIKE '%#arguments.keyword#%'  

  OR recently_searched LIKE '%#arguments.searchwhere#%'  

  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched    

  ORDER BY catorder

Open in new window

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33511300
same issue is being here, it is not using the companycategories table but the things are same like messy here

SELECT COUNT(distinct companies.companyID) as total, province.provinceName,
    companies.provinceid, province.province_id, province.recently_searched  
    from province
    left join companies ON companies.provinceid = province.province_id
    WHERE province.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
    OR province.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchwhere#%">)
    group by province.province_id, province.provinceName,companies.provinceid
    order by province.provinceName

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33511381
distincr of categor is working!

you may ask for a new question to be opened for a new set of issue i am pating here but believe me i am so much jeprpardy that i am worried to be solved it asap as we have made a rhythm for the issue we are already solving
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33513979

No....

Its not a matter of opening new Q,matter is solving ur problem.
from your answer,i guess u get partially resolved.
Pleaase just tell me your problem again.
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33514047
in my second last query, i am now binding the province with the companies, the recently_searched is added in provinces also! so wanna make the counts correct again if the value exists in the company and in that particlular province! i hope u understand

if it exists in both then it should count only
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33517756
hi

we will go step by step. ok?

run following query and tell me u getting right count.

SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,  
  COUNT(distinct companycategories.catID) as Total  
  FROM categories inner join companycategories on companycategories.catID = categories.categoryid  
  
  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched    
  ORDER BY catorder

Open in new window

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33548260
Pleasefind the attached file, here
Untitled-1.png
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33548374
Hi,

Good Morning.

Whats the result u expecting,pls decribe me that.
So its better to produce.
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33548396
Have to see we search for a results and if we furthur need to drill down the results we use the word refine you search! th exactly is my case

we take it as a three part

Phase 1:

Categories/Subcategories!

1) if search anything it will search everywhere in the categories, comapnies table and return results!
    1.1) Suppose in the Category, there comes up as 3 records!
       1.1.) i again enter search criteria in the the same page and it will furthur filter the results from that specific category and if the keyword exists in that one category of the three, it will display the results and the count to 1 only

This is above case with categoriues/subcategories

once we get thrugh it we will move next to provinces!

recnetly_searched exists in categories table! and when i enter company details, i select category, it automatically fetches the detail of recently_searched from the categorie

i hop i made my points clear

let me know if we neeed database structure here

regards
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33548418

Soryy

I partially understand ur scenario.
Your problem is " it will display the results and the count to 1 only" line?

No Database stuructre is nt required, but pls explain with example or share excel file.

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33548452
ok i give u one website example, looking the same like that one

http://www.yellowpages.co.za
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33548515
No no...


I understud ur flow.
But i m not able to pin point the exact issue u finding.

I mean what output u r expecting,what is coming.
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33549716
let us make it simple we do a search and we get results, let's make it just like the url i gave you
refine you search

means search within the search results

lets make it that way

i think removing distinct can clear the counts to as it were before and will appear ok




0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33549854

Hi......

I'm already clear what u wanted.
your question is

my counts are giving me incorrect results! i want that it show counts only if the value exists in the recently_searched

so i just want to know which records are including/excluding?

coming back to your cfdump part.
just pin point me your exact row where u thinking count is coming wrong.
as according to query,everything is seems to be ok.


Untitled-1.png
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33549957
u see in cfdump if the value is empty value it still shows NULL
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33549960
Hi Just read the above line of  >> u see in cfdump if the value is empty value it still shows NULL
as

u see in cfdump if the value is empty value it still shows 1 as total, this is where it is coming wrong
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33549987

got it.

see count will always display value.

you can avoid it by
SELECT categories.category,categories.catdepth,categories.categoryID, recently_searched,  
case when IsNull(recently_searched,'') != '' then COUNT(distinct companycategories.catID) else 0 end as Total  
  FROM categories inner join companycategories on companycategories.catID = categories.categoryid  
 
  Group by categories.categoryid,categories.category,catorder,catdepth, recently_searched    
  ORDER BY catorder
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33550403
k i will see in morning it too late here gotta sleep

regards
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33550408
Okies

Sweet Dreams =)


- Bhavesh(Brichsoft)
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33552313
ok for the categories, it worked! it now does not display other categories which do not have value! ok great

now i have another query which is based on provinces:

SELECT COUNT(companies.companyID) as total, towns.town,
    companies.townid, towns.town_id
    from towns  
    left join companies ON companies.townid = towns.town_id
    WHERE towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
    OR towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchwhere#%">)
    group by towns.town_id, towns.town,companies.townid
    order by towns.town

Applying diustinct makes no and still it displays all counts!

how we can specify same here

if u insist i open new question
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33552777

Randhwa,

Its good you found one answer.
Regarding opening new Question,well I m using EE extensively from last 3 months,but i didnt read EE policy.

In case if we are not violating any EE Policies then I dont mind to continue here.

Back to ur query,it seems there is problem in join.

SELECT COUNT(companies.companyID) as total, towns.town,
    companies.townid, towns.town_id
    from towns  
    left join companies ON companies.townid = towns.town_id
    WHERE towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
    OR towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchwhere#%">)
    group by towns.town_id, towns.town,companies.townid
    order by towns.town

query containing left join and trying to take count from companies table.

You want all company count with towns id?
I changed table name.pls now check.

SELECT COUNT(companies.companyID) as total, towns.town, 
    companies.townid, towns.town_id 
    from companies 
    left join towns ON companies.townid = towns.town_id 
    and towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
    OR towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchwhere#%">)
    group by towns.town_id, towns.town,companies.townid 
    order by towns.town

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33552780
sory not table name, table position
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33552871
Thanks But i know we are not voilating EE Policies, the Counts are going wrong! i cliked the Search and then again Click Refine the search to search from the searched results, but it is showing the count wong again

u want something i should show up like table structure etc
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33552972
not table structure,but show me dump and tell which records are wrongly coming
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33553077
ok i searched for history and history only exists in town table in two fields!

harare and buywayo

while it exists in the companies table too..

so it should display the count for the hara enad buyewayo towns only and if wanna show others, show as zero for them or even hide them

the image is attached
Untitled.png
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33553246
can we use inner join?
SELECT COUNT(companies.companyID) as total, towns.town, 
    companies.townid, towns.town_id 
    from companies 
    inner join towns ON companies.townid = towns.town_id 
    and towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
    OR towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchwhere#%">)
    group by towns.town_id, towns.town,companies.townid 
    order by towns.town

Open in new window

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33553712
definately, whatever solve our purpose we can use even a subquery if that has to be followed. in the meantime i tried ur code
and it made no effet on the previous one
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33553807
Ok..

So according to your dump, 2nd and 4th row should come.

right?
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33553835
only thos records should come where the recently_searched item exists in the town table that's is my concern

becvayse when i will be adding the company, on selction of any towm or province it will automatically add the recently_searched item of the company table!

so here we are concerned only for the two towns because history i searched for is appearing in both the buyowayo and hahare towns and counts are coming wrong
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33553884

sir

do one thing.
show me dump of below query.
SELECT COUNT(companies.companyID) as total, towns.town, 
    companies.townid, towns.town_id,towns.recently_searched
    from companies 
    left outer join towns ON companies.townid = towns.town_id 
    and towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
    OR towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchwhere#%">)
    group by towns.town_id, towns.town,companies.townid,towns.recently_searched
    order by towns.town

Open in new window

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33553909
ok file attached
Untitled.png
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33553955
sir,

according to dump ur 2nd and 3rd row should come.

right??
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33555703
yes but counts should be shown correct!
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33556665

what value should come?
inner join should work as town id both are same.
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33560309
no its not working the above attached shows same result for inner or left join
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33561038

ok.
pls share companies & town table data.

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33564123
ok will do it tonite
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33609148
Ok Here is the Structure of my Both Tables!
 Sorry for a late Reply

Companies Table:

CREATE TABLE [dbo].[companies](
      [companyid] [int] IDENTITY(1,1) NOT NULL,
      [cname] [varchar](max) NULL,
      [homeurl] [nvarchar](255) NULL,
      [emailaddress] [nvarchar](255) NULL,
      [building] [nvarchar](255) NULL,
      [street] [nvarchar](255) NULL,
      [countryid] [int] NULL,
      [provinceid] [int] NULL,
      [townid] [int] NULL,
      [suburbid] [int] NULL,
      [postal1] [varchar](max) NULL,
      [postal2] [varchar](max) NULL,
      [telephone] [nvarchar](255) NULL,
      [fax] [nvarchar](255) NULL,
      [companycellphone] [nvarchar](255) NULL,
      [description1] [text] NULL,
      [companylogo] [nvarchar](255) NULL,
      [status] [int] NULL CONSTRAINT [DF_companies_status]  DEFAULT ((0)),
      [user_id] [int] NULL,
      [visits] [int] NULL,
      [makePremium] [int] NULL CONSTRAINT [DF_companies_makePremium]  DEFAULT ((0)),
      [companyprofile] [text] NULL,
      [longitute] [float] NULL,
      [latitude] [float] NULL,
      [recently_searched] [nvarchar](max) NULL,
 CONSTRAINT [PK_companies] PRIMARY KEY CLUSTERED
(
      [companyid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


TOWNS Table:

CREATE TABLE [dbo].[towns](
      [town_id] [int] IDENTITY(1,1) NOT NULL,
      [town] [varchar](255) NULL,
      [dialingcode] [varchar](255) NULL,
      [provinceID] [int] NULL,
      [recently_searched] [varchar](max) NULL,
 CONSTRAINT [PK_locations] PRIMARY KEY CLUSTERED
(
      [town_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33609153
if a subquery can solve our issue, i have no trouble using or even a procedure
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33609260
Hi......

I need data actually.
can u share the data...
pls?
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33609311
hmm! ok here u go, its mysql generated data but u can get the idea,

i am using basically the MSSQL server, but generated data is mysql based! you can use it i hope, it is same no difference


Companies Data:

INSERT INTO `companies` (`companyid`, `cname`, `homeurl`, `emailaddress`, `building`, `street`, `countryid`, `provinceid`, `townid`, `suburbid`, `postal1`, `postal2`, `telephone`, `fax`, `companycellphone`, `description1`, `companylogo`, `status`, `user_id`, `visits`, `makePremium`, `companyprofile`, `longitute`, `latitude`, `recently_searched`) VALUES 
(1, 'asasdfsdfsdfd', NULL, 'sdf@fhj.lll', 'dfsdfsdfsdf', 'sdfsdfsdf', 196, 1, 2, 2, 'Test Randhawa This is Cool', 'Find YourSelf', '234234234234', '234234234', '2342342', '<p>\r\n	4sdfsdfsdfsdfsdfsdfsdf</p>\r\n', 'grandcanyon04_1024x768_thumbnail.jpg', 1, 17, 1, 10, '<p>\r\n	sdsdsddsdsdsdsd</p>\r\n<div firebugversion="1.5.4" id="_firebugConsole" style="display: none;">\r\n	&nbsp;</div>\r\n<br />\r\n', 74.871552, 31.63089, 'information'),
(5, 'world Inc', 'http://www.world.com', 'test@world.com', '79 asjdhdkasd\r\nasdasdadmnxzc\r\nasdasdgasd\r\nasdasdasd\r\nsss', 'dsfsfsfsfsdfsdf', 196, 1, 1, 1, 'dsfs', 'dfsdfsdfsdfsd', '23423423423', '234234234234', '2342343334', '<p>\r\n	sdfsdfsdfcvxvxcvxcvxcvxcvxv</p>\r\n', 'pla36v_thumbnail.jpg', 1, 21, 101, 20, '<p>\r\n	Gavy Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa Randhawa</p>\r\n<div firebugversion="1.4.5" id="_firebugConsole" style="display: none;">\r\n	&nbsp;</div>\r\n<br />\r\n', 31.053961, -17.82922, 'Business, Marketing,Arts, History, Books, Graduation,History, Yahoo, Google, Groups, Coldfusion, History,,'),
(9, 'Test2', 'http://www.wtar.com', 'test@test.com', 'This is really Nice', 'Testing testing', 196, 2, 1, 16, 'asdasda', 'sdasdasdasd', '23423423423', '234234234234', '2342343334', '<p>\r\n	This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two Thi</p>\r\n', 'buffalos01_1024x768_thumbnail.jpg', 1, 12, 21, 20, '<p>\r\n	This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two</p>\r\n', NULL, NULL, NULL);


Now Towns data

1	Harare	0183	2	harare, civilization,history
2	Bulawayo	0998	1	history, Civilization
3	Manicaland	3434	3	NULL
4	Mashonaland Central	4567	4	NULL
5	Mashonaland East	6661	5	NULL
6	Mashonaland West	090	6	NULL
7	Masvingo Province	888	7	NULL
8	Matabeleland North	098	8	NULL
9	Matabeleland South	123	9	NULL
10	Midlands Province	097	10	NULL

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33609703
sir,

tell m 1 thing,
in companies master townid having NULL value.?
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33609734
Default can be null, but we can assume value here so we get relevant data for our purpose!

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33610671
see.

according to latest png & query.
data shoule come properly.
do 1 thing.
just select one town where u not getting proper counts.
just display companies data for specific that town.

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33611169
you take any one assumption and u use that!

conside record 1 and 9 for this
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33612872

sory sir.

for late reply.
n we cnt make query based on assumption.
if u taking 9th record then,there recently_searched is null..
so u may do like
SELECT COUNT(companies.companyID) as total, towns.town, 
    companies.townid, towns.town_id,towns.recently_searched
    from companies 
    left outer join towns ON companies.townid = towns.town_id 
    and isnull(towns.recently_searched,'') LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
    OR isnull(towns.recently_searched,'') LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchwhere#%">)
    group by towns.town_id, towns.town,companies.townid,towns.recently_searched
    order by towns.town

Open in new window

0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33619481
it is not displaying the text with it, just showing the counts and that also incorrect
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33624576

AS PER U,WHICH VALUE SHOULD COME?
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33624648
at least it should display the names with which values do exists, counts are still going wrong!
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33624769
Sir.

I think we are in some wrong track.
do one thing.
post two sheet.
1 is containing wrong values
2 is expected result.
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33652137
Here is the exact Data with which you can have some help!


COMPANIES TABLE

1	Trinidad Industries (Pvt) Ltd		sdf@fhj.lll	dfsdfsdfsdf	sdfsdfsdf	196	1	2	0	GS Randhawa 79 Kabir Park	P.O. Khalsa College Amritsar Punjab INDIA	234234234234	234234234	2342342	<p>
 4sdfsdfsdfsdfsdfsdfsdf</p>
	grandcanyon04_1024x768_thumbnail.jpg	1	17	1	10	<p>
 sdsdsddsdsdsdsd</p>
<div firebugversion="1.5.4" id="_firebugConsole" style="display: none;">
 &nbsp;</div>
<br />
	74.871552	31.63089	IT,Technology,ComputersHistory, Civilization,IndusBulawayo
5	Test2 Chartered Accountants	http://www.Test2.co.zw	info@Test2.co.zw	Test2 Business Park	135 Enterprise Road	196	2	1	18	dsfs	dfsdfsdfsdfsd	23423423423	234234234234	2342343334	Test2 Chartered Accountants (Zimbabwe) is a well-established firm of chartered accountants and business development consultants.
	Test2_logo_thumbnail.jpg	1	22	101	20	<p>
 <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Our firm offers a team of specialist consultants in </font><font face="Trebuchet MS, Arial, Verdana, sans-serif" size="2">auditing and assurance services, business advisory services, accounting and transaction support services, tax and secretarial consultancy services, investigation and forensic services, information technology support, management consulting and trade consulting</font><font face="Trebuchet MS, Arial, Courier New" size="2">. </font></strong></p>
<h1>
 <font color="#006600" face="Trebuchet MS, Arial, Courier New" size="3"><strong>There are three divisions in the firm</strong></font></h1>
<ul>
 <li>
  <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Audit and Assurance, Investigation and Forensics Division. </font></strong></li>
 <br />
 <li>
  <font size="2"><strong><font face="Trebuchet MS, Arial, Courier New">Tax Compliance and Advisory Services Division.</font></strong></font></li>
 <br />
 <li>
  <font face="Trebuchet MS, Arial, Courier New" size="2"><strong>Financial and Business Advisory Services Division.</strong></font></li>
</ul>
<p>
 <font color="#006600" face="Trebuchet MS, Arial, Courier New" size="3"><strong><a name="membership"></a>Membership</strong></font></p>
<p>
 <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Our firm and partners are members of the following professional bodies: </font></strong></p>
<ul>
 <li>
  <div align="justify">
   <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Institute of Chartered Accountants of Zimbabwe (ICAZ). </font></strong></div>
 </li>
 <li>
  <div align="justify">
   <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Institute of Certified Public Accountants of Zimbabwe (CPA).</font></strong></div>
 </li>
 <li>
  <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Associate member of the Association of Certified Fraud Examiners (ACFE).</font></strong></li>
 <li>
  <div align="justify">
   <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Our partners are registered Public Auditors and Accountants in accordance with the Public Accountants and Auditors Act (Chapter 27:12).</font></strong></div>
 </li>
 <li>
  <div align="justify">
   <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Approved training practice of the Association of Chartered Certified Accountants (UK) ACCA.</font></strong></div>
 </li>
 <li>
  <div align="justify">
   <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Approved training practice of the Institute of Chartered Accountants in England and Wales.</font></strong></div>
 </li>
 <li>
  <div align="justify">
   <strong><font face="Trebuchet MS, Arial, Courier New" size="2">Our Managing Partner is also a registered Estate Administrator in terms of the Estate Administrations Act (Chapter 27:20).</font></strong></div>
 </li>
</ul>
	31.053961	-17.82922	Insurance,IT,Technology,Computers,Commercial Banks,Training,InternetHarare
6	Zimbabwe Online	http://www.zol.co.zw	sales@zol.co.zw		79 Mosiac Building	196	1	2	0	678 Nosya Bulding
	Pin Code: 28876	564787	346576	912354675	Broadband for Zimbabwe	NULL	1	5	22	10	<p>
 This is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool TextThis is a Cool Text</p>
	31.053961	-17.82922	IT,Technology,ComputersHistory, Civilization,IndusBulawayo
8	Bank Bank	http://www.Bank.co.zw	info@Bank.co.zw		79 Mosiac Building	196	1	2	0	79 hiriosimha	the Nagalands	456745	345679	192354765	The bank for Africa	NULL	1	10	12	10	<p>
 cool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is greatcool! the website is great</p>
	NULL	NULL	Retail BanksHistory, Civilization,IndusBulawayo
9	AON Zimbabwe	http://www.aon.co.zw	info@aon.co.zw		Testing testing	196	2	1	16	asdasda	sdasdasdasd	763623	763624	912543586	Risk Solution Providers	aon_logo_thumbnail.jpg	1	24	21	20	<p>
 This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two This is test two</p>
	NULL	NULL	InsuranceHarare
10	ExampleData Zimbabwe	http://www.ExampleData.com	sales@ExampleData.com	14 Winchester Court	12 Lonsdale Road	196	2	1	4	14 Winchester Court	12 Lonsdale Road, Avondale	342356	234312	912381263	Custom web development & application development geared toward your specific goals and needs.	ExampleData_google_logo_thumbnail.gif	1	24	1	20	<br />
	NULL	NULL	Agency, Advertising,InternetHarare
11	ExampleData2 New Holland Divsion	http://www.ExampleData2.co.zw	sales@ExampleData2.co.zw	Private Building	35 Douglas Road	196	2	1	55	35 Douglas Road	Workington	621081	669222	912645263	Manufacturers and Retailers of : Agricultural Implements and Spares, New Holland Tractors (Fiat and Ford) and Spares, Trailers and Water Carts, Cattle Handling Equipment and Animal Drawn Implements	ExampleData2_new_holland_logo_thumbnail.jpg	1	24	1	20	<br />
	NULL	NULL	InsuranceHarare
12	ExampleData3 Holdings Limited		info@ExampleData3.co.zw	Private Building	23 Coventry Road	196	2	1	1	PO Box St. 202	Southerton	663595	665038	912457875	Manufacturing: Holding company involved in the procurement, manufacture and distribution of pharmaceuticals and cosmetic products.	NULL	1	22	1	10	<p>
 test</p>
	NULL	NULL	PC, Laptops, Desktops, ServersHarare


TOWNS TABLE

1	Harare	04	2	Harare
2	Bulawayo	09	1	Bulawayo
3	Mutare	20	3	
4	Bindura	34	4	
5	Marondera	23	5	Marondera
6	Chinhoyi	13	6	Chinhoyi
7	Masvingo	27	7	Masvingo
8	Lupane	24	8	Lupane
9	Gwanda	11	9	Gwanda
10	Gweru	54	10	Gweru

I hope now we can get exact results what we need

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33659332
Hi,

I was on leave,so didnt check.
I will check today and get back to you
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33662593
I was trying 2 understand what u post but some how its difficult.
can u pls post ur data inside excel sheet?
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33704539
Ok the excel sheet is attached, check it please and reply asap as u can

regards
pages.txt.xls
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33707413
Hi,

your excel sheet contains full data?
Unfortunate i dont sql server with me so i tried to similar condition in access.
pls check out attached mdb file,it seems showing proper data.

moreover how many records you are getting in my following query.
please check.

I think your company table must be having more data then your excel sheet contain.
SELECT COUNT(companies.companyID) AS total, towns.town, companies.townid, towns.town_id, towns.recently_searched
FROM companies LEFT JOIN towns ON companies.townid=towns.town_id
GROUP BY towns.town_id, towns.town, companies.townid, towns.recently_searched
ORDER BY towns.town;

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33707415

Please find attached DB.
test.mdb
0
 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 33710142
There is trouble with the query i tried with is

    SELECT COUNT(companies.companyID) AS total, towns.town, companies.townid, towns.town_id, towns.recently_searched
      FROM companies LEFT JOIN towns ON companies.townid=towns.town_id
    WHERE towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.keyword#%">)
    OR towns.recently_searched LIKE (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.searchwhere#%">)
      GROUP BY towns.town_id, towns.town, companies.townid, towns.recently_searched
      ORDER BY towns.town

and it returns no results!

image attached
Untitled.gif
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 33710211
hi,

i dont have ms-sql,so tried with access.
following query is worked fine in my case.

do 1 thing,please.

just import all data of mdb in mssql and try to run attached query.


SELECT COUNT(companies.companyID) AS total, towns.town, companies.townid, towns.town_id, towns.recently_searched
FROM companies LEFT JOIN towns ON companies.townid=towns.town_id
where towns.recently_searched like '*history*' or  towns.recently_searched like '**'
GROUP BY towns.town_id, towns.town, companies.townid, towns.recently_searched
ORDER BY towns.town;

Open in new window

test.mdb
0
 
LVL 15

Author Closing Comment

by:Gurpreet Singh Randhawa
ID: 33736430
Great Help Provided Thanks Dude
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33736520
hey...

r u sure, you got the solution or fed up with looooooooong conversation.;-)

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now