June 5, 2008 at 9:48 am
Hello all i have been trying to get a junction table up and running and cannot seem to get columns from 3 other tables into it.
Table1: is the table i want to insert my data into.
Columns: clmA, clmB, clmC, clmD, clmEPK
Columns I want to insert into Table1
Table2:
Columns: clmA, clmB (clmA=KEY)
Table3:
Columns: clmA, clmD (Compsote Key)
Table4:
Columns: clmB, clmC, clmDate (clmC = Key)
I have Tried this a couple different ways but let me write out what i think i need to do.
INSERT INTO Table1
SELECT a.clmA, a.clmB, b.clmD c.clmC
FROM Table2 a
INNERJOIN Table3 b
ON a.clmA = b.clmA
INNERJOIN (SELECT pp.clmC, pp.clmB, pp.clmDate
FROM Table4 pp
INNERJOIN (SELECT clmC, clmB, MAX(clmDate) AS clmDate
FROM Table4
GROUP BY clmB) pp1
ON pp.clmB = pp1.clmB) c
ON a.clmB = c.clmB
Attatched is the diagram of the actual tables.
ANY help at all would be appreciated.
Thanks in advance!
June 5, 2008 at 10:01 am
AM I right in saying you want to insert into Pack_J_Price?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 5, 2008 at 10:04 am
How about this:
INSERT INTO Pack_J_Price
(PackageID,
PartID,
PartsPricingID,
JobID)
SELECT
[PP].[PackageID],
[PP].[PartID],
[PPr].[PartsPricingID],
[PJ].JobID
FROM PartPackage [PP]
INNER JOIN PackageJob [PJ]
ON [PJ].PackageID = [PP].PackageID
INNER JOIN PartsPricing [PPr]
ON [PP].PartID = [PPR].PartID
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 5, 2008 at 10:24 am
Wow thanks for such a fast response. how would i get the MAX Date of a partID to return?
June 5, 2008 at 10:30 am
what i am trying to do is return the price of a part from a package that is on a job that is the most recent Date thought i should clarify.
June 6, 2008 at 7:31 am
Use the MAX function and then use a group by Close on the other fields.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply