Multiple Table Query - 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

Multiple Table Query

Author

Topic

barrowjohn
Starting Member

6 Posts

Posted - 2008-08-14 : 03:47:12

Hi

I'm new to SQl so please bare with me.

I'm trying to collate date from a number of tables and am getting an occurring problem. I've attached the script I'm using. I only have read only access to the database and the user requires the output to be loaded into Excel so I can't do anything to fancy. This is happening with 1 record but it will have to look at 80,000 records once I can get the SQL working. The Quotation table has a unique reference number and this has a one-to-many relationship with the Article table.

This bit works -

SELECT QUOTATION.QUO_Nr, PRODUCT_QUOTATION.ART_Nr

FROM QUOTATION, PRODUCT_QUOTATION

WHERE QUOTATION.QUO_Nr = PRODUCT_QUOTATION.QUO_Nr AND
QUOTATION.QUO_Nr = '283323'

QUO_Nr ART_Nr
----------- -----------
283323 467514
283323 467516
283323 467518
283323 467520

(4 row(s) affected)

But I get this when I add an extra table into the SQL -

SELECT QUOTATION.QUO_Nr, PRODUCT_QUOTATION.ART_Nr, PRODUCT_QUOTATION_PRICE.PQUOP_ToQty

FROM QUOTATION, PRODUCT_QUOTATION, ARTICLE, PRODUCT_QUOTATION_PRICE

WHERE QUOTATION.QUO_Nr = PRODUCT_QUOTATION.QUO_Nr AND
PRODUCT_QUOTATION.ART_Nr = ARTICLE.ART_Nr AND
PRODUCT_QUOTATION_PRICE.QUO_Nr = QUOTATION.QUO_Nr AND
QUOTATION.QUO_Nr = '283323'

QUO_Nr ART_Nr PQUOP_ToQty
----------- ----------- -----------------------------------------------------
283323 467514 40000.0
283323 467514 53000.0
283323 467514 7000.0
283323 467514 24000.0
283323 467516 40000.0
283323 467516 53000.0
283323 467516 7000.0
283323 467516 24000.0
283323 467518 40000.0
283323 467518 53000.0
283323 467518 7000.0
283323 467518 24000.0
283323 467520 40000.0
283323 467520 53000.0
283323 467520 7000.0
283323 467520 24000.0

(16 row(s) affected)

Any help will be greatly appreciated

Thanks
John

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 03:56:29

what do you want to do with the multiple values for PQUOP_ToQty ? do you want 1 of them? a sum of all of them?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 04:01:03

FTR, this is your query written with standard join syntax - a lot clearer to read...


SELECT QUOTATION.QUO_Nr, PRODUCT_QUOTATION.ART_Nr, PRODUCT_QUOTATION_PRICE.PQUOP_ToQty
FROM QUOTATION 
		join PRODUCT_QUOTATION on  QUOTATION.QUO_Nr = PRODUCT_QUOTATION.QUO_Nr
		join ARTICLE on PRODUCT_QUOTATION.ART_Nr = ARTICLE.ART_Nr
		join PRODUCT_QUOTATION_PRICE on PRODUCT_QUOTATION_PRICE.QUO_Nr = QUOTATION.QUO_Nr
WHERE QUOTATION.QUO_Nr = '283323'

what is join to Article for? perhaps some sample data would help illustrate your problem better?

Em

barrowjohn
Starting Member

6 Posts

Posted - 2008-08-14 : 04:16:00

Thanks for your speedy reply.

I've only included 3 fields of output but there will be 40 in the report and some of them will come from the ARTICLE table.

I want to show the records for each quotation number whether it has 1 article or a number of articles. Therefore, if it has 4 Articles it will have 4 lines of output with it's appropriate data.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 04:18:33

so which PQUOP_ToQty record do you want to display?

Em

barrowjohn
Starting Member

6 Posts

Posted - 2008-08-14 : 04:31:39

The one that is associated with it's ARTICLE Ref_Nr.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 04:37:10

show us the table structures and some sample data as it's not obvious from your query how that relationship is inferred

Em

barrowjohn
Starting Member

6 Posts

Posted - 2008-08-14 : 04:37:58

I've included some more fields to show that although the quotation number has stayed them same each of the 4 article's have different data. As the PQUOP_ToQty will be different to each.

QUO_Nr ART_Nr ART_Lgt ART_Wdt ART_Hgt
----------- ----------- ----------- ------- -------
283323 467514 272 159 226
283323 467516 272 126 292
283323 467518 268 235 232
283323 467520 331 156 266

(4 row(s) affected)

barrowjohn
Starting Member

6 Posts

Posted - 2008-08-14 : 04:55:07

There are over 400 tables so I've tried to look at the approriate ones.

In the QUOTATION module there are 18 tables of which there are 3 that I'm using - QUOTATION, PRODUCT_QUOTATION and PRODUCT_QUOTATION_PRICE

QUOTATION's primary key is QUO_Nr
PRODUCT_QUOTATION's primary key is PQUO_SeqNr but also has QUO_Nr as a secondary key. Within this table is a field for ART_Nr
PRODUCT_QUOTATION_PRICE's primary key is PQUO_RangeSeqNr but also has QUO_Nr and PQUO_SeqNr as secondary keys.

In the ARTICLE and PRODUCT module there are 65 tables of which there is 1 that I'm using - ARTICLE

ARTICLE's promary key is ART_Nr.

Hope this helps

Thanks again
John

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 04:55:40

what we really need is the table structure (i.e. create table...) and some sample data for each table (i.e. insert into...) so we can reproduce on a local system, see the relationsip between the tables and produce your query to match the output you posted originally. from what you've posted so far i've no idea how that Art_Nr relates to the PQUOP_ToQty etc

Em

barrowjohn
Starting Member

6 Posts

Posted - 2008-08-14 : 05:21:12

Not sure if I can create a table as I only have read access.

I think the only relationship between the QUOTATION and ARTICLE tables is that there the fields QUO_Nr and ART_nr are in the PRODUCT_QUOTATION table

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 05:26:43

you don't need to create the table just script it out for us. or at least list the columns for each table, and provide some sample data for each.

you say you want the PQUOP_ToQty that is associated with it's Article Ref_Nr. i've seen nothing yet to give us a clue where / what that is?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 05:27:54

see this for an example...

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources