Hi Shawn I hope this gives you what you need:
SELECT member.Member_Initials, company.Company_Name, subscription.Subscription_Expiry_Date FROM subscription INNER JOIN member ON member.Member_URN = subscription.Member_URN INNER JOIN company ON company.Company_URN = subscription.Company_URN >From member: ABS BV CCS DNV GL KR LR NK RINA RS CRS IRS Many thanks -- Aeon McNulty On 23/7/04 7:35 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > I will be more than happy to help you build a case-specific crosstab > report. I have almost enough information. Could you post a 3 > column-query that will actually produce from your data the information > you would like to have reformatted? > > Column one needs to be your column names, column two needs to be your > row headers, and the 3rd column needs to have the data you want to see > merged into your cells (I won't need the data only the query). It should > look something like: > > SELECT a.name, b.name, c.data > FROM c > INNER JOIN a > on a.id = c.a_id > INNER JOIN b > on b.id = c.b_id > WHERE ..... (any condition will do) > > I will also need a list of the potential column headers (this time I do > need the data): > > SELECT DISTINCT name > FROM a > > With that I will have enough information to "pivot" your query into a > crosstab report. Hopefully you will be able to spot the pattern and be > able to adjust it to fit your other situations as well. > > Yours, > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > Aeon McNulty <[EMAIL PROTECTED]> wrote on 07/23/2004 02:12:37 > PM: > >> Hi Shawn >> >> Yes, that's pretty much it. I'm impressed that you managed to work > that out >> from my previous email which was a bit thin on detail! >> >>> Good news: For a specific case (when you know the number of columns) > the >>> query you seek to write follows a simple and predictable pattern and > is >>> not hard to write at all. >> >> The number of columns are variable but they don't vary often so I can > do a >> quick query first to give me the number and then use that number to > specify >> the number of columns in the second query. Does that make sense? >> >> Please see my other post. Does that give you enough information? >> >> Many thanks >> >> On 23/7/04 6:36 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: >> >>> >>> If I understand you correctly Basically you want to convert this > query >>> output: >>> >>> +-------+-------+-------+ >>> |A.value|B.value|C.value| >>> +-------+-------+-------+ >>> | a1 | b1 | c1 | >>> | a1 | b1 | c2 | >>> | a2 | b1 | c3 | >>> | a2 | b1 | c4 | >>> | a1 | b2 | c5 | >>> | a1 | b2 | c6 | >>> | a2 | b2 | c7 | >>> | a2 | b2 | c8 | >>> | a1 | b3 | c9 | >>> | ... | ... | ... | >>> | a(j) | b(k) | c(n) | >>> +-------+-------+-------+ >>> into something like: >>> +-------+----------+----------+----------+-----------+ >>> | | a1 | a2 | ... | a(j) | >>> +-------+----------+----------+----------+-----------+ >>> | b1 | f(c1,c2) | f(c3,c4) | ... | ... | >>> | b2 | f(c5,c6) | f(c7,c8) | ... | ... | >>> | b3 |f(c9,...) | ... | ... | ... | >>> | ... | ... | ... | ... | ... | >>> | b(k) | f(c1,c2) | f(c2,c3) | ... |f(...,C(n))| >>> +-------+----------+----------+----------+-----------+ >>> >>> Where f() represents one of the aggregate functions: SUM, AVG, STD, > MIN, >>> MAX, etc. >>> >>> Bad news: MySQL does not have an SQL-only solution for the general > case >>> (when you do not know the number of columns) >>> Good news: it is usually quite simple to write a script to handle > the >>> general case. >>> Good news: For a specific case (when you know the number of columns) > the >>> query you seek to write follows a simple and predictable pattern and > is >>> not hard to write at all. >>> >>> If you would like help in writing a specific pivot table (cross-tab) >>> query. Please post your tables' structures (I prefer the output of > SHOW >>> CREATE TABLE xxx) and tell us how you want your cross-tab report > setup >>> (column headers from where, row headers from where, and a formula to > use >>> for your cells) >>> >>> Yours, >>> Shawn Green >>> Database Administrator >>> Unimin Corporation - Spruce Pine >>> >>> >>> Aeon McNulty <[EMAIL PROTECTED]> wrote on 07/23/2004 > 12:30:21 >>> PM: >>> >>>> Hi, I hope someone on the list can help me. >>>> >>>> Is there an easy and straightforward way way of displaying the data >>> from >>>> three tables in a spreadsheet like format using MySQL 4.0? >>>> >>>> Table A has the column headings >>>> Table B has the row headings >>>> Table C is a line items file than cross relates the two >>>> >>>> Many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
