Joining 3 tables - SQL Server Forums

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

Joining 3 tables

Author

Topic

nbalraj
Starting Member

21 Posts

Posted - 2011-04-07 : 10:37:59

Hi,

Here is the situation. I have 3 tables to join.

1 master table 2 child tables

mastertable fields
cntParent strName strdept

child table1 (more than 1 record)
cntChild1 cntparent strdeptcategory

child table2 (more than 1 record)
cntchild2 cntParent cntValue1 cntvalue2

The output i needed should join all the three with the following conditions

take the max (1) record details from child table1 joining the cntparent with master table details and then join that with all the child table2 records

so the display will have total number of rows equal to that of child table2 but 1 record from childtable1 and master table information joined together.

Hope my help is clear.

Thanks,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 10:44:31

[code]select
*
from
mastertable m
inner join childTable2 c2 on c2.cntParent = m.cntParent
cross apply
(
select top 1 * from childTable1 c1 where c1.cntParent = m.cntParent
order by c1.cntChild1 desc/*Which max(1) record? This takes max(cntChild1) */
) c1[/code]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-07 : 16:30:00

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

Rows are not records. Columns are not fields. The terms "parent' and "child" come from Network databases and not RDBMS. We never include a data type are part of a data element (That was BASIC, not SQL).

Try again, with sample data and we can try to turn this thing in SQL.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources