Joining Tables Help

I cannot figure this one out.

I have a SQL Server Database.

I have 4 tables I want data from.

Customer

Invoice

Shipper

Customer Address.

Customer is keyed by CustomerID.

CustomerID is a foreign key in Customer Address, and Invoice

Invoice is keyed by InvoiceID

InvoiceID is a foreign key in Shipper.

Shipper has a field, ShipTo.

Each Customer can have multiple addresses. The key for Customer Address is a composite of CustomerID and AddressNo.

Now...

If I had one table that had CustomerID and ShipTo in the same table, I could just link CustomerID to CustomerID and ShipTo to AddressNo.

However, I don't have a single table with that information.

I have:

[Customer] -> CustomerID -> [Invoice] -> InvoiceID -> [Shipper] -> ShipTo/AddressNo -> [Customer Address]

However... I also have [Invoice] -> CustomerID -> [CustomerAddress], with the intent that I can look up a single address per invoice by a combonation of it's CustomerID and the ShipTo address of the relevant Shipping record.

Elsewhere, I'd make a new table to combine those keys, but I can't do anything with this database, as it's the backend of a system.

Please help?I'm not clear what for you need to use the table Customer_Addresses, you have the shipped_address info in the Shipper table.

The first three tables I would link like this (using left outer join):

Customer.CustomerID ->Invoice.CustomerID
Invoice.InvoiceID -> Shipper.InvoiceID|||The shipped_address info you refer to is an integer.

Moreover, it's part of a composite key. You need to know which CustomerID (integer) and which addressID (also integer) to look up the correct record on the Customer_Address table, so you can get street address, state, zip, names, etc.

The main problem I have is that Shipper only has one part of the composite key (the address ID), and the other tables have the other half (CustomerID).|||Why not to post an example of those fields?
:wave:|||[Customer]
CustomerID: 1 FName: John LName: Doe
[Invoice]
InvoiceID: 7 CustomerID: 1 Amount: 32.93 InvDate: 1/1/2002
[Shipper]
InvoiceID: 7 ShipToNum: 8
[Customer Address]
CustomerID: 1 AddressNum: 8 Street: 502 Mack Lane City: Tulsa State: OK Zip: 53234

(fake data)

Each of these (fake) records all relates to the same customer/invoice/address.|||I don't know what CR version you're using but you can create a command .|||I'm using Crystal Reports 8. How can I create this command to join these tables appropriately?

Joining an aggregate of a table to itself

I am trying to figure out how to do a simple aggregate of a table and then join it to itself to get the remaining fields. I have a few tables that list information on daily entries that includes job_id, location_id, desc, date, beginDate, endDate, manager. I want to list every entry in the table, but for each job_id and location_id combination, I'd like to get the very first entry for beginDate and the very latest entry for endDate for the group and then list all the remaining information within each record.

My first attempt to do this is:

Select M.job_id, M.location_id, M.desc, M.date, Y.beginDate, Y.endDate, M.manager
from myTable M
inner join

(select job_id, location_id, min(beginDate), max(endDate)

from myTable

group by job_id, location_id) as Y

on M.job_id = Y.job_id, M.location_id = Y.location_id

This seems inefficient and I'm not sure it's the best way to get what I want. Any suggestions?

Thanks!!!

I'm not completely sure what to aim at; it will help if you will give some sample data and the desired output. You might be looking for something like one of these:

declare @.myTable table
( rid integer,
job_id integer,
location_id integer,
[desc] varchar(10),
date datetime,
manager varchar(10)
)

insert into @.myTable
select 1, 1, 1, 'First Job', '4/20/7', 'Flintstone' union all
select 2, 1, 1, 'First Job', '4/21/7', 'Slate' union all
select 3, 1, 1, 'First Job', '4/22/7', 'Flintstone' union all
select 4, 2, 2, 'Future Job', '4/20/7', 'Jetson' union all
select 5, 2, 2, 'Future Job', '4/21/7', 'Spacely' union all
select 6, 2, 2, 'Future Job', '4/22/7', 'Spacely'

Select M.job_id,
M.location_id,
M.[desc],
M.date,
Y.beginDate,
Y.endDate,
M.manager
from @.myTable M
inner join
( select job_id,
location_id,
min(Date) as beginDate,
max(Date) as endDate
from @.myTable
group by job_id, location_id
) Y
on M.job_id = Y.job_id
and M.location_id = Y.location_id

/*
job_id location_id desc date beginDate endDate manager
- -- - -
1 1 First Job 2007-04-20 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Flintstone
1 1 First Job 2007-04-21 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Slate
1 1 First Job 2007-04-22 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Flintstone
2 2 Future Job 2007-04-20 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Jetson
2 2 Future Job 2007-04-21 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
2 2 Future Job 2007-04-22 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
*/

declare @.myTable2 table
( rid integer,
job_id integer,
location_id integer,
[desc] varchar(10),
date datetime,
manager varchar(10)
)

insert into @.myTable2
select 1, 1, 1, 'First Job', '4/20/7', 'Slate' union all
select 2, 1, 1, 'First Job', '4/21/7', 'Slate' union all
select 3, 1, 1, 'First Job', '4/22/7', 'Slate' union all
select 4, 2, 2, 'Future Job', '4/20/7', 'Spacely' union all
select 5, 2, 2, 'Future Job', '4/21/7', 'Spacely' union all
select 6, 2, 2, 'Future Job', '4/22/7', 'Spacely'
select job_id,
location_id,
[desc],
min(date) as beginDate,
max(date) as endDate,
Manager
from @.myTable2
group by job_id,
location_id,
[desc],
Manager
order by job_id,
location_id

/*
job_id location_id desc beginDate endDate manager
- -- - -
1 1 First Job 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Slate
2 2 Future Job 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
*/

Joining 2 tables via 3rd table

I've been squeezing my noggin trying to figure this problem out with little
to show for it though I admit my SQL ability is dismal.
I have 3 tables as follows (greatly simplified here):
tblProperties
int ID
1001
1002
1003
tblOwners
int ID
2001
2002
2003
tblPropertyOwners
int ID int PropertyID (FK) int OwnerID (FK)
3001 1001 2001
3001 1001 2002
3002 1001 2003
3003 1003 2002
The tblPropertyOwners table indicates who owns which properties. Thus,
using the above sample data, property 1001 has 3 owners (2001, 2002, 2003),
property 1002 has zero owners (since no entries in the link table) and
property 1003 has one owner (2002).
The client wants a single record returned for each property that shows
property data plus data for up to two owners (if any) for that property. I'
m
loading the dataset into an ASP.Net 2.0 GridView and exporting it to an Exce
l
spreadsheet (which works great, now if only I could get the data correct!).
The output for the above sample data should be as follows:
PropertyID Owner1 Owner2
1001 2001 2003 <-- min & max ownerIDs for first
property though any 2 will do
1002 null null <-- this property has no
owners
1003 2002 <-- this property has one own
er
I've tried many different solutions but my SQL ability is basic and I'm
tired of wasting time on this so I'm looking for others' expertise. Any
suggestions or thoughts? I really appreciate your time.
Troy
.Net DeveloperSomething like:
select
p.ID
, min (po.OwnerID) Owner1
, max (po.OwnerID) Owner2
from
tblProperties p
left join
tblPropertyOwners po on po.PropertyID = p.ID
group by
p.ID
It will give you both columns - even if there isonly one owner.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> wrote in message
news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com...
I've been squeezing my noggin trying to figure this problem out with little
to show for it though I admit my SQL ability is dismal.
I have 3 tables as follows (greatly simplified here):
tblProperties
int ID
1001
1002
1003
tblOwners
int ID
2001
2002
2003
tblPropertyOwners
int ID int PropertyID (FK) int OwnerID (FK)
3001 1001 2001
3001 1001 2002
3002 1001 2003
3003 1003 2002
The tblPropertyOwners table indicates who owns which properties. Thus,
using the above sample data, property 1001 has 3 owners (2001, 2002, 2003),
property 1002 has zero owners (since no entries in the link table) and
property 1003 has one owner (2002).
The client wants a single record returned for each property that shows
property data plus data for up to two owners (if any) for that property.
I'm
loading the dataset into an ASP.Net 2.0 GridView and exporting it to an
Excel
spreadsheet (which works great, now if only I could get the data correct!).
The output for the above sample data should be as follows:
PropertyID Owner1 Owner2
1001 2001 2003 <-- min & max ownerIDs for first
property though any 2 will do
1002 null null <-- this property has no
owners
1003 2002 <-- this property has one
owner
I've tried many different solutions but my SQL ability is basic and I'm
tired of wasting time on this so I'm looking for others' expertise. Any
suggestions or thoughts? I really appreciate your time.
Troy
.Net Developer|||select A.iID as IDProperty,
min(B.iIDOwner) as Owner1,
case when min(B.iIDOwner) <> max(B.iIDOwner) then max(B.iIDOwner)
else NULL end as Owner2
from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
ON A.iID = B.iIDProperty
group by A.iID
Martin C K Poon
Senior Analyst Programmer
====================================
"Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> bl
news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com g...
> I've been squeezing my noggin trying to figure this problem out with
little
> to show for it though I admit my SQL ability is dismal.
> I have 3 tables as follows (greatly simplified here):
> tblProperties
> int ID
> 1001
> 1002
> 1003
> tblOwners
> int ID
> 2001
> 2002
> 2003
> tblPropertyOwners
> int ID int PropertyID (FK) int OwnerID (FK)
> 3001 1001 2001
> 3001 1001 2002
> 3002 1001 2003
> 3003 1003 2002
> The tblPropertyOwners table indicates who owns which properties. Thus,
> using the above sample data, property 1001 has 3 owners (2001, 2002,
2003),
> property 1002 has zero owners (since no entries in the link table) and
> property 1003 has one owner (2002).
> The client wants a single record returned for each property that shows
> property data plus data for up to two owners (if any) for that property.
I'm
> loading the dataset into an ASP.Net 2.0 GridView and exporting it to an
Excel
> spreadsheet (which works great, now if only I could get the data
correct!).
> The output for the above sample data should be as follows:
> PropertyID Owner1 Owner2
> 1001 2001 2003 <-- min & max ownerIDs for
first
> property though any 2 will do
> 1002 null null <-- this property has no
> owners
> 1003 2002 <-- this property has one
owner
> I've tried many different solutions but my SQL ability is basic and I'm
> tired of wasting time on this so I'm looking for others' expertise. Any
> suggestions or thoughts? I really appreciate your time.
> Troy
> .Net Developer
>|||That was my initial attempt but, alas, it doesn't work. If there is only on
e
owner for a property it will be listed as both Owner1 and Owner2, plus it
doesn't show properties with no owners. But thanks for the suggestion and
your time.
"Tom Moreau" wrote:

> Something like:
> select
> p.ID
> , min (po.OwnerID) Owner1
> , max (po.OwnerID) Owner2
> from
> tblProperties p
> left join
> tblPropertyOwners po on po.PropertyID = p.ID
> group by
> p.ID
> It will give you both columns - even if there isonly one owner.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> wrote in message
> news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com...
> I've been squeezing my noggin trying to figure this problem out with littl
e
> to show for it though I admit my SQL ability is dismal.
> I have 3 tables as follows (greatly simplified here):
> tblProperties
> int ID
> 1001
> 1002
> 1003
> tblOwners
> int ID
> 2001
> 2002
> 2003
> tblPropertyOwners
> int ID int PropertyID (FK) int OwnerID (FK)
> 3001 1001 2001
> 3001 1001 2002
> 3002 1001 2003
> 3003 1003 2002
> The tblPropertyOwners table indicates who owns which properties. Thus,
> using the above sample data, property 1001 has 3 owners (2001, 2002, 2003)
,
> property 1002 has zero owners (since no entries in the link table) and
> property 1003 has one owner (2002).
> The client wants a single record returned for each property that shows
> property data plus data for up to two owners (if any) for that property.
> I'm
> loading the dataset into an ASP.Net 2.0 GridView and exporting it to an
> Excel
> spreadsheet (which works great, now if only I could get the data correct!)
.
> The output for the above sample data should be as follows:
> PropertyID Owner1 Owner2
> 1001 2001 2003 <-- min & max ownerIDs for fir
st
> property though any 2 will do
> 1002 null null <-- this property has no
> owners
> 1003 2002 <-- this property has one
> owner
> I've tried many different solutions but my SQL ability is basic and I'm
> tired of wasting time on this so I'm looking for others' expertise. Any
> suggestions or thoughts? I really appreciate your time.
> Troy
> ..Net Developer
>|||YOU ARE THE SQL MASTER! Thanks so much for your time and effort. You've
saved us many hours of headbanging (we've wasted enough time on this problem
as is). I love programming but SQL refuses to stick to my synapses (give me
assembler over SQL any day).
Muchas gracias, Amigo.
Troy
.Net Developer, powered by a 16K TRS-80 Model III with cassette drive
"Martin C K Poon" wrote:

> select A.iID as IDProperty,
> min(B.iIDOwner) as Owner1,
> case when min(B.iIDOwner) <> max(B.iIDOwner) then max(B.iIDOwner)
> else NULL end as Owner2
> from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
> ON A.iID = B.iIDProperty
> group by A.iID
>
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> |b?l￥ó
> news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com ¤¤???g...
> little
> 2003),
> I'm
> Excel
> correct!).
> first
> owner
>
>|||Martin,
Expanding on my initial (simplified) request, what if tblOwners contains
other fields (e.g. varchar Name) that need to be displayed alongside the
owner IDs? Thus, the output for my sample code should be:
PropertyID O1ID O1Name O2ID O2Name
1001 2001 Name2001 2003 Name2003
1002 null null null null
1003 2002 Name2002 null null
Given tblOwners:
ID Name
2001 Name2001
2002 Name2002
2003 Name2003
The datset I am trying to gather actually contains many fields from
tblProperties (ID, name, address, phone, etc) and tblOwners (ID, name,
address, etc), thus joining to tblOwners complicates things a bit (for my
Jethro brain). Again, thanks for your time and effort. Take care.
Troy
"Martin C K Poon" wrote:

> select A.iID as IDProperty,
> min(B.iIDOwner) as Owner1,
> case when min(B.iIDOwner) <> max(B.iIDOwner) then max(B.iIDOwner)
> else NULL end as Owner2
> from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
> ON A.iID = B.iIDProperty
> group by A.iID
>
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> |b?l￥ó
> news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com ¤¤???g...
> little
> 2003),
> I'm
> Excel
> correct!).
> first
> owner
>
>|||
Untested but try this
select p.IDProperty,
t1.ID as O1ID,
t1.Name as O1Name,
t2.ID as O2ID,
t2.Name as O2Name
from (
select A.iID as IDProperty,
min(B.iIDOwner) as Owner1,
case when min(B.iIDOwner) <> max(B.iIDOwner) then
max(B.iIDOwner) else NULL end as Owner2
from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
ON A.iID = B.iIDProperty
group by A.iID ) p
LEFT OUTER JOIN tblOwners t1 ON t1.ID=p.Owner1
LEFT OUTER JOIN tblOwners t2 ON t2.ID=p.Owner2|||You, too, are an SQL Master.
Your SQL code using our naming convention:
select p.PID,
t1.ID as O1ID,
t1.Name as O1Name,
t2.ID as O2ID,
t2.Name as O2Name
from (
select A.ID as PID,
min(B.OwnerID) as Owner1,
case
when min(B.OwnerID) <> max(B.OwnerID) then
max(B.OwnerID)
else NULL
end as Owner2
from tblProperties A
LEFT OUTER JOIN tblPropertyOwners B ON A.ID = B.PropertyID
group by A.ID) p
LEFT OUTER JOIN tblOwners t1 ON t1.ID = p.Owner1
LEFT OUTER JOIN tblOwners t2 ON t2.ID = p.Owner2
ORDER BY P.PID
The output using our data (names changed):
PID O1ID O1Name O2ID O2Name
1 1 ABC Community 4 Riveria Communities
2 2 Chuck Norris 5 Dresden Associates
3 1 ABC Community NULL NULL
4 1 ABC Community NULL NULL
Using your code & Martin's, I'm confidant (faux confidence?) I can complete
the final requirement of joining to a fourth table (tblContacts via
tblOwnerContacts table) so that the first (minimum ID) contact (if any) is
listed for each of the two owners, thus the header row will be PID, O1ID,
O1Name, C1ID, C1Name, O2ID, O2Name, C2ID, C2Name where C1 is the first
contact for O1 (could be null) and C2 is the first contact for O2 (could be
null). I suppose I should have listed the full requirement from the outset
but I was afraid no one would tackle such a beast so I started small. :)
Anyway, thanks for your sharing your wisdom and time. Many thanks.
Troy
"markc600@.hotmail.com" wrote:

>
> Untested but try this
>
> select p.IDProperty,
> t1.ID as O1ID,
> t1.Name as O1Name,
> t2.ID as O2ID,
> t2.Name as O2Name
> from (
> select A.iID as IDProperty,
> min(B.iIDOwner) as Owner1,
> case when min(B.iIDOwner) <> max(B.iIDOwner) then
> max(B.iIDOwner) else NULL end as Owner2
> from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
> ON A.iID = B.iIDProperty
> group by A.iID ) p
> LEFT OUTER JOIN tblOwners t1 ON t1.ID=p.Owner1
> LEFT OUTER JOIN tblOwners t2 ON t2.ID=p.Owner2
>|||My initial foray is close but not quite ready for prime time. The sample
record output below correctly shows contact for Owner1 but, unfortunately, i
t
shows Contact2 (Dan Linkletter) as being a contact for NULL Owner2. I'll
tweak it until I have it down pat. (Names changed in output.)
PID O1ID O1Name C1ID
C1Name
12 8 Richard Bushido Co. 5 Richard Placido NULL NULL 6 Dan Linkletter
My (slightly flawed) SQL is as follows:
select p.PID,
t1.ID as O1ID,
t1.Name as O1Name,
c1.ID as C1ID,
c1.FirstName + ' ' + c1.LastName as C1Name,
t2.ID as O2ID,
t2.Name as O2Name,
c2.ID as C2ID,
c2.FirstName + ' ' + c2.LastName as C2Name
from (
select A.ID as PID,
min(B.OwnerID) as Owner1,
min(C.ContactID) as Contact1,
case
when min(B.OwnerID) <> max(B.OwnerID) then
max(B.OwnerID)
else
NULL
end as Owner2,
case
when min(C.ContactID) <> max(C.ContactID) then
max(C.ContactID)
else
NULL
end as Contact2
from tblProperties A
LEFT OUTER JOIN tblPropertyOwners B ON A.ID = B.PropertyID
LEFT OUTER JOIN tblOwnerContacts C ON B.OwnerID = C.OwnerID
group by A.ID) p
LEFT OUTER JOIN tblOwners t1 ON t1.ID = p.Owner1
LEFT OUTER JOIN tblOwners t2 ON t2.ID = p.Owner2
LEFT OUTER JOIN tblContacts c1 ON c1.ID = p.Contact1
LEFT OUTER JOIN tblContacts c2 ON c2.ID = p.Contact2
ORDER BY P.PID
"Troy Dot Net" wrote:
> You, too, are an SQL Master.
> Your SQL code using our naming convention:
> select p.PID,
> t1.ID as O1ID,
> t1.Name as O1Name,
> t2.ID as O2ID,
> t2.Name as O2Name
> from (
> select A.ID as PID,
> min(B.OwnerID) as Owner1,
> case
> when min(B.OwnerID) <> max(B.OwnerID) then
> max(B.OwnerID)
> else NULL
> end as Owner2
> from tblProperties A
> LEFT OUTER JOIN tblPropertyOwners B ON A.ID = B.PropertyID
> group by A.ID) p
> LEFT OUTER JOIN tblOwners t1 ON t1.ID = p.Owner1
> LEFT OUTER JOIN tblOwners t2 ON t2.ID = p.Owner2
> ORDER BY P.PID
> The output using our data (names changed):
> PID O1ID O1Name O2ID O2Name
> 1 1 ABC Community 4 Riveria Communities
> 2 2 Chuck Norris 5 Dresden Associates
> 3 1 ABC Community NULL NULL
> 4 1 ABC Community NULL NULL
> Using your code & Martin's, I'm confidant (faux confidence?) I can complet
e
> the final requirement of joining to a fourth table (tblContacts via
> tblOwnerContacts table) so that the first (minimum ID) contact (if any) is
> listed for each of the two owners, thus the header row will be PID, O1ID,
> O1Name, C1ID, C1Name, O2ID, O2Name, C2ID, C2Name where C1 is the first
> contact for O1 (could be null) and C2 is the first contact for O2 (could b
e
> null). I suppose I should have listed the full requirement from the outse
t
> but I was afraid no one would tackle such a beast so I started small. :)
> Anyway, thanks for your sharing your wisdom and time. Many thanks.
> Troy
> "markc600@.hotmail.com" wrote:
>|||FYI: I've decided to use brute force to generate the dataset I need (yes
Virginia, I was unable to figure out a good solution using a single query).
I'll use Mark's code to grab property, owner1 and owner2 data and stuff that
into a temp table. Then I'll grab contact1 data and stuff it into the same
temp table, then do the same for Contact2. Finally, I'll return the temp
table. The performance will likely suffer but I need a working solution NOW
.
Troy
"Troy Dot Net" wrote:
> You, too, are an SQL Master.
> Your SQL code using our naming convention:
> select p.PID,
> t1.ID as O1ID,
> t1.Name as O1Name,
> t2.ID as O2ID,
> t2.Name as O2Name
> from (
> select A.ID as PID,
> min(B.OwnerID) as Owner1,
> case
> when min(B.OwnerID) <> max(B.OwnerID) then
> max(B.OwnerID)
> else NULL
> end as Owner2
> from tblProperties A
> LEFT OUTER JOIN tblPropertyOwners B ON A.ID = B.PropertyID
> group by A.ID) p
> LEFT OUTER JOIN tblOwners t1 ON t1.ID = p.Owner1
> LEFT OUTER JOIN tblOwners t2 ON t2.ID = p.Owner2
> ORDER BY P.PID
> The output using our data (names changed):
> PID O1ID O1Name O2ID O2Name
> 1 1 ABC Community 4 Riveria Communities
> 2 2 Chuck Norris 5 Dresden Associates
> 3 1 ABC Community NULL NULL
> 4 1 ABC Community NULL NULL
> Using your code & Martin's, I'm confidant (faux confidence?) I can complet
e
> the final requirement of joining to a fourth table (tblContacts via
> tblOwnerContacts table) so that the first (minimum ID) contact (if any) is
> listed for each of the two owners, thus the header row will be PID, O1ID,
> O1Name, C1ID, C1Name, O2ID, O2Name, C2ID, C2Name where C1 is the first
> contact for O1 (could be null) and C2 is the first contact for O2 (could b
e
> null). I suppose I should have listed the full requirement from the outse
t
> but I was afraid no one would tackle such a beast so I started small. :)
> Anyway, thanks for your sharing your wisdom and time. Many thanks.
> Troy
> "markc600@.hotmail.com" wrote:
>

