|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to SELECT data among 4 tables?I have 4 tables: Summary_Table Header_Table Detail_Table_1 Detail_Table_2 Summary_TableID is defined on both Summary_Table and Header_Table. Summary_Table is like a parent table to Header_Table. Header_TableID is defined on Header_Table, Detail_Table_1 and Detail_Table_2. Header_Table is like a parent to Detail_Table_1 and Detail_Table_2. What I try to do is to retrieve records from all 4 tables. First I join Summary_Table and Header_Table together by using the Summary_TableID on both tables. If it returns record, I will join the returned Header_Table’s Header_TableID to both Detail_Table_1 and Detail_Table_2’s Header_TableID. Joining Header_Table to Detail_Table_1 and Detail_Table_2 might or might not return any record. If it does, the select statement will contain all four table info. If not, it will only contain Summary_Table and Header_Table info. Detail_Table_1 and Detail_Table_2 fields will returns NULL. I tried to use Left Outer Join and DISTINCT keyword below but it doesn’t give me the correct result. Could you help? Thanks. SELECT distinct Summary_Table.FIELD1, Summary_Table.FIELD2, Header_Table.FIELD1, Header_Table.FIELD2, Detail_Table_1.FIELD1, Detail_Table_1.FIELD2, Detail_Table_2.FIELD1, Detail_Table_2.FIELD2, FROM Summary_Table WITH (NOLOCK) JOIN Header_Table on Summary_Table.Summary_TableID = Header_Table. Summary_TableID LEFT OUTER JOIN Detail_Table_1 ON Header_Table.Header_TableID = Detail_Table_1. Header_TableID LEFT OUTER JOIN Detail_Table_2 ON Header_Table.Header_TableID = Detail_Table_2. Header_TableID WHERE Summary_Table.MYDATE BETWEEN @vchStartDate AND @vchEndDate Thanks. Abel Chan Well, other than the NOLOCK only applying to one of the tables, and having 2
detail tables,I don't see any problem with the query. What is wrong with the result? I can guess it has to do with the use of distinct, but more details are necessary. -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Abel Chan" <awong@newsgroup.nospam> wrote in message news:744A6350-919F-483B-911A-C8252A3A812E@microsoft.com... > Hi there, > > I have 4 tables: > Summary_Table > Header_Table > Detail_Table_1 > Detail_Table_2 > > Summary_TableID is defined on both Summary_Table and Header_Table. > Summary_Table is like a parent table to Header_Table. > > Header_TableID is defined on Header_Table, Detail_Table_1 and > Detail_Table_2. Header_Table is like a parent to Detail_Table_1 and > Detail_Table_2. > > What I try to do is to retrieve records from all 4 tables. First I join > Summary_Table and Header_Table together by using the Summary_TableID on > both > tables. If it returns record, I will join the returned Header_Table's > Header_TableID to both Detail_Table_1 and Detail_Table_2's Header_TableID. > Joining Header_Table to Detail_Table_1 and Detail_Table_2 might or might > not > return any record. If it does, the select statement will contain all four > table info. If not, it will only contain Summary_Table and Header_Table > info. Detail_Table_1 and Detail_Table_2 fields will returns NULL. > > I tried to use Left Outer Join and DISTINCT keyword below but it doesn't > give me the correct result. Could you help? Thanks. > > > SELECT distinct > Summary_Table.FIELD1, > Summary_Table.FIELD2, > Header_Table.FIELD1, > Header_Table.FIELD2, > Detail_Table_1.FIELD1, > Detail_Table_1.FIELD2, > Detail_Table_2.FIELD1, > Detail_Table_2.FIELD2, > FROM > Summary_Table > WITH (NOLOCK) > JOIN Header_Table > on Summary_Table.Summary_TableID = Header_Table. Summary_TableID > LEFT OUTER JOIN Detail_Table_1 > ON Header_Table.Header_TableID = Detail_Table_1. Header_TableID > LEFT OUTER JOIN Detail_Table_2 > ON Header_Table.Header_TableID = Detail_Table_2. Header_TableID > WHERE > Summary_Table.MYDATE BETWEEN @vchStartDate AND @vchEndDate > > Thanks. > > Abel Chan > > Hi Abel. You may want to rethink your naming convention. It can get quite
confusing to use 'table' in name of object referencing a table. You end up saying "The foriegn key of the Customers_table table, is related to the Orders_table table". It is usually understood that an object is a table because it is under the Tables folder in Enterprise Manager. I would also suggest referencing some books on the subject of "data analysis and design" for best practices. Using ficticious scenario, allow me to outline example that may clear things up a little... Customers PKId (primary key) Name Orders PKId (pk) Name OrderDate CustomerOrders PKId (pk) CustomerId (foriegn key - relative to Customers.PKId) OrderId (fk - relative to Orders.PKId) Now to select all orders a specific customer has and use their name as a criteria, the following select... select c.Name, o.Name, o.OrderDate from c Customers, o Orders, co CustomerOrders where c.Name = "John" and c.PKId *= o.PKId The Customers and Orders tables are known as primary tables. The CustomerOrders table is a relationship table. Notice all the tables have a primary key named PKId. They don't include any part of the table name in the field name because they will be reference by the table name. For example, Customers.PKId can be assumed this is the primary key of the Customers table. However, notice when referencing the PKId of a table in another (called a foriegn key), we use the table name. For example, in making the relationship between the Customers and Orders in the CustomerOrders table, we call the PKId of the Customers table 'CustomerId'. To become proficient in database design, one should reference materials related to systems/database analysis and design. Good luck! Adam Show quote "Abel Chan" <awong@newsgroup.nospam> wrote in message news:744A6350-919F-483B-911A-C8252A3A812E@microsoft.com... > Hi there, > > I have 4 tables: > Summary_Table > Header_Table > Detail_Table_1 > Detail_Table_2 > > Summary_TableID is defined on both Summary_Table and Header_Table. > Summary_Table is like a parent table to Header_Table. > > Header_TableID is defined on Header_Table, Detail_Table_1 and > Detail_Table_2. Header_Table is like a parent to Detail_Table_1 and > Detail_Table_2. > > What I try to do is to retrieve records from all 4 tables. First I join > Summary_Table and Header_Table together by using the Summary_TableID on both > tables. If it returns record, I will join the returned Header_Table's > Header_TableID to both Detail_Table_1 and Detail_Table_2's Header_TableID. > Joining Header_Table to Detail_Table_1 and Detail_Table_2 might or might not > return any record. If it does, the select statement will contain all four > table info. If not, it will only contain Summary_Table and Header_Table > info. Detail_Table_1 and Detail_Table_2 fields will returns NULL. > > I tried to use Left Outer Join and DISTINCT keyword below but it doesn't > give me the correct result. Could you help? Thanks. > > > SELECT distinct > Summary_Table.FIELD1, > Summary_Table.FIELD2, > Header_Table.FIELD1, > Header_Table.FIELD2, > Detail_Table_1.FIELD1, > Detail_Table_1.FIELD2, > Detail_Table_2.FIELD1, > Detail_Table_2.FIELD2, > FROM > Summary_Table > WITH (NOLOCK) > JOIN Header_Table > on Summary_Table.Summary_TableID = Header_Table. Summary_TableID > LEFT OUTER JOIN Detail_Table_1 > ON Header_Table.Header_TableID = Detail_Table_1. Header_TableID > LEFT OUTER JOIN Detail_Table_2 > ON Header_Table.Header_TableID = Detail_Table_2. Header_TableID > WHERE > Summary_Table.MYDATE BETWEEN @vchStartDate AND @vchEndDate > > Thanks. > > Abel Chan > > Hi Adam,
Thanks for your suggestion. I will look up subject of data analysis and design for best practices. Abel Show quote "Adam Cox" wrote: > Hi Abel. You may want to rethink your naming convention. It can get quite > confusing to use 'table' in name of object referencing a table. You end up > saying "The foriegn key of the Customers_table table, is related to the > Orders_table table". It is usually understood that an object is a table > because it is under the Tables folder in Enterprise Manager. I would also > suggest referencing some books on the subject of "data analysis and design" > for best practices. > > Using ficticious scenario, allow me to outline example that may clear things > up a little... > > Customers > PKId (primary key) > Name > Orders > PKId (pk) > Name > OrderDate > CustomerOrders > PKId (pk) > CustomerId (foriegn key - relative to Customers.PKId) > OrderId (fk - relative to Orders.PKId) > > Now to select all orders a specific customer has and use their name as a > criteria, the following select... > > select c.Name, o.Name, o.OrderDate > from c Customers, o Orders, co CustomerOrders > where c.Name = "John" > and c.PKId *= o.PKId > > The Customers and Orders tables are known as primary tables. The > CustomerOrders table is a relationship table. Notice all the tables have a > primary key named PKId. They don't include any part of the table name in > the field name because they will be reference by the table name. For > example, Customers.PKId can be assumed this is the primary key of the > Customers table. However, notice when referencing the PKId of a table in > another (called a foriegn key), we use the table name. For example, in > making the relationship between the Customers and Orders in the > CustomerOrders table, we call the PKId of the Customers table 'CustomerId'. > > To become proficient in database design, one should reference materials > related to systems/database analysis and design. > > Good luck! > > Adam > > > "Abel Chan" <awong@newsgroup.nospam> wrote in message > news:744A6350-919F-483B-911A-C8252A3A812E@microsoft.com... > > Hi there, > > > > I have 4 tables: > > Summary_Table > > Header_Table > > Detail_Table_1 > > Detail_Table_2 > > > > Summary_TableID is defined on both Summary_Table and Header_Table. > > Summary_Table is like a parent table to Header_Table. > > > > Header_TableID is defined on Header_Table, Detail_Table_1 and > > Detail_Table_2. Header_Table is like a parent to Detail_Table_1 and > > Detail_Table_2. > > > > What I try to do is to retrieve records from all 4 tables. First I join > > Summary_Table and Header_Table together by using the Summary_TableID on > both > > tables. If it returns record, I will join the returned Header_Table's > > Header_TableID to both Detail_Table_1 and Detail_Table_2's Header_TableID. > > Joining Header_Table to Detail_Table_1 and Detail_Table_2 might or might > not > > return any record. If it does, the select statement will contain all four > > table info. If not, it will only contain Summary_Table and Header_Table > > info. Detail_Table_1 and Detail_Table_2 fields will returns NULL. > > > > I tried to use Left Outer Join and DISTINCT keyword below but it doesn't > > give me the correct result. Could you help? Thanks. > > > > > > SELECT distinct > > Summary_Table.FIELD1, > > Summary_Table.FIELD2, > > Header_Table.FIELD1, > > Header_Table.FIELD2, > > Detail_Table_1.FIELD1, > > Detail_Table_1.FIELD2, > > Detail_Table_2.FIELD1, > > Detail_Table_2.FIELD2, > > FROM > > Summary_Table > > WITH (NOLOCK) > > JOIN Header_Table > > on Summary_Table.Summary_TableID = Header_Table. Summary_TableID > > LEFT OUTER JOIN Detail_Table_1 > > ON Header_Table.Header_TableID = Detail_Table_1. Header_TableID > > LEFT OUTER JOIN Detail_Table_2 > > ON Header_Table.Header_TableID = Detail_Table_2. Header_TableID > > WHERE > > Summary_Table.MYDATE BETWEEN @vchStartDate AND @vchEndDate > > > > Thanks. > > > > Abel Chan > > > > > > > Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. Next, please read any basic RDBMS book so you will stop confusing rows and records, fields and columns, and using that silly redundant "table" in table names (SQL only has one data structure). And I hope that "@vchStartDate" is temporal and not a VARCHAR(n). Look up what "attribute splitting" is so you will stop doing it. I assume that is why you the absurd "Detail_Table_1" and "Detail_Table_2" as if you were working with arrays. Look up why we do not mix data and metadata in a schema, Then if the Summary table is actually a summary, why are you storing computed data? Is this a Data Warehouse or what? Hi,
For your scenario, currenlty I'd suggest please write this as one stored procedure and then we could firstly get the query result from Header_Table and Summary_Table; then perform the follow-up operations according to your design. For example: declare @result int select @result=count(1) FROM WITH (NOLOCK) JOIN Header_Table on Summary_Table.Summary_TableID = Header_Table. Summary_TableID if 0 = @result -- we get no result from this query else -- we have the value, join more tables If you have any further question, please feel free to let us know! Enjoy a nice weekend! Best Regards, Wei-Dong XU Microsoft Support --------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. --------------------------------------------------------------------------- It is my pleasure to be of any assistance. Explain? I don't see that this helps? I mean if you don't get any data
from the first join, this means that ther are no rows at all in the child table. Plus, this would be far faster written as an exists: If exists (select * FROM Summary_Table WITH (NOLOCK) --this is still kind of strange JOIN Header_Table on Summary_Table.Summary_TableID = Header_Table. Summary_TableID) -- we have the value, join more tables else -- we get no result from this query The problem is, without knowledge of the exact issue (which we can find out if Abel would give us some data.) His query seemed ok to me, which really says that there is possibly something wrong in the data. -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Wei-Dong XU [MS]" <v-w***@online.microsoft.com> wrote in message news:lsIodxwIGHA.1236@TK2MSFTNGXA02.phx.gbl... > > Hi, > > For your scenario, currenlty I'd suggest please write this as one stored > procedure and then we could firstly get the query result from > Header_Table > and Summary_Table; then perform the follow-up operations according to your > design. For example: > > declare @result int > select @result=count(1) > FROM > WITH (NOLOCK) > JOIN Header_Table > on Summary_Table.Summary_TableID = Header_Table. Summary_TableID > > if 0 = @result > -- we get no result from this query > else > -- we have the value, join more tables > > > If you have any further question, please feel free to let us know! Enjoy a > nice weekend! > > Best Regards, > Wei-Dong XU > Microsoft Support > --------------------------------------------------------------------------- > This posting is provided "AS IS" with no warranties, and confers no > rights. > --------------------------------------------------------------------------- > It is my pleasure to be of any assistance. > > Hi all,
To clarify the issue, I provided an extract of the actual code. Forgive me all (and Adam). I should follow the best practice next time. Header Table: CREATE TABLE dbo.tbOrderHeader ( intHeaderId int IDENTITY (1, 1) NOT NULL ,dtmInsertDate datetime NOT NULL ,dtmUpdateDate datetime NOT NULL ,tiRecordStatus tinyint NOT NULL ) Detail Table: CREATE TABLE dbo.tbOrderDetail ( intDetailId int IDENTITY (1, 1) NOT NULL ,intHeaderId int NOT NULL ,dtmInsertDate datetime NOT NULL ,dtmUpdateDate datetime NOT NULL ,tiRecordStatus tinyint NOT NULL ) Adjustment Table: CREATE TABLE dbo.tbAdjustment( intAdjustmentId int IDENTITY (1, 1) NOT NULL ,intDetailId int NOT NULL ,dtmInsertDate datetime NOT NULL ,dtmUpdateDate datetime NOT NULL ,tiRecordStatus tinyint NOT NULL ) Validation Table: CREATE TABLE dbo.tbValidation( intValidationId int IDENTITY (1, 1) NOT NULL ,intDetailId int NOT NULL ,dtmInsertDate datetime NOT NULL ,dtmUpdateDate datetime NOT NULL ,tiRecordStatus tinyint NOT NULL ) If joining Header and Detail returns some records, I need to find out whether there is any record under Adjustment and Validation. There could be multiple records of Adjustment and validation under each detail record. When I think more carefully, I understand why my select statement won’t work. This is because there is no direct relationship between Adjustment and Validation. They are just like separate child under the Detail table. By using a single SELECT statement to return Header, Detail, Adjustment and Validation, the number of returning records will be: (# of records Joining Header and Detail) * Adjustment * Validation. That is exactly what happened. I just need to create three separate SELECT statements to handle all the information. Thanks again. Abel That does make more sense.
> To clarify the issue, I provided an extract of the actual code. Forgive No worries at all. It is hard to really put together a test case, but it > me > all (and Adam). I should follow the best practice next time. sure does hurry thing along when you provide it :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arguments are to be avoided: they are always vulgar and often convincing." (Oscar Wilde) "Abel Chan" <awong@newsgroup.nospam> wrote in message news:2765C5A8-B8B7-4AA3-934D-E0EA887F701D@microsoft.com... > Hi all, > > To clarify the issue, I provided an extract of the actual code. Forgive > me > all (and Adam). I should follow the best practice next time. > > Header Table: > > CREATE TABLE dbo.tbOrderHeader ( > intHeaderId int IDENTITY (1, 1) NOT NULL > ,dtmInsertDate datetime NOT NULL > ,dtmUpdateDate datetime NOT NULL > ,tiRecordStatus tinyint NOT NULL > ) > > Detail Table: > > CREATE TABLE dbo.tbOrderDetail ( > intDetailId int IDENTITY (1, 1) NOT NULL > ,intHeaderId int NOT NULL > ,dtmInsertDate datetime NOT NULL > ,dtmUpdateDate datetime NOT NULL > ,tiRecordStatus tinyint NOT NULL > ) > > Adjustment Table: > > CREATE TABLE dbo.tbAdjustment( > intAdjustmentId int IDENTITY (1, 1) NOT NULL > ,intDetailId int NOT NULL > ,dtmInsertDate datetime NOT NULL > ,dtmUpdateDate datetime NOT NULL > ,tiRecordStatus tinyint NOT NULL > ) > > Validation Table: > > CREATE TABLE dbo.tbValidation( > intValidationId int IDENTITY (1, 1) NOT NULL > ,intDetailId int NOT NULL > ,dtmInsertDate datetime NOT NULL > ,dtmUpdateDate datetime NOT NULL > ,tiRecordStatus tinyint NOT NULL > ) > > If joining Header and Detail returns some records, I need to find out > whether there is any record under Adjustment and Validation. There could > be > multiple records of Adjustment and validation under each detail record. > > When I think more carefully, I understand why my select statement won't > work. This is because there is no direct relationship between Adjustment > and > Validation. They are just like separate child under the Detail table. By > using a single SELECT statement to return Header, Detail, Adjustment and > Validation, the number of returning records will be: > > (# of records Joining Header and Detail) * Adjustment * Validation. > > That is exactly what happened. I just need to create three separate > SELECT > statements to handle all the information. > > Thanks again. > > Abel > >  |
|||||||||||||||||||||||
Other interesting topics