Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Markus430
Starting Member
4 Posts |
 Posted - 2011-01-20 : 07:00:04
|
Dear heroes of sql users in need of aid I'm a php developper who was always much more interested in PHP programming than in SQL. I only use SQL as a sort of extra in my php sites, a tool to acces my database. But as soon as I need to do anything involving more than one table in my database, I compensate my lack of SQL knowledge with PHP. Now I have an issue, which I can and have already solved using PHP, but though it's functional I simply don't like it that I am quite sure that it is relatively easy to do in sql and I don't know how to do it. In other words, though I hate sql, I'm eager to learn more about the language.Enough talk, let's code.Here's my situation. This is for a telecom website that offers Internet, Tv and phone services. A product is either a variant of telephone services, tv or internet. A subscription(abonnement) is a package including 2 or more products, but of each kind only one variant is included. Example:ProductInt---IntID = 1Name = Fast internetCosts = 20Speed = 20kbpsProductTel---TelID = 1Name = ProfitphoneCosts = 10(other irrelevant data)ProductTv---TvID = 1Name = Royal TVCosts = 15Abonnement---AboID = 1aName = InterTVDescription = Internet and TVProfit = 10AboProduct---AboID = 1IntID = 1TelID = 0TvID = 1In other words, package "AboID 1 contains Internet product with ID 1, no telephone product and TV product with ID 1."Now I am writing a script to list all subscriptions/packages/(abonnementen) on one page. Therefore, I want a SELECT query to return a set of rows with all subscriptions (from Abonnement table), their discription and name, and then the total cost for this subscription that is the sum of the prices of all individual products included in the subscription. So if in the AboProduct table the AboID = 1, IntID = 1, TelID = 1 and TvID = 0, it must return a total price of 30 (according to the example data above). However, extra complication is that subscriptions in which only 1 of the three product columns is > 0, are really not a package but a subsciption for a single product. So I do not want those to be listed too. Later when this sql query returns the data for the subscription and the total cost of all the products in that subscription, I will use another query to get the profit for the requested subscription and subtract that from the total costs to calculate the final price for the package. THat last thing is something I got covered, just telling you to possibly help you understand the situation.Abonnement [subscription]-----------AboID aNameDescriptionprofitAbonnementProduct----------AboIDIntIDTelIDTvIDProductInt----------IntIDintNaamcostsProductTel----------TelIDtelNaamcostsProductTV----------TvIDtvNaamcostsAny help is greatly appreceated, even if it doesn't lead me to the answer^^What I need is a (to you guys) simple SELECT query, not using variables and such. As basic as possible, not using CASE, WHEN and THEN (unless really required)..Thanks in advance! |
|
|
Markus430
Starting Member
4 Posts |
Posted - 2011-01-20 : 07:29:53
|
| Oh, and probably worth mentioning: I am using MySQL [a] |
 |
|
|
Markus430
Starting Member
4 Posts |
Posted - 2011-01-23 : 14:22:33
|
| Such gifted sql programmers, such a simple question, yet not a single response.. Pitty!Lucky I figured it out shortly after I posted this. No thanks for the help... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-01-23 : 14:30:07
|
Pity? This is a Microsoft SQL Server forum. Most of us have read and understand that.Since you are using MySQL, try your query over at www.dbforums.com
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Markus430
Starting Member
4 Posts |
Posted - 2011-01-23 : 15:43:52
|
| Ah, that explains, though at least someone could've pointed that out...My bad. |
|
|
|
|
|
|
|
|
SELECT and SUM data from 3 tables