could you help me write a query : select where 2 rows have same vals in 2 cols

steamPunk

I'm trying to write a query that just lists all instances where two rows have the same order number and the same product code and list by order number

something like :

SELECT * FROM comm_li WHERE (???? two rows have identical vals in the ID_Commande_Site && AR_REF cols ???? ) ORDER BY ID_Commande_Site ASC

I just can't figure this out and would be very grateful for any help

thanks

bradgrafelman

Do you have a PRIMARY or UNIQUE key on any column(s) in this comm_li table?

EDIT: Nevermind... here's an untested query that may or may not work:

SELECT t1.*
FROM comm_li t1
INNER JOIN comm_li t2
    USING (ID_Commande_Site, AR_REF)

steamPunk

Hi - thanks for your reply
Unfortunately that doesn't return any results
I don't know the 'USING' part and I can't find a reference to it in the manual - what I want to do is something like this :

SELECT t1.* FROM comm_li AS t1 INNER JOIN comm_li AS t2 ON t2.ID_Commande_Site=t1.ID_Commande_Site WHERE t2.AR_REF=t1.AR_REF

but that doesn't seem to work either

the table in question looks like this

CREATE TABLE IF NOT EXISTS `comm_li` (
  `DO_Piece` varchar(9) NOT NULL,
  `ID_Commande_Site` int(11) NOT NULL,
  `AR_REF` varchar(19) NOT NULL,
  `AR_PrixHT` decimal(6,2) NOT NULL,
  `QUANTITE` decimal(6,2) NOT NULL,
  KEY `AR_REF` (`AR_REF`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `comm_li` (`DO_Piece`, `ID_Commande_Site`, `AR_REF`, `AR_PrixHT`, `QUANTITE`) VALUES
('298378', 258377, '02PACA_PANI', '3.50', '1.00');

does anyone have any other ideas ?

bradgrafelman

steamPunk;11013985 wrote:
Hi - thanks for your reply
Unfortunately that doesn't return any results

Really? The query is indeed wrong, but it's wrong in that it should have shown you all rows i nthe table, namely because I forgot an important piece.. (more on that below)

steamPunk;11013985 wrote:
I don't know the 'USING' part and I can't find a reference to it in the manual

Really? You must not have looked in the MySQL manual then, because it's definitely in there:

MySQL Manual wrote:
The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:
a LEFT JOIN b USING (c1,c2,c3)

steamPunk;11013985 wrote:
the table in question looks like this

Well I can see one problem already with my method - you've got no PRIMARY or UNIQUE keys. In other words, you're saying there's no value that will uniquely identify a given row in the table. Is that true?

If that wasn't true, then you could do something like:

SELECT t1.*
FROM comm_li t1
INNER JOIN comm_li t2
    ON t1.ID_Commande_Site = t2.ID_Commande_Site
       AND t1.AR_REF = t2.AR_REF
       AND t1.[I]PRI_OR_UNIQUE_COL[/I] != t2.[I]PRI_OR_UNIQUE_COL[/I]
GROUP BY t1.[I]PRI_OR_UNIQUE_COL[/I]

NogDog

Maybe dump the table into a temporary table that includes an auto-increment primary key, then run a query against that temp table so that you can do a != check against that primary key? (Assuming you wouldn't prefer to just add such a field to the original table?)

steamPunk

bradgrafelman - thanks for the link to USING - I swear I looked in the manual but couldn't find and 'using' is a word that crops up all over the place anyway
yes there are no unique keys because one order can have several lines and one product can be in several orders.

NogDog - I'll try adding in the auto-increment col and see if bradgrafelman's last query will work with that

thanks to both