Database

20-Feb-07 9:14

hi there
i have 2 table with no relationship between them
and i need 2 extract from table1 the col1
and the phase of col1 in table2 col2
for example:

Table1
Col1
1000
1001
1002
=============================================
Table2
Col2
A1000A
bbb1000bcd
cdf1000frg
A1001a
bvc1001dcfe
=============================================
Result:
colRes count_in_table_2
1000 3
1001 2
1002 0

thanks in advance

roni vars

Mark J. Miller20-Feb-07 9:33

Mark J. Miller

20-Feb-07 9:33

The following will result in a table scan, but it will do the job:

SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 LIKE '%' + T2.Col2 + '%'

Another alternative would be to create a full-text index on Table2.Col2. It all depends on how much data we're talking about and how often and how busy your server is when you run the query.

ronivars20-Feb-07 10:15

20-Feb-07 10:15

hi there thaks for your quick answer
it's not working performance it's not issue
i work with small table
thanks again
any suggestion

roni vars

ronivars20-Feb-07 10:24

20-Feb-07 10:24

hi again

i think u forget the group by T1.Col1

SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 LIKE '%' + T2.Col2 + '%'

group by T1.Col1

it's still not working

roni vars

Mark J. Miller20-Feb-07 11:52

Mark J. Miller

20-Feb-07 11:52

Sorry,

I wrote that a little quickly. Yes, I forgot the GROUP BY, but the main problem was I reversed the references on the join D'Oh! | :doh:

. Try this (I tested it this time Blush | :O

):

SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 LIKE '%' + T1.Col1 + '%'
group by T1.Col1

ronivars20-Feb-07 12:16

20-Feb-07 12:16

hi Mark

i'm sorry but is still doesn't work i try both sql
and return me 0 rows

SELECT T1.Col, COUNT(*) FROM tbl1 T1
INNER JOIN tbl2 T2 ON T2.col LIKE '%' + T1.col + '%'
group by T1.Col

SELECT T1.Col, COUNT(*) FROM tbl1 T1
INNER JOIN tbl2 T2 ON T1.Col LIKE '%' + T2.Col + '%'
group by T1.Col

i also try this 2 sql sentences and is stil doesn't work
its return me
1000 0
1001 0
etc....

1)
select t1.col ,(SELECT count(*) FROM tbl2 t2
WHERE t1.col in (select t2.col from tbl2 t2))
From tbl1 t1
2)
select tbl1.col ,count(case when CHARINDEX(tbl1.col ,tbl2.col) > 0 then 1 else null end)
from tbl1,tbl2
group by tbl1.col

by the way i work with sql server 2005
thanks again
waiting for rescue

Mark J. Miller20-Feb-07 12:23

Mark J. Miller

20-Feb-07 12:23

I'm running 2005 also. This script:

IF OBJECT_ID('Table1') IS NOT NULL
DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL
DROP TABLE Table2
go

CREATE TABLE Table1(Col1 Varchar(15))
CREATE TABLE Table2(Col2 VARCHAR(100))

INSERT INTO Table1 VALUES('1000')
INSERT INTO Table1 VALUES('1001')
INSERT INTO Table1 VALUES('1002')

INSERT INTO Table2 VALUES('A1000A')
INSERT INTO Table2 VALUES('bbb1000bcd')
INSERT INTO Table2 VALUES('cdf1000frg')
INSERT INTO Table2 VALUES('A1001a')
INSERT INTO Table2 VALUES('bvc1001dcfe')

SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 LIKE '%' + T1.Col1 + '%'
GROUP BY T1.Col1

Gives me these results:

1000 3
1001 2

If this isn't working for you, you'll need to give me your schema and what the results are (or the error messages).

ronivars20-Feb-07 12:50

20-Feb-07 12:50

thanks mark is work fine when the column type is varchar
but when the type is nchar it's doesnt work becuse it's fix length

roni vars

ronivars20-Feb-07 13:21

20-Feb-07 13:21

hi mark
You realy help in this Query
You are like Books Online
thanks again for your important help

and if it is nchar column type need to use ltrim rtrim function

SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON ltrim(rtrim(T2.Col2)) LIKE '%' + ltrim(rtrim(T1.Col1)) + '%'
GROUP BY T1.Col1

regards

roni vars

ronivars20-Feb-07 12:22

20-Feb-07 12:22

sory my mistake i try this Sentence instead of first sentence i wrote before

select t1.col ,(SELECT count(*) FROM tbl2 t2 WHERE t1.col like '%'+ t2.col +'%') as count1
From tbl1 t1

roni vars

Count Query Prob

tadhg8820-Feb-07 6:39

20-Feb-07 6:39

Hi,
I am having some trouble with an sql statement and im just wondering if anyone could help me I have a ScoutingReport Table that stores player scouting info such as the matched, coached and playerId and I need a count of the amount of times a player has been scouted but if the same scout reports on the same match twice then I must only be counted once hers what I have started with any help would be appreciated.

Select Count(Distinct(MatchId))
From ScoutingReport
where ScoutedPersonId = 10002
group by MatchId
order by MatchId

Thanks in advance
Tim

andyharman20-Feb-07 7:05

andyharman

20-Feb-07 7:05

Try:

select ScotedPersonId, count(distinct MatchId)
from ScotingReport
group by ScoutedPersonId
order by ScoutedPersonId

tadhg8820-Feb-07 7:32

20-Feb-07 7:32

thats what i need so far but i should also have said that if a different scout reports on the same match then it should be counted as 2 reports any ideas
thanks for your reply

andyharman20-Feb-07 8:17

andyharman

20-Feb-07 8:17

Which database are you using? You may be able to get away with:

select ScoutedPersonId,
   count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId))
from ScotingReport
group by ScoutedPersonId
order by ScoutedPersonId

(which cheats a bit and turns the MatchId and ScoutId into a single string value that can be counted) or

select ScoutedPersonId, Sum(ScoutCount)
from (
  select ScoutedPersonId, MatchId, count(distinct ScoutId) AS ScoutCount
  from ScoutingReport
  group by ScoutedPersonId, MatchId
) A
group by ScoutedPersonId
order by ScoutedPersonId

(which uses an inline view).

Regards
Andy

tadhg8821-Feb-07 0:37

21-Feb-07 0:37

both worked perfectly
ur an absolute gentleman thanks alot man much appreciated

tadhg8822-Feb-07 1:53

How to add costum row to combo what linked to DataSource

22-Feb-07 1:53

hi i am using this query to count the amount of team scout reports but when a player has no reports null is returned but i need 0 to be returned this is the sql any help would be appreciated

select sr.ScoutedPersonId, count(distinct convert(varchar, sr.MatchId) + '-' + convert(varchar, sr.ScoutId)) as 'IndCount',
(
-------------------
this is the team count that is not working
select
CASE
When count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId)) > 0
Then count(distinct convert(varchar, MatchId) + '-' + convert(varchar, ScoutId))
Else 0
END

from ScoutingReport
Where Type = 1 -- team scout reports
And ScoutedPersonId = sr.ScoutedPersonId
group by ScoutedPersonId
---------------
) as 'TeamCount'
from ScoutingReport sr
Join Person p on p.ID = sr.ScoutedPersonId
Where sr.Type = 0
Group By sr.ScoutedPersonId
Having count(distinct convert(varchar, sr.MatchId) + '-' + convert(varchar, sr.ScoutId)) = 1
Order By sr.ScoutedPersonId

El'Cachubrey20-Feb-07 3:37

El'Cachubrey

20-Feb-07 3:37

Hi all, i have a few quetion to discover, bee so pleasant to explain me how i can do:

1)
I have combobox and data source for one

this.comboBox1.DataSource = this.authorsBindingSource;

Now I need to add to combobox an row, what not exist in bindingSource (supoose I want do display in combobox an additional row "not defined")
How I can do this????
---------------------------------------------------------------
2)
How I can to mark a rows in dataTable, so that ones not will be stored in database then this dataTable updated.
I mean:

//myDataTable - DataTable object

// I want that this row shouldn’t bee stored in database, but exist in //dataTable (what I should do)
DataRow dr1 = myDataTable.NewRow();

///initilize row columns with data
..

myDataTable.Rows.Add(dr1);

// I want that this row should bee stored in database and exist in //dataTable (rest all as is)
DataRow dr2 = myDataTable.NewRow();

///initilize row columns with data
..

myDataTable.Rows.Add(dr2);

//Want to bee stored dr2 not dr1
this.myDataTableAdapter.Update(myDataTable);

How I can do this.
-----------------------------------------------------------------------------------
3) How I can explore what column (DataColumn type) have default value???

How to order Integer field as a string ??

kindman_nb20-Feb-07 3:22

kindman_nb

20-Feb-07 3:22

Hi ..
I a table ,, which has integer field "ID" ,,
I'd like order ID on Sql statment as a string ..
for eg ..
fields is

1 , 2 , 101 , 103 , 201 , 202

the order of fields will be :
1 , 2 , 101 , 103 , 201 , 202

but if that fields is String the order will be :
1 , 101 , 103 , 2 , 201 , 202
it's that I want ....

then how can I on the sql statment order an Integer field as String field

thanks for my favorite forum ... Rose | [Rose]

jooooo

Aswanikumarj20-Feb-07 3:45

Aswanikumarj

20-Feb-07 3:45

select cast(orderid as varchar(10)) from table1 order by cast(orderid as varchar(10))

use the above query u can get what ever output ur saying.

kindman_nb20-Feb-07 5:05

kindman_nb

20-Feb-07 5:05

so thanks my friend
it's ok

jooooo

andyharman20-Feb-07 3:46

andyharman

20-Feb-07 3:46

Unusual requirement! Order by Convert(varchar(10), ID)