Join Question - Is this possible in a single SQL statement?

[SimonPGreen]

Hi all,

Can anyone help with the syntax (if its possible) to achieve the following in one SELECT statement:

Have 2 Access DB's
DB 1 has 3 tables; tblcontracts/tbljobcards/tblbookings
DB 2 has 3 tables; tblpricecontracts/tblpricejobcards/tblpricepricework
Tables are all the same structure adn relationships.
The first DB has links to the second so that all tables are available for query in DB 1.

I have produced SQL as follows to list all contracts respectively for paid hours and pricework(cut down versions)

Statement 1
select tblcontracts.contractref,tblcontracts.contractname,tblbookings.basic
from tblcontracts
inner join
tbljobcards
on tblcontracts.contractid = tbljobcards.contractd
inner join
tblbookings
on tbljobcards.jobcardid = tblbookings.jobcardid
group by ..... etc

Statement 2
select tblpricecontracts.contractref,tblpricecontracts.contractname,tblpricework.price
from tblpricecontracts
inner join
tblpricejobcards
on tblpricecontracts.contractid = tblpricejobcards.contractd
inner join
tblpricework
on tblpricejobcards.jobcardid = tblpricepricework.jobcardid
group by ..... etc

I obviously now have two SQL queries that both have a related contractid value but I can't work out how to write a single set of join statements that can combine all of the tables as they fork off from two common tables.

PS I know that the DB design should really have been a single DB but this is what I have got.
I have managed to write a query that calls the first two queries and achieves the objective but I am interested if there is a better way

Kind Regards,

Simon Green

 

[alvechurchdata]

I have managed to write a query that calls the first two queries and achieves the objective but I am interested if there is a better way

If your solution runs quickly enough then I'd say you've found the better way already. Your solution works and putting it all into one query will give you a very complex statement which will be difficult to debug and maintain.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lespaul]

do you mean that you want all of the results in a single statement?

select tblcontracts.contractref,tblcontracts.contractname,tblbookings.basic
from tblcontracts
inner join
tbljobcards
on tblcontracts.contractid = tbljobcards.contractd
inner join 
tblbookings
on tbljobcards.jobcardid = tblbookings.jobcardid
group by ..... etc
[b]UNION[/b]
select tblpricecontracts.contractref,tblpricecontracts.contractname,tblpricework.price
from tblpricecontracts
inner join
tblpricejobcards
on tblpricecontracts.contractid = tblpricejobcards.contractd
inner join 
tblpricework
on tblpricejobcards.jobcardid = tblpricepricework.jobcardid
group by ..... etc

adding a UNION to the two statements will bring all the results in a single set.

Does that help?

Leslie

 

[SimonPGreen]

Thanks for the reply Leslie.

I am new to this but my undestanding of union is that it will combine both sets of information that do not share a common field?

In this case both queries return a contract reference that is common in both and hence can be related?

Am I misunderstanding something?

Regards,

Simon

 

[lespaul]

a UNION query will combine the results of two queries as long as the number of fields in the SELECT statement correspond:

SELECT Field1, Field2, Field3 FROM Table1
UNION
SELECT Field4, Field5, Field6 FROM Table2

if there are fields in the first query that don't exist in the second query, you can use place holders:

SELECT Field1, Field2, Field3 FROM Table1
UNION
SELECT Field4, Field5, "" FROM Table2

The only thing I can think of that might affect you is the UNION removes duplicates, you have to use UNION ALL if you want to include the duplicates. For instance, if you want a list of all people who are students and all people who are teachers, but there are some students who are also teachers:

SELECT * FROM PEOPLE WHERE TYPE = 'S'
UNION
SELECT * FROM PEOPLE WHERE TYPE = 'T'

this query would return a single record for a person who both a student and a teacher.

SELECT * FROM PEOPLE WHERE TYPE = 'S'
UNION ALL
SELECT * FROM PEOPLE WHERE TYPE = 'T'

in the second query you would get two records for a person who is both a student and a teacher.

Leslie

 

[SimonPGreen]

Thanks Leslie that clarifies things a little.

A couple more questions:

If the select statements use aggregate functions do the 'group by' statements go before the UNION keyword?

Do aggregate functions in one select require "" placeholders in the other?

I have the following code thus far but unfortunately it bombs

select tblcontracts.contractref as 'Contract Number', tblcontracts.contractname as 'Contract Name', sum(tblbookings.basic) as 'Normal Time',sum(tblbookings.timehalf) as 'Time and Half',sum(tblbookings.double) as 'Double Time', ""
from
(tblcontracts
inner join
tbljobcards
on tblcontracts.contractid = tbljobcards.contractid)
inner join
tblbookings
on tbljobcards.jobcardid = tblbookings.jobcardid
group by
tblcontracts.contractref,
tblcontract.contractname
union
select tblpricecontracts.contractref as 'Contract Number',"","","","","",sum(tblpricepricework.price) as 'Price'
(tblpricecontracts
inner join
tblpricejobcards
tblpricecontracts.contractid = tblpricejobcards.contractid)
inner join
tblpricepricework
on tblpricejobcards.jobcardid = tblpricepricework.jobcardid
group by
tblpricecontracts.contractref

Any help appreciated.

