[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Trouble with the coldfusion & mysql Code

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
Gurpreet Singh Randhawa
Asked:
Gurpreet Singh Randhawa
  • 33
  • 32
1 Solution
 
Bhavesh ShahLead AnalysistCommented:
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
 
kingjelyCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
u mean to say one query and QoQ after it of two queries and how do i fetch the results!
0
 
Bhavesh ShahLead AnalysistCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Pleasefind the attached file, here
Untitled-1.png
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Good Morning.

Whats the result u expecting,pls decribe me that.
So its better to produce.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
ok i give u one website example, looking the same like that one

http://www.yellowpages.co.za
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
u see in cfdump if the value is empty value it still shows NULL
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
k i will see in morning it too late here gotta sleep

regards
0
 
Bhavesh ShahLead AnalysistCommented:
Okies

Sweet Dreams =)


- Bhavesh(Brichsoft)
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
Bhavesh ShahLead AnalysistCommented:
sory not table name, table position
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
not table structure,but show me dump and tell which records are wrongly coming
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
Ok..

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

right?
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
ok file attached
Untitled.png
0
 
Bhavesh ShahLead AnalysistCommented:
sir,

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

right??
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
yes but counts should be shown correct!
0
 
Bhavesh ShahLead AnalysistCommented:

what value should come?
inner join should work as town id both are same.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
no its not working the above attached shows same result for inner or left join
0
 
Bhavesh ShahLead AnalysistCommented:

ok.
pls share companies & town table data.

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
ok will do it tonite
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
if a subquery can solve our issue, i have no trouble using or even a procedure
0
 
Bhavesh ShahLead AnalysistCommented:
Hi......

I need data actually.
can u share the data...
pls?
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
sir,

tell m 1 thing,
in companies master townid having NULL value.?
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Default can be null, but we can assume value here so we get relevant data for our purpose!

0
 
Bhavesh ShahLead AnalysistCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
you take any one assumption and u use that!

conside record 1 and 9 for this
0
 
Bhavesh ShahLead AnalysistCommented:

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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
it is not displaying the text with it, just showing the counts and that also incorrect
0
 
Bhavesh ShahLead AnalysistCommented:

AS PER U,WHICH VALUE SHOULD COME?
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
at least it should display the names with which values do exists, counts are still going wrong!
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
Hi,

I was on leave,so didnt check.
I will check today and get back to you
0
 
Bhavesh ShahLead AnalysistCommented:
I was trying 2 understand what u post but some how its difficult.
can u pls post ur data inside excel sheet?
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Ok the excel sheet is attached, check it please and reply asap as u can

regards
pages.txt.xls
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
Bhavesh ShahLead AnalysistCommented:

Please find attached DB.
test.mdb
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Great Help Provided Thanks Dude
0
 
Bhavesh ShahLead AnalysistCommented:
hey...

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

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 33
  • 32
Tackle projects and never again get stuck behind a technical roadblock.
Join Now