|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stuck on SQL syntaxI have two simple tables: tClients lID // links to tAddress .lClientID FName LName tAddress lID lClientID // links to tClients.lID Address01 Address02 Address03 etc, etc Anyhow, I am trying to generate a results set that shows my all client details (SELECT * FROM tClients) BUT... with an additional column tacked onto the end that returns the number of addresses (in tAddress) for each client. Something like this: lID FName LName NumAddresses 01 Chris Hill 3 02 Alex Wells 0 etc I hope this makes sense. Currently, I am doing: SELECT * FROM tClients populating a datatable, and then having to loop through each row and pass the lID to a second stored proc to determine the number of addresses. In short, it's pretty long winded and v. inefficient. Any pointers/help would be massively appreciated hmmm, how about
With AddressCount ( Select lClientID, Count(*) NumAddresses From tAddress Group By lClientID ) Select c.IID, c.FName, c.LName, ac.NumAddresses From tClients c Left Join AddressCount ac on ac.lClientID = c.IID Or for SQL 2000 Select c.IID, c.FName, c.LName, ac.NumAddresses From tClients c Left Join ( Select lClientID, Count(*) NumAddresses From tAddress Group By lClientID ) ac on ac.lClientID = c.IID Colin. Hi Colin
Fantastic !!! Thanks a million; works like a d-ream :) I am using SQL Server 2000 Just one further query, if I may: Can I wrap this up to say, only perform this for a client with a particular ID. I.e. in English "Show me the clients ID, FName, LName and NumAddress WHERE the clientID is [blah] Cheers Chris Show quote "Colin Dawson" wrote: > hmmm, how about > > With AddressCount ( > Select > lClientID, > Count(*) NumAddresses > From tAddress > Group By lClientID > ) > Select > c.IID, > c.FName, > c.LName, > ac.NumAddresses > From tClients c > Left Join AddressCount ac on ac.lClientID = c.IID > > Or for SQL 2000 > > Select > c.IID, > c.FName, > c.LName, > ac.NumAddresses > From tClients c > Left Join ( > Select > lClientID, > Count(*) NumAddresses > From tAddress > Group By lClientID ) ac on ac.lClientID = c.IID > > Colin. > > > Chris (Ch***@discussions.microsoft.com) writes:
> Just one further query, if I may: It's as simple as:> > Can I wrap this up to say, only perform this for a client with a > particular ID. > > I.e. in English "Show me the clients ID, FName, LName and NumAddress WHERE > the clientID is [blah] >> Select WHERE lClientID = @clientid>> c.IID, >> c.FName, >> c.LName, >> ac.NumAddresses >> From tClients c >> Left Join ( >> Select >> lClientID, >> Count(*) NumAddresses >> From tAddress >> Group By lClientID ) ac on ac.lClientID = c.IID -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Show quote
"Erland Sommarskog" <esq***@sommarskog.se> wrote in message I'd just like to add one tiny detail, it's better to use alias names at all news:Xns979ACA2768DDYazorman@127.0.0.1... > Chris (Ch***@discussions.microsoft.com) writes: >> Just one further query, if I may: >> >> Can I wrap this up to say, only perform this for a client with a >> particular ID. >> >> I.e. in English "Show me the clients ID, FName, LName and NumAddress >> WHERE >> the clientID is [blah] > > It's as simple as: > >>> Select >>> c.IID, >>> c.FName, >>> c.LName, >>> ac.NumAddresses >>> From tClients c >>> Left Join ( >>> Select >>> lClientID, >>> Count(*) NumAddresses >>> From tAddress >>> Group By lClientID ) ac on ac.lClientID = c.IID > > WHERE lClientID = @clientid > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx times, unless there's no join operation. Keeping with this I'd add the same where clause as Erland, but with one modification to clarify what the code is actually doing, it will help to make maintenance easier as you won't need to guess which table the data column is suppose to come from. Here's the modified line (add it to the end of the SQL Statement)... Where c.IID = @clientid I'd prefer to use c.IID as opposed to lClientID, the reason for this is that there is a left outer join in operation here. There is a chance that the client has no addresses stored. In this case you will still need the result line, but with the NumAddresses returning a 0. If you place the where clause into the statement so that lCientID is used for the filter, if there are no addresses you'll get no result row at all! Regards Colin. Colin Dawson (newsgro***@cjdawson.com) writes:
> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message Wholly agree. I was Posting too quickly, too close to bedtime, I gues.>> WHERE lClientID = @clientid > > I'd just like to add one tiny detail, it's better to use alias names at > all times, unless there's no join operation. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx On Sun, 2 Apr 2006 10:45:02 -0700, Chris wrote:
Show quote >Hi all Hi Chris,> >I have two simple tables: > >tClients > lID // links to tAddress .lClientID > FName > LName > >tAddress > lID > lClientID // links to tClients.lID > Address01 > Address02 > Address03 > etc, etc > >Anyhow, I am trying to generate a results set that shows my all client >details (SELECT * FROM tClients) BUT... with an additional column tacked onto >the end that returns the number of addresses (in tAddress) for each client. >Something like this: > >lID FName LName NumAddresses >01 Chris Hill 3 >02 Alex Wells 0 >etc > >I hope this makes sense. The best solution is to redesign the Address table. Your current design is not normalised. Instead of having a large number of columns Address1, Address2, ..., you should have a column AddressNo and a column Address, like this: CREATE TABLE Addresses (ClientID int NOT NULL, AddressNo tinyint NOT NULL, Address varchar(200) NOT NULL, PRIMARY KEY (ClientID, AddressNo), FOREIGN KEY (ClientID) REFERENCES Clients(ClientID) ON UPDATE CASCADE ON DELETE CASCADE ) And then, the query would simply be SELECT ID, FirstName, LastName, (SELECT COUNT(*) FROM Addresses WHERE Addresses.ClientID = Clients.ClientID) AS NumAddresses FROM Clients With your current design, you're stuck with an ugly kludge like SELECT c.ID, c.FirstName, c.LastName, CASE WHEN a.Address1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN a.Address2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN a.Address3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN a.Address4 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN a.Address5 IS NOT NULL THEN 1 ELSE 0 END + ...... + CASE WHEN a.Address999 IS NOT NULL THEN 1 ELSE 0 END AS NumAddresses FROM Clients AS c LEFT OUTER JOIN Addresses AS a ON a.ClientID = c.ClientID -- Hugo Kornelis, SQL Server MVP
Show quote
"Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message Hi Hugo.news:5qk0329s4lhqs3hpmtricbdtcalnvsrj1d@4ax.com... > On Sun, 2 Apr 2006 10:45:02 -0700, Chris wrote: > >>Hi all >> >>I have two simple tables: >> >>tClients >> lID // links to tAddress .lClientID >> FName >> LName >> >>tAddress >> lID >> lClientID // links to tClients.lID >> Address01 >> Address02 >> Address03 >> etc, etc >> >>Anyhow, I am trying to generate a results set that shows my all client >>details (SELECT * FROM tClients) BUT... with an additional column tacked >>onto >>the end that returns the number of addresses (in tAddress) for each >>client. >>Something like this: >> >>lID FName LName NumAddresses >>01 Chris Hill 3 >>02 Alex Wells 0 >>etc >> >>I hope this makes sense. > > Hi Chris, > > The best solution is to redesign the Address table. Your current design > is not normalised. Instead of having a large number of columns Address1, > Address2, ..., you should have a column AddressNo and a column Address, > like this: > > CREATE TABLE Addresses > (ClientID int NOT NULL, > AddressNo tinyint NOT NULL, > Address varchar(200) NOT NULL, > PRIMARY KEY (ClientID, AddressNo), > FOREIGN KEY (ClientID) REFERENCES Clients(ClientID) > ON UPDATE CASCADE ON DELETE CASCADE > ) > > And then, the query would simply be > > SELECT ID, FirstName, LastName, > (SELECT COUNT(*) > FROM Addresses > WHERE Addresses.ClientID = Clients.ClientID) AS NumAddresses > FROM Clients > > > With your current design, you're stuck with an ugly kludge like > > SELECT c.ID, c.FirstName, c.LastName, > CASE WHEN a.Address1 IS NOT NULL THEN 1 ELSE 0 END > + CASE WHEN a.Address2 IS NOT NULL THEN 1 ELSE 0 END > + CASE WHEN a.Address3 IS NOT NULL THEN 1 ELSE 0 END > + CASE WHEN a.Address4 IS NOT NULL THEN 1 ELSE 0 END > + CASE WHEN a.Address5 IS NOT NULL THEN 1 ELSE 0 END > + ...... > + CASE WHEN a.Address999 IS NOT NULL THEN 1 ELSE 0 END AS > NumAddresses > FROM Clients AS c > LEFT OUTER JOIN Addresses AS a > ON a.ClientID = c.ClientID > > > -- > Hugo Kornelis, SQL Server MVP I disagree with this. Whilst I agree that the OP's table design does not lend itself to the easiest interpretation; I do think that you've misinterpreded the table design. (very easily done as there's no DDL included) Chris can you confirm this? If my understanding is correct the Address01, Address02, Address03 columns are supposed to be columns which will contain eash line of the address, not the entire address. I've made this assumption based on the way that the original question was asked. Regards Colin. Hi Guys
First off, thanks for all the help. Really Appreciated :) As for my tAddress table, Colin is right : Address01 Address02 Address03 Correspond to a line in one address, not a complete address. It would of been better if I'd described the table like this: tAddress lID cAddressLine1 cAddressLine2 cAddressLine3 cTown cPostcode Sorry for any confusion. Is my table structure a poor one? REgards Chris Show quote "Colin Dawson" wrote: > > "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message > news:5qk0329s4lhqs3hpmtricbdtcalnvsrj1d@4ax.com... > > On Sun, 2 Apr 2006 10:45:02 -0700, Chris wrote: > > > >>Hi all > >> > >>I have two simple tables: > >> > >>tClients > >> lID // links to tAddress .lClientID > >> FName > >> LName > >> > >>tAddress > >> lID > >> lClientID // links to tClients.lID > >> Address01 > >> Address02 > >> Address03 > >> etc, etc > >> > >>Anyhow, I am trying to generate a results set that shows my all client > >>details (SELECT * FROM tClients) BUT... with an additional column tacked > >>onto > >>the end that returns the number of addresses (in tAddress) for each > >>client. > >>Something like this: > >> > >>lID FName LName NumAddresses > >>01 Chris Hill 3 > >>02 Alex Wells 0 > >>etc > >> > >>I hope this makes sense. > > > > Hi Chris, > > > > The best solution is to redesign the Address table. Your current design > > is not normalised. Instead of having a large number of columns Address1, > > Address2, ..., you should have a column AddressNo and a column Address, > > like this: > > > > CREATE TABLE Addresses > > (ClientID int NOT NULL, > > AddressNo tinyint NOT NULL, > > Address varchar(200) NOT NULL, > > PRIMARY KEY (ClientID, AddressNo), > > FOREIGN KEY (ClientID) REFERENCES Clients(ClientID) > > ON UPDATE CASCADE ON DELETE CASCADE > > ) > > > > And then, the query would simply be > > > > SELECT ID, FirstName, LastName, > > (SELECT COUNT(*) > > FROM Addresses > > WHERE Addresses.ClientID = Clients.ClientID) AS NumAddresses > > FROM Clients > > > > > > With your current design, you're stuck with an ugly kludge like > > > > SELECT c.ID, c.FirstName, c.LastName, > > CASE WHEN a.Address1 IS NOT NULL THEN 1 ELSE 0 END > > + CASE WHEN a.Address2 IS NOT NULL THEN 1 ELSE 0 END > > + CASE WHEN a.Address3 IS NOT NULL THEN 1 ELSE 0 END > > + CASE WHEN a.Address4 IS NOT NULL THEN 1 ELSE 0 END > > + CASE WHEN a.Address5 IS NOT NULL THEN 1 ELSE 0 END > > + ...... > > + CASE WHEN a.Address999 IS NOT NULL THEN 1 ELSE 0 END AS > > NumAddresses > > FROM Clients AS c > > LEFT OUTER JOIN Addresses AS a > > ON a.ClientID = c.ClientID > > > > > > -- > > Hugo Kornelis, SQL Server MVP > > Hi Hugo. > > I disagree with this. Whilst I agree that the OP's table design does not > lend itself to the easiest interpretation; I do think that you've > misinterpreded the table design. (very easily done as there's no DDL > included) > > Chris can you confirm this? > > If my understanding is correct the Address01, Address02, Address03 columns > are supposed to be columns which will contain eash line of the address, not > the entire address. > > I've made this assumption based on the way that the original question was > asked. > > Regards > > Colin. > > > <snip>
Show quote > It would of been better if I'd described the table like this: </snip>> > tAddress > lID > cAddressLine1 > cAddressLine2 > cAddressLine3 > cTown > cPostcode > > Sorry for any confusion. > > Is my table structure a poor one? > > REgards > > Chris > It's a good logical structure. The problem was the confusion that it caused. There is one thing missing from the table stucture, but this really depends on how you are using the table. There's nothing to identify what type of address your are referring to. For example, I have a contact manager database this contains a virtual business card (a vCard if you like) Here's the table structure... Contact --> ContactAddress --> Address --> AddressType The AddressType table is a lookup list that can be placed in a combo/listbox. This helps to identify the type of address, i.e. if it's a home/work or other address. This table has an AddressTypeID column and a description. The Address table contains that actual address, just like your tAddress table. I'll use AddressID for the primary key. This table will contain a distinct list of addresses. These addresses can be used for any purpose. The Contact table contains the contact - name, date of birth and a few other details, and a ContactID. The ContactAddress table is the key to my structure. This table looks a little like this.... Create Table ContactAddress( ContactID, AddressID, AddressTypeID Constraint PK_ContactAddress Primary Key Clustered( ContactID, AddressID ) ) The idea is that I can tie many addresses to a single contact. If I have several contacts at the same address there is still only one address row. The Address type ID can be used in mailshots, to set the preference for the destination of letters. i.e. Type to send business stuff to the work address if not, then go to the home address, and so on. Regards Colin. <snip>
It's a good logical structure. The problem was the confusion that it caused. There is one thing missing from the table stucture, but this really depends on how you are using the table. There's nothing to identify what type of address your are referring to. </snip> Yeah, sorry for not explaining it too well first time round, but I am glad that it passes muster as a table structure : ) I see yr point about id'ing the type of address - I never thought of that. I too, use this as part of a contacts db and get round the address for mail merges by having another boolean column to indicate which address, from many, is the preferred mailing address. Regards Chris Show quote "Colin Dawson" wrote: > <snip> > > It would of been better if I'd described the table like this: > > > > tAddress > > lID > > cAddressLine1 > > cAddressLine2 > > cAddressLine3 > > cTown > > cPostcode > > > > Sorry for any confusion. > > > > Is my table structure a poor one? > > > > REgards > > > > Chris > > > </snip> > > It's a good logical structure. The problem was the confusion that it > caused. There is one thing missing from the table stucture, but this > really depends on how you are using the table. There's nothing to identify > what type of address your are referring to. For example, I have a contact > manager database this contains a virtual business card (a vCard if you > like) Here's the table structure... > > Contact --> ContactAddress --> Address > --> AddressType > > The AddressType table is a lookup list that can be placed in a > combo/listbox. This helps to identify the type of address, i.e. if it's a > home/work or other address. This table has an AddressTypeID column and a > description. > > The Address table contains that actual address, just like your tAddress > table. I'll use AddressID for the primary key. This table will contain a > distinct list of addresses. These addresses can be used for any purpose. > The Contact table contains the contact - name, date of birth and a few other > details, and a ContactID. > > The ContactAddress table is the key to my structure. This table looks a > little like this.... > > Create Table ContactAddress( > ContactID, > AddressID, > AddressTypeID > > Constraint PK_ContactAddress Primary Key Clustered( ContactID, AddressID ) > ) > > The idea is that I can tie many addresses to a single contact. If I have > several contacts at the same address there is still only one address row. > The Address type ID can be used in mailshots, to set the preference for the > destination of letters. > > i.e. Type to send business stuff to the work address if not, then go to the > home address, and so on. > > Regards > > Colin. > > >  |
|||||||||||||||||||||||
Other interesting topics