Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 25th, 2006, 11:48 AM
Registered User
 
Join Date: Oct 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to Create Join Script for a Query .......

Hi

i'm developing a VISUALL query builder as the same as EnterPrise manager for making Sql Server Query.I'm simulating it exactly.so my application has followings Panes:

    Diagram Pane
    Grid Pane
    Sql Pane
    Result Pane

User can drop any tables in the Diagram pane and then select fields and make relations(joins) as he/she wants for example suppose user drops Customers and Orders Tables from NorthWind Database in the diagram pane and make a join from Customers.CustomerID to Orders.CustomerID After that Sql Server in the Sql PAne
will write the followings script:

SELECT *
FROM Customers INNER JOIN
       Orders ON Customers.CustomerID = Orders.CustomerID

This is a simple join and my app is able to generate script for this query. Also in the background as you might have guessed i use a object structure for saving informaton about all the joins that user has made for example for the above query i create a Join Object and fill it with this info:

 Left Table Name : Customers
 Left Field Name : CustomerID

 Right Table Name : Orders
 Right Field Name : CustomerID

 Join Type : Inner join
 Join Operator : =

For each join i will do this.So I have all information about Joins that user has made Visually.

But until now this was a very simple Query with one join .Imagine we have the following query.It is a right query and it has not grammatical mistake and can be Executed properly regardless of its logics.

SELECT *
FROM Orders INNER JOIN
       Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
       Products ON Orders.OrderID = Products.ProductID AND
       Orders.EmployeeID = Products.SupplierID AND
       Customers.CompanyName = Products.ProductName

Now in my app i have this information:

  Join 1:
         Left Table Name : Customers
         Left Field Name : CustomerID

         Right Table Name : Orders
         Right Field Name : CustomerID

         Join Type : Inner join
         Join Operator : =

  Join 2:
         Left Table Name : Products
         Left Field Name : ProductName

         Right Table Name : Customers
         Right Field Name : CompanyName

         Join Type : Inner join
         Join Operator : =

  Join 3:
         Left Table Name : Products
         Left Field Name : ProductID

         Right Table Name : Orders
         Right Field Name : OrderID

         Join Type : Inner join
         Join Operator : =

  Join 4:
         Left Table Name : Products
         Left Field Name : SuplierID

         Right Table Name : Orders
         Right Field Name : EmployeeID

         Join Type : Inner join
         Join Operator : =

Now i'm looking for a instruction ,a way or an algorithm to attach above information to achieve following Join Script as mentioned sql Query.

FROM Orders INNER JOIN
       Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
       Products ON Orders.OrderID = Products.ProductID AND
       Orders.EmployeeID = Products.SupplierID AND
       Customers.CompanyName = Products.ProductName

or in the other words a functon with this specification:

function GetJoinScriptFor(List of Join objects as a Array Parameter) : String containing Join Script.

I think at this point you should figure out my problem, am i right?

I tried to follow the lead of EnterPrise manager But sometimes
i can't guess its method.i hope you help me.
Thanks for your patience to follow my problem.

Thanks in advance.
With Best Regards.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested Query or Join? rlull SQL Server 2000 4 November 10th, 2005 05:23 PM
Opposite to INNER JOIN query crisan Access 1 April 29th, 2005 06:50 AM
JOIN query wverner Access 2 April 5th, 2005 01:44 PM
Join Query tsimsha Classic ASP Databases 2 December 2nd, 2004 09:31 AM
Strange join query nikosdra SQL Language 1 August 10th, 2003 06:10 AM



All times are GMT -4. The time now is 01:04 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.