DISTINCT WHERE?

[imperium2335]

Hi,

 

I have a problem where I want to check a for something in a row, but there are time where the row with the same reference will appear multiple times.

 

$branchCostPoundsNoVAT = mysql_query("SELECT sum(pos.amount)
	FROM pos, jobs, pourbaskets
	WHERE pos.currency = '£'
	AND pos.jobRef = jobs.id
	AND jobs.isInvoiced = 1
	AND pos.enqRef = pourbaskets.enquiryRef # This is whats causing the problem.
	AND pourbaskets.vat = '0.00'
	")or die(mysql_error()) ;

 

As there can be multiple occurances of pourbaskets.enquiryRef, so I would like something like:

 

AND DISTINCT pos.enqRef = pourbaskets.enquiryRef, but this results in an error.

[Muddy_Funster]

Learn how to use JOIN - it's a core part of MySQL syntax for anyone using more than 1 table.

[imperium2335]

How would that be used on this occasion?? 

[Muddy_Funster]

Without knowing your tables - I have no idea 

[imperium2335]

How do you use join?

[GalaxyTramp]

take a look here

 

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

 

[mikosiko]

Learn how to use JOIN - it's a core part of MySQL syntax for anyone using more than 1 table.

 

don't follow.... explain please what do you think is wrong with the OP code

[mikosiko]

Hi,

 

I have a problem where I want to check a for something in a row, but there are time where the row with the same reference will appear multiple times.

 

$branchCostPoundsNoVAT = mysql_query("SELECT sum(pos.amount)
	FROM pos, jobs, pourbaskets
	WHERE pos.currency = '£'
	AND pos.jobRef = jobs.id
	AND jobs.isInvoiced = 1
	AND pos.enqRef = pourbaskets.enquiryRef # This is whats causing the problem.
	AND pourbaskets.vat = '0.00'
	")or die(mysql_error()) ;

 

As there can be multiple occurances of pourbaskets.enquiryRef, so I would like something like:

 

AND DISTINCT pos.enqRef = pourbaskets.enquiryRef, but this results in an error.

 

post your tables structure to offer better advise... small examples of your data and the expected results will help too

[imperium2335]

Basically It's counting more than once when it sees a reference more than once, I'd like it to stop after getting just one.

 

Should I post my sql for creating the tables I'm working with?

 

Thanks so far!

[mikosiko]

yes

[Muddy_Funster]

Learn how to use JOIN - it's a core part of MySQL syntax for anyone using more than 1 table.

 

don't follow.... explain please what do you think is wrong with the OP code

 

Use of an INNER JOIN on the tables pos & pourbaskets would provide the 1->1 relationship that he wants between the fields.

SELECT sum(pos.amount)
FROM pos INNER JOIN pourbaskets ON (pos.enqRef = pourbaskets.enquieryRef), jobs
WHERE pos.currency = '£'
AND pos.jobRef = jobs.id
AND jobs.isInvoiced = 1
AND pourbaskets.vat = '0.00'[code]
For example would be a better way of doing it. 

[imperium2335]

Ok, for pos it is:

 

CREATE TABLE IF NOT EXISTS `pos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jobRef` int(11) NOT NULL,
  `enqRef` int(11) NOT NULL,
  `poPrefix` varchar(128) COLLATE utf8_bin NOT NULL,
  `partRefs` text COLLATE utf8_bin NOT NULL COMMENT 'part ids from pourbaskets that are in this PO.',
  `supplier` varchar(255) COLLATE utf8_bin NOT NULL,
  `branch` varchar(128) COLLATE utf8_bin NOT NULL,
  `paymentTerms` varchar(128) COLLATE utf8_bin NOT NULL,
  `ourPayMethod` varchar(255) COLLATE utf8_bin NOT NULL COMMENT 'How we paid for this.',
  `currency` varchar(3) COLLATE utf8_bin NOT NULL,
  `net` decimal(11,2) NOT NULL,
  `vat` decimal(11,2) NOT NULL,
  `amount` decimal(11,2) NOT NULL,
  `dateCreated` varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'Stored as a time stamp',
  `dateModified` varchar(32) COLLATE utf8_bin NOT NULL,
  `paid` int(1) NOT NULL DEFAULT '0' COMMENT 'Have we paid this already? Wont update if already paid!',
  `weReceivedInvoice` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Have we received the invoice for this PO?',
  `datePaid` datetime NOT NULL COMMENT 'the date we paid.',
  `canceled` int(1) NOT NULL DEFAULT '0',
  `dollarRate` decimal(11,2) NOT NULL,
  `euroRate` decimal(11,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=448 ;

 

for pourbaskets:

 

CREATE TABLE IF NOT EXISTS `pourbaskets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enquiryRef` int(11) NOT NULL,
  `counterPart` int(11) NOT NULL COMMENT 'The mirror part in partbaskets',
  `poRef` int(11) NOT NULL,
  `supplier` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `manufacturer` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `partNumber` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `newUsed` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `jobType` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `directIndirect` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `paymentTerms` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `vatCode` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `currency` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `net` decimal(11,2) NOT NULL,
  `vat` decimal(11,2) NOT NULL,
  `total` decimal(11,2) NOT NULL,
  `coreCreditBuy` decimal(11,2) NOT NULL COMMENT 'For service exchanges only.',
  `amountPaid` decimal(11,2) NOT NULL COMMENT 'amount we have paid for the item (most times equals the total)',
  `ourPaymentStatus` varchar(128) NOT NULL,
  `ourPaymentMethod` varchar(128) NOT NULL COMMENT 'How did we pay for this',
  `shippingSupplier` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `shippingType` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `shippingCurrency` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `shippingNet` decimal(11,2) NOT NULL,
  `shippingVat` decimal(11,2) NOT NULL,
  `shippingTotal` decimal(11,2) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2744 ;

 

and for jobs its:

 

CREATE TABLE IF NOT EXISTS `jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enquiryRef` int(11) NOT NULL,
  `trader` varchar(255) COLLATE utf8_bin NOT NULL,
  `assignedAdmin` varchar(255) COLLATE utf8_bin NOT NULL COMMENT 'Which admin is dealing with this.',
  `actionedBy` varchar(128) COLLATE utf8_bin NOT NULL,
  `creationDate` datetime NOT NULL,
  `status` varchar(255) COLLATE utf8_bin NOT NULL,
  `dateCancelled` datetime NOT NULL,
  `cancelledBy` varchar(255) COLLATE utf8_bin NOT NULL,
  `dateReactivated` datetime NOT NULL,
  `reactivatedBy` varchar(255) COLLATE utf8_bin NOT NULL,
  `dateCompleted` datetime NOT NULL,
  `completedBy` varchar(255) COLLATE utf8_bin NOT NULL,
  `amountPaid` decimal(11,2) NOT NULL,
  `paymentStatus` varchar(128) COLLATE utf8_bin NOT NULL,
  `customerPaymentMethod` varchar(128) COLLATE utf8_bin NOT NULL,
  `overrideReason` text COLLATE utf8_bin NOT NULL,
  `dateInvoiced` datetime NOT NULL COMMENT 'The date the invoice was raised (finance)',
  `proformaInvoice` varchar(128) COLLATE utf8_bin NOT NULL,
  `jobNotes` text COLLATE utf8_bin NOT NULL,
  `lastChange` datetime NOT NULL,
  `isChecked` tinyint(1) NOT NULL DEFAULT '0',
  `isInvoiced` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=497 ;

 

Thanks for your help so far, I know my base is probably messy so please any ideas or thoughs on how I can do it better please tell me!

[mikosiko]

@Muddy... is exactly what I thought will be your answer... unfortunately you are incorrect, the OP code is absolutely equivalent to the one that you are thinking, the only difference is that he is using IMPLICIT JOIN notation and you are using EXPLICIT JOIN notation.. The OP code is syntactically correct but functional incorrect, due probably to a wrong relation condition....just an small example:

 

SELECT table_a.field1, table_a.field2, table_b.field1, table_b.field2
   FROM table_a, table_b
   WHERE table_a.id = table_b.table_aid

 

is 100% equivalent to

 

SELECT table_a.field1, table_a.field2, table_b.field1, table_b.field2
   FROM table_a
      JOIN table_b ON table_a.id = table_b.table_aid   // and in this notation INNER is implicit too

 

in the case of the posted OP code (IMPLICIT JOIN notation):

SELECT sum(pos.amount)

FROM pos, jobs, pourbaskets

WHERE pos.currency = '£'

AND pos.jobRef = jobs.id

AND jobs.isInvoiced = 1

AND pos.enqRef = pourbaskets.enquiryRef # This is whats causing the problem.

AND pourbaskets.vat = '0.00'

 

it is equivalent to this (EXPLICIT JOIN notation)

SELECT sum(pos.amount)

  FROM pos

    JOIN jobs ON pos.jobRef = jobs.id AND jobs.isInvoiced = 1

    JOIN pos.enqRef = pourbaskets.enquiryRef and pourbaskets.vat = '0.00'

  WHERE pos.currency = '£'    // again INNER is implicit

 

which one is better?... that is hard to tell/evaluate, but using EXPLICIT notation is mostly recommended because RDBMS's queries optimizer convert internally implicit notation to explicit notation, therefore is less job for the optimizer.

 

but let focus in the real OP problem ok?

[mikosiko]

@imperium:  even when I'm not sure which is the result that you are looking for, try this code (dirty..non tested on my side)

 

SELECT pos.jobRef, sum(pos.amount)

  FROM pos

    JOIN jobs ON pos.jobRef = jobs.id AND jobs.isInvoiced = 1

  WHERE pos.currency = '£'

    AND EXISTS (SELECT 1 FROM pourbaskets WHERE pourbaskets.enquiryRef = pos.enqRef AND pourbaskets.vat = '0.00' LIMIT 1)

  GROUP BY pos.jobRef

 

it seems to answers this scenario:

"Sum the posted amount of each pos with pos.currency = '£'  that have invoiced jobs and have at least one record in the pourbaskets table with vat = 0"  (not sure if that result is what you are after)...

 

otherwise, clarify what do you want and post some table(s) data with the expected results ... sure somebody else will chime with the right solution.