tags:

views:

60

answers:

4

I have the following SQL Query:

SELECT porel.refcode, podetail.ponum, SUM(podetail.orderqty * podetail.unitcost) AS Total
FROM podetail
LEFT JOIN porel ON podetail.ponum = porel.ponum
WHERE porel.poline=podetail.poline AND porel.reftype = 'SPEX'
GROUP BY porel.refcode, podetail.ponum
ORDER BY porel.refcode, podetail.ponum

which works fine to bring me a list of ponums and their total values against refcodes.

I now want to add a third column vendor.name. The vendor table has a field vendorid which it shares with the podetail table.

There are multiple podetail records with the same ponum but these will also have matching vendorid's.

Could someone advise how I would add in the vendor.name field without returning extra rows?

+2  A: 

Add it into the GROUP BY. I have assumed that there will be one vendor for each podetail.ponum. By the way your current query is converting the left join to an inner join because of the porel.reftype = 'SPEX' in the Where clause.

SELECT   porel.refcode,
         podetail.ponum,
         SUM(podetail.orderqty * podetail.unitcost) AS Total,
         vendor.name
FROM     podetail
         INNER JOIN vendor ON podetail.vendorid = vendor.vendorid
         LEFT JOIN porel ON podetail.ponum = porel.ponum
WHERE    porel.poline=podetail.poline
AND      porel.reftype = 'SPEX'
GROUP BY porel.refcode,
         podetail.ponum,
         vendor.name
ORDER BY porel.refcode,
         podetail.ponum
Martin Smith
Thanks for your answer, Martin, I'm new to SQL so that's a big help. Your solution almost works but it is returning multiple identical ponums, all with the same Total but with different vendor names.I think my initial question might not have been specific enough so, to clarify; there are multiple podetail records with the same ponum (these are lines on a purchase order) but I need the total for the purchase order as a whole so I somehow need to work out orderqty * unitcost for each podetail record, then add these totals together for records with the same ponum.I hope that makes sense.
Rogue Wolf
@Rogue - But if there are multiple vendor names which one do you want to return in your query then?
Martin Smith
There is only one vendor name associated with each podetail record (via the vendorid field). Where there are multiple podetail records with the same ponum they all have the same vendorid.
Rogue Wolf
For example, there may be two podetail records with a ponum of '12345' (two lines on purchase order 12345) but they will both have identical vendorids.
Rogue Wolf
Before adding the vendor data into the mix, the query in my initial question only returned one line for each ponum. Adding the vendor table has resulted in the same ponum being returned multiple times.
Rogue Wolf
Did you include `vendor.name` in the `group by`? Most RDBMSs would insist on this but I think if you are using MySQL it might not.
Martin Smith
Thanks for persevering. I've just tried that but all it seems to do is remove the `ponum` column from the results, which I need. The lines are still being duplicated.It looks like the join to the `vendor` table is incorrect and returning multiple matches instead of just one.
Rogue Wolf
I've just checked and, yes, `vendor.name` is in the `group by`.
Rogue Wolf
I have seen another query which used a nested `SELECT` with `WHERE EXISTS` but am not familiar with the construction of such a query. Could that be a solution?
Rogue Wolf
@Rogue - I think you need to validate your assumption that "Where there are multiple podetail records with the same ponum they all have the same vendorid."
Martin Smith
@Rogue - I have the same suspicion as Martin Smith. Would you try something like `select ponum, count(distinct vendorid) as numvendors from podetail group by ponum` and see if numvendors is ever > 1?
Matt
@Martin - That's definitely the case. For example, the query is returning lines similar to the following:Refcode | poline | ponum | total | name10000 | 1 | 12345 | 20000 | ABC Systems10000 | 1 | 12345 | 20000 | DEF Ltdbut ponum 12345 has only one line with a value of 20000.
Rogue Wolf
@Matt - I ran the query you suggested and `numvendors` is exactly 1 for all 13000+ rows.
Rogue Wolf
@Rogue - Thanks, I'm convinced! And, just to make sure as a sanity check, vendorid is definitely, definitely unique in the vendor table? Sorry to ask these dumb questions - remote debugging is hard.
Matt
@Matt - I know what you mean, remote explaining isn't easy either :-) Yes, `vendorid` is **definitely** unique in the `vendor` table. I've uploaded a file to http://drop.io/sqloq4h which shows a better example of the output I'm getting. And, to be clear, ponum 12345 in that example was only showing up once until I added the vendor link.
Rogue Wolf
@Rogue - OK, now I see why this is frustrating. Because, by your DB structure, one PO must only have one vendorId must only have one name. Maybe I'll have some other ideas after lunch but this is the only other option I got for now - maybe PO is a string with trailing whitespace, and `group by` was inappropriately collapsing both `'12345'` and `'12345 '` before? In the following SO question, Martin discovered that `distinct` was doing that. (http://stackoverflow.com/questions/3493705/why-does-select-distinct-a-b-from-return-fewer-records-than-select-distinc)
Matt
@Matt - Yes, you're right, `ponum` and `vendorid` are set by a table further up the structure, namely `poheader`. `poheader` is the top level purchase order table and the `podetail` records are individual lines on the purchase orders. So, there will be only one `poheader` record with a `ponum` of 12345 but there may be multiple lines on the order and therefore multiple `podetail` records with a `ponum` of 12345. The `vendorid` is also set at the `poheader` level and inherited by all the `podetail` records associated with is so they will all have the same `vendorid`. Does that help?
Rogue Wolf
I don't think that there will be any whitespace in the `ponum` as the values are auto-generated by a frontend but I'll check when I get back to work on Monday, just to make sure. Thanks for sticking with me on this one, it's driving me crazy!
Rogue Wolf
@Matt - I've just checked and there doesn't seem to be any whitespace in the `ponum` values.
Rogue Wolf
+1, this answer was technically correct and, better, you've called out the conversion of the `LEFT JOIN` to an `INNER JOIN`. At best, this kind of confuses the intention of the query. At worst, it might be a mistake!
Matt
I agree with Matt on that. As soon as I have sufficient reputation I'll +1 this too. Thanks for your help Martin.
Rogue Wolf
A: 
SELECT   porel.refcode,
         podetail.poline,
         podetail.ponum,
         SUM(podetail.orderqty * podetail.unitcost) AS Total,
         vendor.name
FROM     podetail
         INNER JOIN vendor ON podetail.vendorid = vendor.vendorid
         LEFT JOIN porel ON podetail.ponum = porel.ponum
WHERE    porel.poline=podetail.poline
AND      porel.reftype = 'SPEX'
GROUP BY porel.refcode,
         podetail.ponum,
         vendor.name
ORDER BY porel.refcode,
         podetail.poline,
         podetail.ponum

I think, you are missing the need to include podetail.poline in your SELECT and GROUP BY.

shahkalpesh
Thanks for your reply. Adding `podetail.poline` as you suggest shows the same `poline` multiple times for each repeated `ponum`.The more I look at this the more it seems like the join to the `vendor` table is wrong and is therefore returning duplicate lines.
Rogue Wolf
I have seen another query which used a nested `SELECT` with `WHERE EXISTS` but am not familiar with the construction of such a query. Could that be a solution?
Rogue Wolf
A: 

Since you are aggregating podetails, simply aggregate vendor names:

SELECT porel.refcode, podetail.ponum
, MAX(vendor.name) as VendorName
, SUM(podetail.orderqty * podetail.unitcost) AS Total
FROM podetail
LEFT JOIN vendor on podetail.vendorid = vendor.vendorid
LEFT JOIN porel ON podetail.ponum = porel.ponum
WHERE porel.poline=podetail.poline AND porel.reftype = 'SPEX'
GROUP BY porel.refcode, podetail.ponum
ORDER BY porel.refcode, podetail.ponum

Or use MIN() if you prefer.

bbadour
@bbadour - Thanks for the suggestion. Aggregating the vendor names doesn't seem to work either. Running your version of the query returns one line for each `ponum` (which is what I want) but the vendor name and values are incorrect.I could be wrong, but aggregating the vendors seems to be going at it from the wrong direction; especially as without the aggregation the query is returning multiple different vendors for the same po, which is incorrect. I would imagine that trying to aggregate multiple different vendors will always have a chance of returning the wrong vendor.
Rogue Wolf
Surely there must be a way to connect the `vendor` table in such a way that it only returns the correct vendor. Maybe through a nested `SELECT` statement?
Rogue Wolf
You obviously have not fully described the foreign key reference from podetail to vendor. Once we know what uniquely identifies a podetail, porel and vendor, we will give you answers that return the results you want.
bbadour
+2  A: 

Heya, Rogue. I wanted to say a little more than fits in a comment, so here.

One more easy diagnostic off the top of my head is this:

SELECT
    porel.refcode,
    podetail.ponum,
    SUM(podetail.orderqty * podetail.unitcost) AS Total,
    podetail.vendorid
FROM
    podetail
    LEFT JOIN porel
    ON podetail.ponum = porel.ponum
WHERE
    porel.poline = podetail.poline 
    AND porel.reftype = 'SPEX' 
GROUP BY
    porel.refcode, 
    podetail.ponum,
    podetail.vendorid
ORDER BY
    refcode, 
    ponum

In here, we don't join to vendor at all, taking the table completely out of the equation. Instead, we'll just roll up podetail.vendorids instead.

Can you post the output of this query?

Matt
@Matt - Please, please, shoot me now! I ran the query as you suggested and was checking the results by running `SELECT * from vendor WHERE vendorid = *etc*` when I noticed that there *are* multiple vendors with the same vendorid! The vendor table has entries for multiple departments (companies) and different departments can have the same `vendornum` but for different vendors. What a schoolboy mistake :-s I've now added a `AND podetail.company = vendor.company` to the `WHERE` clause and it's working a treat. Thank you for all your help and sorry to have wasted your time
Rogue Wolf
@Rogue - Awesome. One more thing: You'd asked a couple times about a nested `SELECT ... WHERE EXISTS ... SELECT`. When people get more rows than you expect from a `JOIN`, they aren't sure why it's like that exactly, but they're tempted to throw some "fix it" syntax at it like `DISTINCT`, `TOP`, `MAX`, `WHERE IN ...` etc. Sometimes it's fine but it very often means there's something wrong underneath and you're masking over it. Just keep that in mind when you encounter this situation again. Anyway, enough preaching - good luck with the rest of your queries. :)
Matt
@Matt - Many thanks, I'll definitely bear that in mind for the future. I come from more of a programming background than a DB one so although I'm picking up the SQL syntax pretty quickly I'm still stumbling over some of the intricacies of database design; so your help and sanity checks have been invaluable.
Rogue Wolf
+1 Well done for getting to the bottom of it in the end!
Martin Smith