Multiple inner joins on same field or nested query?

Hi,

I'm wondering how I can structure an SQL statement to perform either a multiple join on a single table, or possibly using a sub-query. Basically, I've got one table in which both fields are foreign keys to another table, as follows:

Table #1

employee_id (pk)
employee_name

Table #2
teamleader_employee_id
backup_employee_id


both fields in table 2 need to do a lookup in table 1 to get the name of the actual employee. Do I need to use nested queries to accomplish this? Is it possible to do two inner joins on the same table? (I've tried this, unsuccessfully!) Any help is greatly appreciated!

-PJtwo joins to the same table, using table aliases to distinguish which table the rows are from, and column to distinguish the columns in the result set
select lead.employee_name as teamleader
, bkup.employee_name as backup
from table2
inner
join table1 as lead
on teamleader_employee_id = lead.employee_id
inner
join table1 as bkup
on backup_employee_id = bkup.employee_id|||Thank you very much. Works like a charm!

Multiple Columns with different Values OR Single Column with Multiple Criteria?

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1

ON

TBL.col1=TBL1.[key]

INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2

ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

Before you merged the two columns could you not write your query something like this

select *

from TBL

where col2 = 'engine' and col3 = 'toyota'

You could put variables in place of the engine and toyota and create a stored procedure that passes in the values you want to search on.

I don't think there is a need to merge the columns matter of fact it will most likely run faster without being merged.

Or maybe I missed you rquestion.

Multiple Columns with different Values OR Single Column with Multiple Criteria?

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1

ON

TBL.col1=TBL1.[key]

INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2

ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

Before you merged the two columns could you not write your query something like this

select *

from TBL

where col2 = 'engine' and col3 = 'toyota'

You could put variables in place of the engine and toyota and create a stored procedure that passes in the values you want to search on.

I don't think there is a need to merge the columns matter of fact it will most likely run faster without being merged.

Or maybe I missed you rquestion.