How can I get data from 3 tables as above with condition RegisDate,

Ex: When user select Model, Cell then choose condition is RegisDate will be show the result from 3 tables above (allowed to have NULL from 1 or 2 table)

select *
from Registration as reg
left join Repair as rep
on rep.Model = reg.Model and
rep.Cell = reg.Cell and
rep.BodyNo = reg.BodyNo
left join Borrow-return as ret
on ret.Model = reg.Model and
ret.Cell = reg.Cell and
ret.BodyNo = reg.BodyNo

When user select Model, Cell then choose condition is RegisDate will be show the result from 3 tables above

I need to get result with condition
RegisDate, and I tried but the result not exactly,

Could you provide more things to explain the condition of “RegisDate”? You’d better provide some sample record of your three tables and then give your expected output.

Besides, in your second reply, the expected out is “A B 5 6 A B NULL NULL”, but there’s no related record in Table1, so I think it’s better to make the 5 and 6 column be null. Because, it’s not easy to say why they would not be “A B 5 6
A C NULL NULL” and why it would only be “A B 5 6 A B NULL NULL”.

Or, you could have a look at the following sample code that would return your expected result and maybe it could help you.

with cte (col1,col2,col3,col4,col5,col6,col7,col8)
as
(
SELECT *
FROM t2 a FULL OUTER JOIN t1 b
ON b.Col1 = a.Col1 AND b.Col2 = a.Col2 and b.Col3 = a.col3 and b.Col4 = a.col4
)
select
col1,col2,col3,col4,
case when col7 is null then col1
else col5
end as col5,
case when col8 is null then col2
else col6
end as col6,
col7,col8
from cte

Member

5 Points

31 Posts

## Query return data from 3 tables in sql

Feb 22, 2016 02:39 PM|myonlinekingkong|LINK

Hi,

I have 3 tables in database,

1. Registration( Model, Cell, BodyNo,RegisDate, RegisTime)

2. Repair ( Model, Cell, BodyNo, RepairDate, RepairTime)

3. Borrow-return (Model, Cell, BodyNo, BorrowDate, BorrowTime, ReturnDate, ReturnTime)

How can I get data from 3 tables as above with condition RegisDate,

Ex: When user select Model, Cell then choose condition is RegisDate will be show the result from 3 tables above (allowed to have NULL from 1 or 2 table)

I used Outer join but result not exactly,

So pls help me to solve that trouble,

Thank in advance!

Member

290 Points

71 Posts

## Re: Query return data from 3 tables in sql

Feb 22, 2016 02:52 PM|gapimex|LINK

Hi,

Try something like this:

Hope this help

Member

5 Points

31 Posts

## Re: Query return data from 3 tables in sql

Feb 22, 2016 03:17 PM|myonlinekingkong|LINK

Hi gapimex,

Thank for your quick reply,

I need to get result with condition RegisDate, and I tried but the result not exactly,

And I also using FULL OUTER JOIN and the result the same,

Other Example:

Table1 have value as below:

A B 1 2

A B 3 4

A C 1 2

Table2 with value as below

A B 1 2

A B 3 4

A B 5 6

A C 1 2

And I want to get the result as below:

A B 1 2 A B 1 2

A B 3 4 A B 3 4

A B 5 6 A B NULL NULL

A C 1 2 A C 1 2

Pls help me to find out the result exactly,

Thank you!

Member

290 Points

71 Posts

## Re: Query return data from 3 tables in sql

Feb 22, 2016 05:50 PM|gapimex|LINK

You can post your query?

Participant

1775 Points

829 Posts

## Re: Query return data from 3 tables in sql

Feb 22, 2016 05:54 PM|TechView|LINK

SELECT * FROM Table1 a FULL OUTER JOIN Table2 b ON b.Col1 = a.Col1 AND b.Col2 = a.Col2 AND b.Col3 = a.Col3 AND b.Col4 = a.Col4

Hope helps.

TechView

Member

5 Points

31 Posts

## Re: Query return data from 3 tables in sql

Feb 23, 2016 12:16 PM|myonlinekingkong|LINK

Hi TechView and gapimex,

I used both of query command from you and using my command with condition where but I get the result not exactly also,

I think with tables structure as I posted as above, we can not get the result exactly,

Because 3 tables don't have relationship ( primary key link with foreign key of each table),

So anw,thank you for your support!

Star

7970 Points

1586 Posts

## Re: Query return data from 3 tables in sql

Mar 02, 2016 11:44 AM|Weibo Zhang|LINK

Hi myonlinekingkong,

Could you provide more things to explain the condition of “

RegisDate”?You’d better provide some sample record of your three tables and then give your expected output.Besides, in your second reply, the expected out is “A B 5 6 A B NULL NULL”, but there’s no related record in Table1, so I think it’s better to make the 5 and 6 column be null. Because, it’s not easy to say why they would not be “A B 5 6

A CNULL NULL” and why it would only be “A B 5 6A BNULL NULL”.Or, you could have a look at the following sample code that would return your expected result and maybe it could help you.

Best Regards,

Weibo Zhang