Join Table Key to Multiple Table Names

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

ThxWouldn't it be a lot less trouble to have one table for stock, one table for jobs, and one table to show job-stock-usage? That way you could have a practical infinity of jobs and stocks with only three tables.

This is a fundamental database design process called normalization. It is the key to maintaining your sanity as your projects grow!

-PatP|||I totally agree...I don't like this design whatsoever but its all I have to work with because I didn't design it and it's the way things are being done right now.|||I believe that particular schema is taken directly from Chapter 6, page 142 of the ever popular and best-selling book, "WORLD'S WORST DATABASE DESIGNS".

You are going to have to use dynamic SQL to solve this. Essentially, you will construct your SQL statement as a string concatenating the value of jobID in as the table name, and then execute the string.

Developers like that ought to be shot.|||thx for the reply blindman. I was leaning that way but I'm fairly new to using ms sql. I'm currently reading up on dynamic sql and seeing how it works. I need to generate a view out of this somehow.

Thx again|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)|||-----
lol|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)I don't know that it has ever been officially published, it is more of a "work in progress" kind of thing. Every time they think they're ready to publish, somebody runs in yelling "You've got to see this one" and they go right back to editing!

-PatP|||73% of the database designs in that book, WORLD'S WORST DATABASE DESIGNS, were written by php programmers, who design tables like that all the time