Regards,

Simon

 

[lespaul]

well, I don't know if this is a cut and paste error, but you don't have a FROM clause in the second query.

your select fields don't match up either:

select tblcontracts.contractref as 'Contract Number', tblcontracts.contractname as 'Contract Name', sum(tblbookings.basic) as 'Normal Time', sum(tblbookings.timehalf) as 'Time and Half', sum(tblbookings.double) as 'Double Time', ""

select tblpricecontracts.contractref,"","","","","",sum(tblpricepricework.price) as 'Price'

Each Select needs a red green blue red green blue and you've got an extra purple at the end!

you also don't need the duplicate AS Contract Number in the second SELECT.

HTH

Leslie

 

[SimonPGreen]

Thanks for sticking with this Leslie.

I now have a working query however the result is not as I expected. At present the query below provides a row at the head of the recordset giving sum of all price. If I add tblpricecontracts.contractref to the lower select and group by it I get two rows for each contract in the recordset, one containing all 'hours' and the other with just the price.

What I actually want to achieve (if possible) is a single row for each contract i.e.

Contract Number Contract Name Normal .. Total Price
A012 London Job 25 .. 3483.00
A013 Ipswich 35 .. 382.00

Again many thanks for your time.

Regards,

Simon

select tblcontracts.contractref as ['Contract Number'],
tblcontracts.contractname as ['Contract Name'],
sum(tblbookings.basic) as ['Normal Time'],
sum(tblbookings.timehalf) as ['Time and Half'],
sum(tblbookings.double) as ['Double Time'],
nz(sum(tblbookings.basic),0) + nz(sum(tblbookings.timehalf),0) + nz(sum(tblbookings.double),0) as ['Total Hours'],
['Total Hours'] * 34.33 as ['Selling Value'],
""
from
(tblcontracts
inner join
tbljobcards
on tblcontracts.contractid=tbljobcards.contractid)
inner join
tblbookings
on tbljobcards.jobcardid=tblbookings.jobcardid
group by
tblcontracts.contractref,
tblcontracts.contractname
UNION select"","","","","","","", nz(sum(tblpricepricework.price),0) as ['Total Price']
from
(tblpricecontracts
inner join
tblpricejobcards
on tblpricecontracts.contractid = tblpricejobcards.contractid)
inner join
tblpricepricework
on tblpricejobcards.jobcardid = tblpricepricework.jobcardid;

 

[lespaul]

Ok let's start fresh. Can you list all the related tables and the needed fields (and indicate FKs), provide some sample raw data, and your expected results from that data?

tblContracts
ContractID (PK?)
ContractName

tblBookings
JobCardID (PK?)
Basic
TimeHalf
Double

etc.

Les

 

[SimonPGreen]

Leslie,

The table details as follows;

tblcontracts
ContractID (PK)
ContractName


tblJobcards
JobCardID (PK)
JobCardNo
ContractID (FK)

tblBookings
JobBookingID (PK)
JobCardID (FK)
Basic
TimeHalf
Double
Date

The following are linked tables from another DB (Basically the same structure and should really have been in the first DB!)

tblPriceContracts
ContractID (PK)
ContractName

tblPriceJobcards
JobCardID (PK)
JobCardNo
ContractID (FK)

tblPricePricework
PriceworkID (PK)
JobcardID (FK)
Date
Price

Sample Date
TblContracts
ContractId 'Auto number'
ContractRef 'A012'
ContractName 'Archive'

TblJobcards
JobCardID 'Auto number'
JobCardNo '6128'
ContractId 'FK in tblcontracts'

TblBookings
JobBookingID 'Auto number'
JobCardID 'FK in tblJobcards'
Basic '9'
Timehalf '1.5'
Double '1'
Date '12/1/2005'

TblPriceContracts - same as above
TblPriceJobcards - same as above
TblPricePricework
PriceworkID 'Auto Number'
JobCardId 'FK in tblPriceJobcards'
Date '12/1/2005'
Price '35.00'

What I would like to achieve is a single query that would list the following as a result grouped by contract with the values [Basic] [TimeHalf] [Double] and [Price] all summary values:

ContractRef ContractName Basic TimeHalf Double Price
A012 Archive 9 1.5 1 35.00

I can get singular queries for each to work fine but I don't understand how the two can be linked via the common value that is ContractRef in both tables tblContacts/TblPriceContracts i.e. 'A012' in this case.

What I should really do is move the data in the second DB into the the first and it would be really easy

Again I appreciate the help

Regards,

Simon

 

[PHV]

Why not joining the linked tables ?
tblPriceContracts to tblcontracts on ContractID
tblPriceJobcards to tblJobcards on JobCardID
tblPricePricework to tblJobcards on JobCardID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SimonPGreen]

Thanks for ther advice PH

What I couldn't work out was the correct syntax for performing the joins in one statement.

Regards,

Simon

 

[PHV]

I can get singular queries for each to work fine
Another way is to simply join this 2 queries on ContractRef.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SimonPGreen]

Thanks PH,

This is gonna sound strange but I have already done that and it works fine. I am really interested in seeing if it is possible to do it with one SQL statement. For educational purposes really.

Regards,

Simon

 

[PHV]

Can you please post the 2 SQL ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244