Executing two tables and grabbing it's data.

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

General SQL Server Forums

New to SQL Server Programming

Executing two tables and grabbing it's data.

Author

Topic

tarz
Starting Member

31 Posts

Posted - 2008-05-02 : 12:34:54

Hi,

I am passing a parameter and executing two tables and grabbing it's data.. In the future I will put the code into a store-procedure.

--Exec Table 1
declare @id varchar(20), @MEMBER_ID varchar(20)
set @id=null
set @MEMBER_ID ='55555' --ie. 55555

Select id from emp Where MEMBER_ID = @MEMBER_ID

--Okay, Next I need to execute another table and pass in the id
--that was selected from the emp table.

SELECT EMAIL FROM moreInfo WHERE id = @id

Currently, the emp table displays ie. 100 records that matches the member id 55555.. But the second select is empty.. And I need to display email data for the 100 records that were selected from the emp table..

I hope is it not confusing what I am trying to do..

Please tell me how to do it..

Thank you...

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-05-02 : 12:53:44

select m.email
from emp e left join moreInfo m on e.id = m.id
WHERE e.MEMBER_ID = @MEMBER_ID

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-05-02 : 17:13:10

Send sample data and expected output.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tarz
Starting Member

31 Posts

Posted - 2008-05-05 : 17:00:15

Here is what I need to accomplish.. Please take a look.. Thank you.

I am written a store procedure that would access four tables and grab appropriate fields..

I am using JOIN functionality because it can have multiple rows.

( The goal is: )
Step 1: User can search by ID or MEMBER_ID or both .. grab all the data from mainTable based on the search. WORKS.
Step 2: TABLE 2 (userTable table) get EMAIL for each record that was grabbed.. based on the ID. WORKS.
Step 3: TABLE 3 and TABLE 4.. I am having some problems combing into the query.. how to add multiple JOINS… Is it safe? Please see below what data needs to be combined into the query.

--Code works for Step 1 and 2.
declare @ID varchar(20), @MEMBER_ID varchar(20)
set @ID= null --testing data..
set @MEMBER_ID ='15552' –testing data..

Select MainTable.REFNO,MainTable.ID,mainTable.MEMBER_ID,userTable.EMAIL
FROM mainTable
LEFT JOIN userTable
ON mainTable.ID = userTable.ID
Where
(mainTable.ID = @ID OR @ID IS NULL) and
(mainTable.MEMBER_ID = @MEMBER_ID OR @MEMBER_ID IS NULL)

TABLE 3: (works by itself)
SELECT SR.COMPANY, SR.LOCATION_NO
FROM SI INNER JOIN SR
ON SI.SR_ID = SR.SR_ID
WHERE SI.ID = MainTable.ID)
ORDER BY SR.DATE_RECEIVED DESC

TABLE 4: (works by itself)
I will be retrieving LOCATION_NO from SR table and comparing the value to the below query: for each record that was found in the mainTable.
select LOCATION_NAME from location
where LOCATION_NO= SR.LOCATION_NO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 01:24:09

quote:
Originally posted by tarz

Here is what I need to accomplish.. Please take a look.. Thank you.

I am written a store procedure that would access four tables and grab appropriate fields..

I am using JOIN functionality because it can have multiple rows.

( The goal is: )
Step 1: User can search by ID or MEMBER_ID or both .. grab all the data from mainTable based on the search. WORKS.
Step 2: TABLE 2 (userTable table) get EMAIL for each record that was grabbed.. based on the ID. WORKS.
Step 3: TABLE 3 and TABLE 4.. I am having some problems combing into the query.. how to add multiple JOINS… Is it safe? Please see below what data needs to be combined into the query.

--Code works for Step 1 and 2.
declare @ID varchar(20), @MEMBER_ID varchar(20)
set @ID= null --testing data..
set @MEMBER_ID ='15552' –testing data..

Select MainTable.REFNO,MainTable.ID,mainTable.MEMBER_ID,userTable.EMAIL,
t.COMPANY, t.LOCATION_NO,t.LOCATION_NAME
FROM mainTable
LEFT JOIN userTable
ON mainTable.ID = userTable.ID
INNER JOIN (SELECT SI.ID,SR.COMPANY, SR.LOCATION_NO,SR.DATE_RECEIVED ,L.LOCATION_NAME
FROM SI INNER JOIN SR
ON SI.SR_ID = SR.SR_ID
INNER JOIN LOCATION L
ON L.LOCATION_NO= SR.LOCATION_NO )t
ON t.ID = MainTable.IDWhere
(mainTable.ID = @ID OR @ID IS NULL) and
(mainTable.MEMBER_ID = @MEMBER_ID OR @MEMBER_ID IS NULL)
ORDER BY t.DATE_RECEIVED DESC

Can you try like this?

tarz
Starting Member

31 Posts

Posted - 2008-05-06 : 16:21:40

Thanks...Almost what I need... there needs to be a change..

The table SI table might have multiple records with the same IDs, because the ID is not PK, The PK is SR_ID.

I need to get the last one based on the DATE_RECEIVED.

Now it shows all of them as the result..

i.e
|company | Location No | Date_Received | Name ..etc…
Abc | 123 | 2006-06-16 | Joe
Abc | 123 | 2007-06-13 | Mike
Abc | 123 | 2008-03-11 | Mike

I would need to display ONLY the DATE_RECEIVED DESC
[ Abc | 123 | 2008-03-11 |Max]

Select MainTable.REFNO,MainTable.ID,mainTable.MEMBER_ID,userTable.EMAIL,
t.COMPANY, t.LOCATION_NO,t.LOCATION_NAME
FROM mainTable
LEFT JOIN userTable
ON mainTable.ID = userTable.ID
INNER JOIN (SELECT SI.ID,SR.COMPANY, SR.LOCATION_NO,SR.DATE_RECEIVED ,L.LOCATION_NAME
FROM SI INNER JOIN SR
ON SI.SR_ID = SR.SR_ID
INNER JOIN LOCATION L
ON L.LOCATION_NO= SR.LOCATION_NO) t
ON t.ID = MainTable.ID
Where (mainTable.ID = @ID OR @ID IS NULL) and
(mainTable.MEMBER_ID = @MEMBER_ID OR @MEMBER_ID IS NULL)
ORDER BY t.DATE_RECEIVED DESC

When Now executed..
i.e
REFNO | ID |MEMBER_ID | EMAIL | COMPANY | LOCATION_NO | LOCATION_NAME | DATE_RECEIVED ….etc..

125 2222 aacc aaa@a.com Abc 123 Def 2006-06-16
125 2222 aacc aaa@a.com Abc 123 Def 2008-03-16 ..etc..
133 2555 ddcc rrr@a.com fedex 222 3333 2008-05-06 ..etc..

First and second record has the same ID.. I need to get the data from SR table based on the DATE_RECEIVED

Should only display
125 2222 aacc aaa@a.com Abc 123 Def 2008-03-16
133 2555 ddcc rrr@a.com fedex 222 3333 2008-05-06 ..etc..

I hope it is not confusing…

Thank you so much …

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources