May 9, 2008 at 5:40 pm
My db structure is as follows:
All invoices have a user account associated through an id
All tickets have an invoice associated to the tickets
I am wanting to find all users, whose first created invoice, contains ticket(s) to a specific event.
Can someone tell me how to write this sort of query?
Thanks guys!
May 9, 2008 at 6:10 pm
JReed - help us help you...
Read this and see if you can give us some specifics as is decribed in the article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 7:47 pm
You need to show some effort here. This sounds like homework. Please make some attempt at the problem and we'll then help more.
May 10, 2008 at 9:03 am
This is actually for a client needing an export of unwanted customers. I wanted to see how theoretically this could be accomplished.
Also, How is you telling me the query better than you giving me ideas on building it myself? Maybe I'm missing something here.
May 10, 2008 at 9:16 am
I think I need to do the following logic:
SELECT *
FROM customers INNER JOIN
invoices INNER JOIN
tickets
WHERE top 1(invoice sorted ascending by invoice.date)
HAS tickets.game = 'specific opponent'
I'll reply with my solution in case this helps anyone else with a similar problem.
May 10, 2008 at 9:38 am
Ok so I know how to do this now.
I use a stored procedure to get all distinct customers while ordering by their associated invoice.date.
I store this information in a declaration table. I then inner join the declaration table with tickets through invoice.ticket_num and select all of those whose event = target event.
Sounds good in theory anyway 🙂
May 12, 2008 at 1:26 pm
Without having actual table structures, I'm guessing a bit here, but here's an idea:
;with FirstInvoice (CID, InvDate) as
(select customerid, min(invoicedate)
from dbo.invoices
group by customerid)
select *
from dbo.customers
inner join dbo.invoices
on customers.customerid = invoices.customerid
inner join FirstInvoice
on CID = customers.customerid
and InvDate = invoices.InvoiceDate
where ticketid = 1
The CTE (FirstInvoice) will find the first invoice for each customer ID. Then join that to the tables you want to select from, and limit it to the ID of the ticket you want. That will give you a list of customers and invoices, where it's the customer's first invoice, and it has that ticket on it.
As for what others were asking for, they weren't asking you to do something odd or whatever. They were asking for enough data to make sure this kind of query is even possible. For example, is there even an InvoiceDate field in your Invoices table? I can't tell. Instead, I have to hope that you look at what I wrote, and know enough to modify it for your needs.
If you're cool with that, wonderful. Many people asking questions here aren't up to that point, and need more exact help. That's all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2008 at 4:29 pm
Thanks G-Squared.
What I ended up doing was created a stored procedure that first select all distinct uids from the invoice table ordered by invoice number. Since invoice numbers increment they are already in order by date. I then took that information in a declaration table and inner joined with the tickets table to see which user's (first invoice) was to that event.
I have never heard of using "with"s.. I may read up on this for future reference.
Also, thanks for understanding my need to just discuss how to approach this problem.
Kind Regards,
May 13, 2008 at 8:46 am
The "with" is what's called a CTE (Common Table Expression). They're new in SQL 2005. If you look up CTE in Books Online, it will tell you how they work and what they're good for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply