How to use joins with multiple Tables....

Question

Post reply

How to use joins with multiple Tables....

Manish_

Ten Centuries

Points: 1070
More actions
November 21, 2005 at 6:12 am

#90023

Hi Friends
i need your guidance on this below mentioned problem...
i want to join my table with some tables... in a single query only
i have a sql query like this...
Select * From emc_messageinfo
which gives me output
--------------------------------
Table Code     Remark
--------------------------------
1     1      This is Customer
1     2      This is Customer
1     3      This is Customer
1     4      This is Customer
2     1      This is Supplier
2     2      This is Supplier
2     3      This is Supplier
2     4      This is Supplier
3     1      This is User
3     2      This is User
3     3      This is User
3     4      This is User
--------------------------------
Here it shows that the column Table is having 3 kind of Values i.e 1 and 2 and 3
Now i want to get the
name of the Customer from table Customer_mast where the value of Table column is 1,name of the Supplier from table Supplier_mast where the value of Table column is 2
name of the User from table User_mast when the value of Table column is 3....
means i want to get my output like this....
--------------------------------
Table      Code     Remark
--------------------------------
Customer     ABC     This is Customer
Customer     DEF      This is Customer
Customer     HIJ      This is Customer
Customer     KLM      This is Customer
Supplier     ZZZ      This is Supplier
Supplier     YYY     This is Supplier
Supplier     XXX     This is Supplier
Supplier     WWW     This is Supplier
User         AMIT      This is User
User         AMIN      This is User
User         ANIL      This is User
User         AMIN      This is User
--------------------------------
But see for getting this information i have to join my main table that is emc_messageinfo with any of this table like
Customer_mast,Supplier_mast,User_mast.....
here only i m stucked and i m facing problem to do so....
Please guide me how can i perform this....
- Manish

Viewing 4 posts - 1 through 3 (of 3 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

SET NOCOUNT ON

GO

DECLARE @emc_messageinfo TABLE

(

[Table] INT,

Code INT,

Remark VARCHAR(25)

)

INSERT @emc_messageinfo

SELECT 1, 1, 'This is Customer' UNION

SELECT 1, 2, 'This is Customer' UNION

SELECT 1, 3, 'This is Customer' UNION

SELECT 1, 4, 'This is Customer' UNION

SELECT 2, 1, 'This is Supplier' UNION

SELECT 2, 2, 'This is Supplier' UNION

SELECT 2, 3, 'This is Supplier' UNION

SELECT 2, 4, 'This is Supplier' UNION

SELECT 3, 1, 'This is User' UNION

SELECT 3, 2, 'This is User' UNION

SELECT 3, 3, 'This is User' UNION

SELECT 3, 4, 'This is User'

DECLARE @Customer_mast TABLE

(

Code INT,

Value VARCHAR(100)

)

INSERT @Customer_mast

SELECT 1, 'AB1' UNION

SELECT 2, 'DE2' UNION

SELECT 3, 'HI3' UNION

SELECT 4, 'KL4'

DECLARE @Supplier_mast TABLE

(

Code INT,

Value VARCHAR(100)

)

INSERT @Supplier_mast

SELECT 1, 'ZZ1' UNION

SELECT 2, 'YY2' UNION

SELECT 3, 'XX3' UNION

SELECT 4, 'WW4'

DECLARE @User_mast TABLE

(

Code INT,

Value VARCHAR(100)

)

INSERT @User_mast

SELECT 1, 'AM1' UNION

SELECT 2, 'AM2' UNION

SELECT 3, 'AN3' UNION

SELECT 4, 'AM4'

SELECT

= CASE WHEN E.[Table] = 1 THEN 'Customer' WHEN E.[Table] = 2 THEN 'Supplier' WHEN E.[Table] = 3 THEN 'User' END,

CODE = CASE

WHEN E.[Table] = 1 THEN C.Value

WHEN E.[Table] = 2 THEN S.Value

WHEN E.[Table] = 3 THEN U.Value

END,

E.Remark

FROM

@emc_messageinfo E

LEFT JOIN

@Customer_mast C

ON

E.Code = CASE WHEN E.[Table] = 1 THEN C.Code END

LEFT JOIN

@Supplier_mast S

ON

E.Code = CASE WHEN E.[Table] = 2 THEN S.Code END

LEFT JOIN

@User_mast U

ON

E.Code = CASE WHEN E.[Table] = 3 THEN U.Code END

WHERE

COALESCE(CASE

WHEN E.[Table] = 1 THEN C.Value

WHEN E.[Table] = 2 THEN S.Value

WHEN E.[Table] = 3 THEN U.Value

END, '') <> ''

Regards,
gova

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Or similarly (borrowing Govinn's temp tables + data - thanks for that!!)

select 'Customer' as [Table], C.Value
FROM @emc_messageinfo E
  INNER JOIN @Customer_mast C
    on E.Code = C.Code and E.[Table] = 1
UNION ALL
select 'Supplier' as [Table], S.Value
FROM @emc_messageinfo E
  INNER JOIN @Supplier_mast S
    on E.Code = S.Code and E.[Table] = 2
UNION ALL
select 'User' as [Table], U.Value
FROM @emc_messageinfo E
  INNER JOIN @User_mast U
    on E.Code = U.Code and E.[Table] = 3

Try this instead of the last select statement in govinn's code - it's a different approach, not necessarily better or worse, but possibly a bit easier to understand (I also forgot to put in that third column - easy to add). It may also take better advantage of any indices you have on the tables. As always, there are many ways to solve a problem - try a couple of the ideas, or modify yourself, and let us know how you go.

Cheers,

Ian

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

I agree Ian's solution is easy to undersatnd and maintain. I am not sure about the efficiency but I would take that query instead of what I gave for simplicity.

Regards,
gova