Complex queries joining 3 tables?
-
Hi Everyone
I have following tables..
Customer
[Ac Ref] Company Address Town County
XX001 XXXXX Some Add Some Town County
YY001 YYYYY Some Add Some Town County
ZZ001 ZZZZZ Some Add Some Town County
Customer Contact
[Ac Ref] Contact Name Position Telephone [Primary Contact]
XX001 Mr. A Head of IT 123456 Yes
XX001 Mr. B Technicial 654123 Yes
ZZ001 Mr. Z The Boss 987456 Yes
YY001 Mr. Y Technicial 654123 Yes
Invoice
[Invoice Id] Company Amount
1 XXXXX 1000
2 YYYYY 500
3 ZZZZZ 700
I need to run the query and find all invoices above 500 and return the results as follows. As you can see Company XXXXX has 2 contacts and both of them are primary but i need to display only the first or just one.
[Ac Ref] Company Address Town County Contact Name
XX001 XXXXX Some Add Some Town County Mr. A
ZZ001 ZZZZZ Some Add Some Town County Mr. Z
I managed to get above result but without contact name. I would like to have contact name as well.
Mits
-
Why not use a derived table to join to? IE... INNER JOIN (SELECT TOP 1 stuff from CompanyContact) CNTCTS and
Good Hunting!AJ Ahrens
webmaster@kritter.net -
The previous reply won't work as the TOP 1 statement will limit you to returning only one Company in the select statement, the options for returning multiple companies with one primary contact for each are
Having two seperate statements one insert and then an update, not great but it's simple. The other option is to join along these lines
SELECT *
FROM Company
INNER JOIN CompanyContact
ON Company.Company_ID = CompanyContact.Company_ID
INNER JOIN (SELECT MAX(Contact_ID) AS Contact_ID, Company_ID
FROM CompanyContact
GROUP BY Company_ID) TopContact
ON CompanyContact.Company_ID = TopContact.Company_ID
AND CompanyContact.Contact_ID = TopContact.Contact_ID
WHERE Primary = 1
It's not idea having to use MAX but thats life, be interested to see if anyone else knows of another way within a single statement to do this.
Hope that helps
-
I added Invoice.Amount so you could see it was getting what you were hoping for:
DECLARE @Customer TABLE( [Ac Ref] varchar(5),
Company varchar(10),
Address varchar(10),
Town varchar(10),
County varchar(10))
INSERT INTO @Customer VALUES( 'XX001', 'XXXXX', 'Some Add', 'Some Town', 'County')
INSERT INTO @Customer VALUES( 'YY001', 'YYYYY', 'Some Add', 'Some Town', 'County')
INSERT INTO @Customer VALUES( 'ZZ001', 'ZZZZZ', 'Some Add', 'Some Town', 'County')
DECLARE @CustomerContact TABLE( [Ac Ref] varchar(5),
[Contact Name] varchar(10),
Position varchar(10),
Telephone varchar(10),
[Primary Contact] varchar(10))
INSERT INTO @CustomerContact VALUES( 'XX001', 'Mr. A', 'Head of IT', '123456', 'Yes')
INSERT INTO @CustomerContact VALUES( 'XX001', 'Mr. B', 'Technicial', '654123', 'Yes')
INSERT INTO @CustomerContact VALUES( 'ZZ001', 'Mr. Z', 'The Boss', '987456', 'Yes')
INSERT INTO @CustomerContact VALUES( 'YY001', 'Mr. Y', 'Technicial', '654123', 'Yes')
DECLARE @Invoice TABLE( [Invoice Id] integer,
Company varchar(10),
Amount integer)
INSERT INTO @Invoice VALUES( 1, 'XXXXX', 1000)
INSERT INTO @Invoice VALUES( 2, 'YYYYY', 500)
INSERT INTO @Invoice VALUES( 3, 'ZZZZZ', 700)
SELECT Customer.[Ac Ref], Customer.Company, Customer.Address, Customer.Town, Customer.County,
CustomerContact.[Contact Name], Invoice.Amount
FROM @Customer Customer
INNER JOIN @Invoice Invoice ON( Customer.Company = Invoice.Company)
INNER JOIN( SELECT MAX( [Contact Name]) AS [Contact Name], [Primary Contact], [Ac Ref]
FROM @CustomerContact
GROUP BY [Primary Contact], [Ac Ref]) CustomerContact
ON( Customer.[Ac Ref] = CustomerContact.[Ac Ref])
WHERE Invoice.Amount > 500
AND CustomerContact.[Primary Contact] = 'Yes'
I wasn't born stupid - I had to study.
-
Dougiii,
If you add Company as part of the SELECT TOP 1 for returning data you would get one item per Company. I generally do not hand ALL the code to posters here instead I help lead them down the path to help resolve problems.
Of course you need to add a GROUP BY as well...Very good catch though...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
thanks guys for all your suggestion. I will try them out and see how it goes.
Mits
-
I'm fairly sure if you use the TOP 1 in your subquery it will only return 1 record which is fine if you're only looking for one company but if you're after more than one you'll need to use the subquery with MAX(Company_ID) Etc
-
Maybe this is not what you'd want to hear, but the solution to the problem doesn't lie with convoluted SQL (although I know - this is what we all have to deal with in real life)
What you have here is a design flaw - you have multiple 'primary', where one may argue that 'there can only be one' (as some Highlanders might put it
)The question then comes down to: if you have two contacts that are equal (ie both primary) it is wrong to ask just for one of them, since there is nothing to distinguish them with. For the question to be correctly posed, you should show both.
Sadly, this seldom is accepted from users or PHB's, they just want the stuff as they like it, not as it should be
/Kenneth
-
I'm with you on this one Kenneth, it's not Primary if there are two
-
This reminds me of a previous boss who all the projects we had were Priority 1
* Noel -
So how did you make him change his mind about that??
-
So how did you make him change his mind about that??
I never said I was able change his mind
* Noel -
If I were in that situation I think I would start to write one function per important project in rotation so that each project gets the same attention all the time
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply