Query tables with relationships established 3

[Michael57]

I have two tables that have relationships established but when I query the data it is as if the relationship does not exist. Do I have to restate the relationship inside the query?

 

[SQLDenis]

natural joins do not exist in sql server you have to specify the join condition

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Michael57]

How do I do this?

 

[SQLDenis]

example

select * from table1 t1
join table2 t2 on t1.column = t2.column

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Michael57]

Help me I'm new and a little lost. This is my current code:
Select JobAddChgHeader.[Index],[Work Rel#],PO#,Ref,Die,Description,JobAddChgDetail.Amount
From WorkProcess.dbo.JobAddChgHeader,WorkProcess.dbo.JobAddChgDetail
Where [Work Rel#]>0

I'm trying to relate JobAddChgHeader.[Index] to Ref

 

[SQLDenis]

Select jh.[Index],[Work Rel#],PO#,Ref,Die,Description,jd.Amount
From WorkProcess.dbo.JobAddChgHeader jh
join WorkProcess.dbo.JobAddChgDetail jd on jh.[Index] =jd.[Index]
Where [Work Rel#]>0

change [Index] to the column that both tables have
maybe you need a left join since I don't know your tables it is difficult to say

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[vbkris]

also try out the query window in enterprise manager.

if you have defined the relationships correctly it will write out the joins for you...

Known is handfull, Unknown is worldfull

 

[AlexCuse]

I'd have to disagree with vbkris. The query window in Enterprise Mangler has some limitations that can be very frustrating, and it doesn't always come up with the best code. Now is the perfect time to get started using query analyzer to write your queries so that you can REALLY understand SQL. Stop the bad habits before they start

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Michael57]

I've seen this query window but how do you add tables from different databases to it. It only seems to see the current database your in and you can only add tables from that database

 

[Michael57]

Where do I find "query analyser

 

[AlexCuse]

If you are already in EM, you just click Tools --> SQL Query Analyzer

(it should also be available in your start menu).

To reference a table in another database (same server) use its' full name:

[Database].[owner].

ie

AlexDB.dbo.MyTable

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

I think you should read this FAQ on joins. It might help you for the future.

Questions about posting. See faq183-874

 

[SQLSister]

Oops forgeot the link
JOIN Fundamentals faq183-4785

Questions about posting. See faq183-874

 

[vbkris]

>>The query window in Enterprise Mangler has some limitations that can be very frustrating, and it doesn't always come up with the best code

correct. but for writing out PURE join statements only its the best (assuming you have the relationships setup)! infact i use it a lot when i have to join more than 6-7 tables (no extra where clauses).

from there i copy the written SQL and process it further using the analyser...

Known is handfull, Unknown is worldfull

 

[AlexCuse]

Fair enough

Ignorance of certain subjects is a great part of wisdom