why this query shows records twice...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

why this query shows records twice...

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-04-06 : 15:35:55

hi,

this is my query and it shows all records twice

SELECT t.id,t.ticket_no,t.customer_id,t.location,t.distributor,t.date_of_buy
,t.ticket_status,c.mobile FROM dbo.ticket_details t left join customer_details c
on t.customer_id = c.id WHERE t.createdDate < DATEADD(HH,-48,getdate()) ORDER BY t.id DESC

please help me

REgards,
ASIF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 15:39:38

It's because you have two matches for your join. Run SELECT * FROM <rest of your query> to see the differences.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-04-06 : 15:46:24

Sorry tara,

I didnt get you, can you write a query for me,

Regards,
ASIF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 15:52:49

SELECT *
FROM dbo.ticket_details t
left join customer_details c
on t.customer_id = c.id
WHERE t.createdDate < DATEADD(HH,-48,getdate())
ORDER BY t.id DESC

This is just to inspect the data so that you can fix the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 15:56:32

[code]SELECT *
FROM dbo.ticket_details t
LEFT JOIN customer_details c
ON t.customer_id = c.id
WHERE t.createdDate < DATEADD(HH,-48,getdate())
ORDER BY t.id DESC
[/code]
That won't stop each record displaying twice, but it will give you an idea as to why it's happening.

There are 10 types of people in the world, those that understand binary, and those that don't.

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-04-06 : 15:57:36

Hi Tara,

So, how can i fix this one

why it gives two records

Regards,
ASIF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 16:05:47

You would need to show us some data for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 16:07:47

What are the primary keys of the ticket_details and customer_details tables?

There are 10 types of people in the world, those that understand binary, and those that don't.

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-04-06 : 16:47:18

Hi,

I have two tables such as ticket and customer,

in customer table i have these fields such mobileno,customerName,Addrss,Id etc....
ID column is autogenerated(IDENTITY)

In, ticket table,I have these fields,
id,cust_id,ticket_detail,createdDate etc....
here also ID is autogenerated,

Now, there can be more than one ticket for one customer

I need query like,

display all tickets with customer mobileno and customername

means like this
mobileno,customername,ticket_detail,createdDate etc.....

Regards,
ASIF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 17:01:41

Where's the sample data that was asked for?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-04-06 : 17:08:20

id ticket_no customer_id location distributor date_of_buy ticket_status mobile CustomerName
1 1 1 East Asef 04/04/2010 Open 966502146894 asifbhura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 17:08:57

One row of data is not going to help us help you. Please give some more effort if you want us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog