get results from 2 tables?

Question

get results from 2 tables?

mick burden

SSCarpal Tunnel

Points: 4432
More actions
October 1, 2011 at 2:28 pm

#245830

I have a table which holds names and phone numbers and another table that records when they rang, I'd like to be able to query table1 to find numbers for a certain person such as zoe, but in the same recordset return the last time those people rang me or just thier numbers even if they hadn't rung, is this possible?
Table1 Table2
Name Number Number date of call
Zoe Home 123456 876344 01/01/2011 15:23
Zoe Work 342452 323345 01/02/2011 07:22
tony 876344 876344 01/02/2011 08:12
Alice 323345 876344 01/02/2011 09:00

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

To answer your questions, Yes it is possible. That said what you have posted as your table definition(s) is not very clear. To get a tested response, can you post the table definitions, and sample data by following the techniques described in the first link of my signature block. That link also includes sampe T-SQL to facilitate your posting of the table definitions as well as the sample data.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 2

My opologises, it seems I'd formatted the text wrong, the tables are made up like

table1

name as varchar(50)

[Number] as Varchar(20)

table2

[Number] as varchar(20)

[date of call] as datetime

In both tables the number field contains a phone number, the name fiel holds the persons name and the date of call field holds the date and time the person rang, hope this helps

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Mick,

Read the link that BitBucket pointed you to and post your data using the methods therein... you'll get answers much quicker.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 4

Ok, as per Jeff's kind prompt I've created a script to create a test database to show what I'm after.

IF OBJECT_ID('mycalls','U') IS NOT NULL

DROP TABLE mycalls

IF OBJECT_ID('myPhonebook','U') IS NOT NULL

DROP TABLE myPhonebook

--===== Create the test table with

CREATE TABLE mycalls

( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

DateCalled DATETIME,

mname varchar(80),

mnumber varchar(15) )

CREATE TABLE myPhonebook

( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

pname varchar(80),

pnumber varchar(15) )

SET IDENTITY_INSERT mycalls ON

INSERT INTO mycalls

(ID, DateCalled, mname, mnumber)

SELECT '1','Oct 03 2011 17:02:00','Mick Home','02081233423' UNION ALL

SELECT '2','Oct 02 2011 18:03:00','Zoe Mobile','07765843678' UNION ALL

SELECT '3','Oct 01 2011 20:21:00','Zoe Mobile','07765843678' UNION ALL

SELECT '4','Oct 01 2011 17:56:00','Mick mobile','07764534523' UNION ALL

SELECT '5','Sep 27 2011 18:00:00','Mick Home','02081233423' UNION ALL

SELECT '6','Sep 27 2011 17:24:00','Mick Home','02081233423' UNION ALL

SELECT '7','Sep 25 2011 15:23:00','Zoe Mobile','07765843678' UNION ALL

SELECT '8','Sep 23 2011 11:00:23','Mick Home','02081233423'

SET IDENTITY_INSERT mycalls Off

SET IDENTITY_INSERT myPhonebook ON

INSERT INTO myPhonebook

(ID, pName, pnumber)

SELECT '1','Mick Home','02081233423' UNION ALL

SELECT '2','Zoe Mobile','07765843678' UNION ALL

SELECT '3','Zoe Home','02064537564' UNION ALL

SELECT '4','Mick mobile','07764534523' UNION ALL

SELECT '5','Tony Home','02034236423' UNION ALL

SELECT '6','Tony Mobile','07734236423'

SET IDENTITY_INSERT myPhonebook Off

As you can see there are 2 tables, one is esentially a phone book of names and phone numbers, the other table shows when a person has rung me, what I'm after is to be able to search the phonebook table for a name using like, and for the search to return the fullname, number and the date and time the last time that particular number has rung getting this from mycalls table, or return null if that number hasn't appeared in the mycalls table. I hope this is more clear than my first posts.

What I've tried so far with assistance of a friend is

SELECT d.Name, d.Number, c.[DateCalled]

FROM myPhonebook d left outer JOIN

mycalls c ON d.Name = c.Name

where d.[name] like '%mick%'

group by d.Name, d.Number, c.[DateCalled]

order by c.[DateCalled] desc

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

this may help

SELECT MP.pname AS Phone_Name,

MP.pnumber AS Phone_Number,

MAX(MC.DateCalled) AS last_called_on

FROM myPhonebook MP

LEFT OUTER JOIN mycalls MC

ON MP.pname = MC.mname

GROUP BY MP.pname,

MP.pnumber

HAVING ( MP.pname LIKE '%mick%' )

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 6

That is brilliant, thank you so much, it's exactly what I needed