18% of them were written by people who've spent too much time at dbdebumph.com and have drunk the koolaid -- not a null in sight!!

9% were written by oracle developers who thought that storing a whole nested table inside a field was a neat idea and supports their concept of object-oriented encapsulation|||Man, you NAILED it with #3!!! I'll buy you a round (12 pack or a bottle of your choice) whenever you're in town!!! Though I see so much of #1 that it's not even funny any more...I wish images from Unreal Tornament were real sometimes...|||thank you, thank you, i'll be here all week, try the veal and don't forget to tip your waitress

:cool:|||no sooner do i say it, and another example of a design in the first category pops up: this thread (http://forums.devshed.com/t199186/s.html)

happens all the time|||This is SO ironic...I just declined an offer from a real estate marketing company which excercised a similar design "strategy" ... They even asked me at the interview how I would resolve this situation... Of course my answer was to FIRE the designer of the current database first ;)

Join syntax help

I am trying to figure out some sql syntax, and I could use some help. This
is my first atempt at joins, so bear with me.

I have a table (A) which looks like the following

ID Data Source
--------
1 abcdef 100
2 abcdef 100
3 abcdef 200
4 abcdef 200
5 abcdef 200

A second table (B) which looks like the following

Key ID
--------
Key1 1
Key1 2
Key1 3
Key1 4
Key2 1
Key2 2

Essentially, A is a table of items, and B is a table of where those items
have been used (Key1 is like an invoice which has items 1-4 on it, Key2 is a
second invoice with 1 and 2.) Source, in table A, is like the item
supplier.

I would like to get a list of every invoice (Key) that has used a part (ID)
from a particular Source.

So, for example, I would like to query for source 100 and get back (Key1,
Key2) or query for source 200 and get back only Key1.

To this end, I tried

"SELECT DISTINCT B.Key FROM B JOIN A ON (B.ID = A.ID) WHERE (A.Source =
100)"

But I got an empty recordset, so something is amiss.

Any help is greatly appreciated.

Thanks,

-dPlease post some code that will actually reproduce the problem. Your query
worked for me and here's the proof:

/* (My assumptions about your tables and keys) */
CREATE TABLE A (id INTEGER PRIMARY KEY, data VARCHAR(10), source INTEGER NOT
NULL)
CREATE TABLE B ([key] VARCHAR(10), id INTEGER NOT NULL REFERENCES A (id),
PRIMARY KEY ([key],id))

INSERT INTO A (id, data, source)
SELECT 1, 'abcdef', 100 UNION ALL
SELECT 2, 'abcdef', 100 UNION ALL
SELECT 3, 'abcdef', 200 UNION ALL
SELECT 4, 'abcdef', 200 UNION ALL
SELECT 5, 'abcdef', 200

INSERT INTO B ([key],id)
SELECT 'Key1', 1 UNION ALL
SELECT 'Key1', 2 UNION ALL
SELECT 'Key1', 3 UNION ALL
SELECT 'Key1', 4 UNION ALL
SELECT 'Key2', 1 UNION ALL
SELECT 'Key2', 2

SELECT DISTINCT B.[key]
FROM B JOIN A
ON B.id = A.id
WHERE A.source = 100

Result:

key
----
Key1
Key2

(2 row(s) affected)

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Vd6dnQPnvP1QhX7cRVn-2g@.giganews.com...
> Please post some code that will actually reproduce the problem. Your query
> worked for me and here's the proof:

Goodness, sorry to waste your time, and thanks for the help nonetheless. It
seems I was querying for nonexistent data.

-d