Joins

[rohithere]

Hi everybody,
Iam developing a report for my client.Suppose For eg: I have 3 tables ie.table1,table2,table3 and I want to inner join table2 and table3 to table1.Can it be done?

Eg:
SELECT blah,blah
FROM table1 inner join table2 on table1.id=table2.id inner join table3 on table1.pid=table3.pid
WHERE blah,blah
Group By blah,blah


Thanx in advance.

 

[AtomicWedgie]

Yes

 

[rohithere]

But what if the senario is like this:

There are 7 tables ie. from table1 to table7

SELECT blah,blah
FROM table1 inner join table2 on table1.id=table2.id inner join table3 on table2.pid=table3.pid inner join table4 on table2.abc=table4.abc inner join table5 on table1.xyz=table5.xyz inner join table6 on table5.xyz=table6.xyz inner join table7 on table5.xyz=table7.xyz

WHERE blah,blah
Group By blah,blah

 

[nigelrivett]

>> But what if the senario is like this:
What's the problem with that?
You can include up to 256 tables in a select statement.

You might want to split the query into parts using temp tables if you get optimisation problems.
I would advise all database access via stored procedures.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

But is this query correct way of joining tables :

SELECT blah,blah
FROM table1 inner join table2 on table1.id=table2.id inner join table3 on table2.pid=table3.pid inner join table4 on table2.abc=table4.abc inner join table5 on table1.xyz=table5.xyz inner join table6 on table5.xyz=table6.xyz inner join table7 on table5.xyz=table7.xyz

WHERE blah,blah
Group By blah,blah


Please explain how to do this :

"You might want to split the query into parts using temp tables if you get optimisation problems."

 

[nigelrivett]

>> But is this query correct way of joining tables
The syntax is correct - you have to consider the dtabase structure to tell if it will get the correct resultset.

>> You might want to split the query into parts using temp tables if you get optimisation problems.
>> Please explain how to do this :
(note the formatting)
select blah, blah
into #mytbl
from table1
inner join table2
on table1.id=table2.id
inner join table3
on table2.pid=table3.pid

select blah, blah
from #mytbl
inner join table4
on #mytbl.abc=table4.abc
inner join table5
on #mytbl.xyz=table5.xyz
inner join table6
on table5.xyz=table6.xyz
inner join table7
on table5.xyz=table7.xyz


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

Hi,
Parameters for report are :
Select Partners : All,mmc,amp etc.
Select Group : All,NBT001,NBT002,etc.
Select Destination : All,Afghanistan,Albania,etc.

For my report I have to show the foll columns :

grp.groupName,
grp.vendorid,
partMast.NimbusPartnerCode,
loc.locationName,
ord.orderDate,ct.rate,
ord.attendedBy,
ct.rechargable,
ct.ControlID,
ct.orderId,
ct.PINNo,
cust.name,
ord.userid,
ct.cardName,
cust.customerID


Which are available from the foll tables:

partnerMaster partMast,
groupp grp,
location loc,
customerTransaction ct
orders ord
customer cust
batch bt
Vendor vnd

The relations between these tables are :

Orders Table [PK orderid] ------ customerTransaction Table [FK orderid]

partnerMaster Table [PK partnerid] ----- customer Table [FK partnerid]

groupp Table [PK groupid] ------- batch Table [FK groupid]

location Table [PK locationid] ------ batch table [FK locationid]

vendor Table [PK vendorid] ------- groupp table [FK vendorid]

Orders Table (fields userid,partnerid,etc...) customerTransaction Table (field batchid,etc...)
batch table (fields groupid,locationid,etc...)


The parameters passed by user is:


select partner : All
select gruop : All
Select destination : Afghanistan - locationid is 1.


Now can any one tell me what would be the sql query to generate this report??
Iam using Stored procedures.


Thanx.

 

[nigelrivett]

Have a go yourself first - you know the join syntax so you should at least be able to join the tables together.
The parameters will be used in a where clause.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

Hi,
I got u r point & its correct.


Thanx.