SQL

Question

I f i have two table i take one issue from one table(Eg.101) and now i want to see same issue(101) in another table how time it repeat same issue. so how i can hit query. please help soon.

Answer 1

Are you looking for the below:

Create Table OneTable(Col1 int)
Insert into OneTable Select 101
Insert into OneTable Select 102
Create Table SecondTable(Col1 int)
Insert into SecondTable Select 101
Insert into SecondTable Select 101
Select A.Col1, COUNT(B.Col1) From OneTable A
Left Join SecondTable B on A.Col1 = B.Col1
Group by A.Col1

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 2

insert into Table2 (Issue, ....)

select Issue, ... from Table1 where Issue = '101'

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Sir i don't want to create or insert. table is already exit and record is also there. i just want to find by sql query. hint is count statement 

Answer 4

can explicit please

Answer 5

Change the query with your actual tables. As long as we need work tables, we always create.

Select A.Col1, COUNT(B.Col1) From OneTable A
Left Join SecondTable B on A.Col1 = B.Col1
Group by A.Col1

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 6

sir i just want to know total issues.

i have issues table from where i get issues id and i have another table (action) in that there are number of issues of same issues(which is in issues table) and i want the data of 2011-2012 record for that i hit like query. but how i can join like and count query.

example: i have issues table issues is (10001) in which year is also the like '11_____' which indicate of record of 2011

now i take issues from issue table and seacrh in action table i get mutliple issues of same issues which in issue table(10001) how to hit query please help

Answer 7

Can you please post structure of both tables, some input data and desired output? It's hard to understand you right now.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

Its hard to understand your req. However, check and let me any of the below query is something what you would like to have....

Create Table Issue(IssueID int)
Insert into Issue Select 101
Insert into Issue Select 102
Create Table [Action](IssueID int,CreatedDate Date, Action Varchar(100))
Insert into [Action] Select 101, GETDATE()-1,'Started'
Insert into [Action] Select 101,GETDATE(),'Closed'
--To get the count of Action table for each every issue
Select A.IssueID, COUNT(B.IssueID) 'Number of Actions' From Issue A
Left Join [Action] B on A.IssueID = B.IssueID
Where Year(B.CreatedDate)=2012 OR B.CreatedDate is null
Group by A.IssueID
--To get all the issues only Action table has values 
Select * From Issue A
Inner Join [Action] B on A.IssueID = B.IssueID
--To get all the issues though no value in Action table
Select * From Issue A
Left Join [Action] B on A.IssueID = B.IssueID
 

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 9

Sir thanks for u r help.

Select A.IssueID, COUNT(B.IssueID) 'Number of Actions' From Issue A Left Join [Action] B on A.IssueID = B.IssueID Where Year(B.CreatedDate)=2012 OR B.CreatedDate is null Group by A.IssueID

What A and B indicate?

Answer 10

A and B are alias to represent the tables. Meaningful alias would be help in complex T-SQL development for easy coding and maintainability

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 11

Sir really thanks for Ur help. 

sir i have more related to this.

How to find out Total number of hits by the employee(P Despande) on these complaints(since
there may be multiple attendances to theses complaints by the
employee)

Answer 12

I would suggest few things to you...

1. This question is totally different from what you have asked initially. So, please mark the thread as answered if your first question is answered.

2. Please start a new thread with details like Table strture and sample data. This would help us to help you better.

3. If you do not mind, I would recommand you to go through some SQL Server fundamental course available.

As I suggested, please give us more info on the second question(table structure and sample input and output data)

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 13

sir new in sql.  

sir i have two table as i told before.

In action tables fields are:

ACTIONID

ISSUEID

OLDSTATUS

NEWSTATUS

CHSUSERTYPE

CHSUSERID

CHSUSERNAME

In issues tables fields are:

CHSREASONID COMPLAINANTID

OFFICERID

CSCCODE

DISTRIBUTORID

STATUS

PRIORITY

How to find out Total number of hits by the employee(P Despande) on these complaints(since
there may be multiple attendances to theses complaints by the
employee)

Answer 14

Select A.IssueID, COUNT(B.IssueID) 'Number of Actions' From Issue A Left Join [Action] B on A.IssueID = B.IssueID Where Year(B.CreatedDate)=2012 OR B.CreatedDate is null Group by A.IssueID

But we have to use count statement and employee and ReferenceID column

Answer 15

How do you match these two tables? What is the common field(s) between them? I have a feeling you may need COUNT(distinct but it is still hard to suggest anything.

I suggest you do right click on the first table in SSMS, select Script create and post the script here, then the same for the second table, then two screen shots for some data and desired result. This way we should be able to help you.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 16

common between two table is issueid. 

we have to use COUNT

I  tried select Count(chsuserid) Number of CHURSEID from actions(TABLE NAME) where Chsuserid = '4047' and RefrenceID = '11%' but it is giving invalid refrenceid

Answer 17

Post the exact create table statements for both tables, so we don't need to deal with partial information.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 18

i have info in excel. i just wht to create query

if u want any info i can send

Answer 19

select chsuerid, count(*) from issues inner join actions on actions.issueid = issues.issueid where chsuserid = '4042' and refrenceid like = '11%' 

how to declare the refrence id table name in this same query

Answer 20

select chsuerid, count(*) from issues inner join actions on actions.issueid = issues.issueid where chsuserid = '4042' and refrenceid like = '11%' 

Error: ORA-00936: missing expression

how to declare the refrence id table name in this same query

Answer 21

select chsuerid, count(*) from issues inner join actions on actions.issueid
  = issues.issueid where chsuserid = '4042' and refrenceid like '11%' and [Status] = '4' and not exists (select from 
 Actions A where A.chsUerID = Actions.chuerID and A.Status = '5')
 Group by chsuserid 

What not exits???