showing duplicate records (mysql sql)

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

showing duplicate records (mysql sql)

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-10-17 : 03:23:57

select m.model_id, IF( i.icon_id =6, 1, 0 ) AS hvp, IF( i.icon_id =5, 1, 0 ) AS ws, IF( qr.quantity_range_price_model_id IS NULL , 1, 0 ) AS free FROM model m left JOIN mp_classe_model mp ON m.model_id = mp.model_id LEFT JOIN mp_icon_model i ON m.model_id = i.model_id LEFT JOIN quantity_range_price_model qr ON m.model_id = qr.model_id AND qr.shipping_type_id =1 WHERE mp.classe_id =2 AND STATUS =1 ORDER BY free DESC , ws DESC , hvp DESC

this is showing duplicate records returned
why? how can i only show distinct model_id?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 03:55:43

Did you actually know this is Microsoft SQL Server Forum?
I have seen you posting lot of Mysql questions here
Try posting at www.mysql.com or www.dbforums.com

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 03:59:23

After 589 message I expect so. Probably respects this forum and was hoping for an answer, hence the Title hint.

My view would be that I'm not aware of any of the regulars here using MySQL ... they are using SQL Server for all sorts of good reasons ... and therefore getting a credible answer here is unlikely.

esthera: Can you not use SELECT DISTINCT ?

Kristen

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-10-17 : 04:06:39

hi sorry yes - i was hoping i could get an answer here as it's an sql question and not registered by those other forums... i guess i'll go register

distinct doesn't owrk as the iconid returned is what's making it show double.

i though a left join shows all records from the first table and only those from the second where it's = - so why would that show double?

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 04:34:54

"i though a left join shows all records from the first table and only those from the second where it's = - so why would that show double?"

Correct, but maybe there are multiple matching rows in the second table? - that would give you "double" for any give JOINed column

Kristen

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-10-17 : 06:23:13

so what is the way around this in a query -- if I only want to join to the first or max id

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 07:04:28

"so what is the way around this in a query"

Assuming you just want the child record from second table with the MAX() value on some TieBreakColumn then something like this:


SELECT ...
FROM Table1 AS T1
    JOIN Table2 AS T2A
         ON T2A.MyPK = T1.MyPK
    JOIN
    (
        SELECT T2B.MyPK, [MaxID] = MAX(T2B.TieBreakColumn)
        FROM Table2 AS T2B
        GROUP BY T2B.MyPK
    ) AS T2B
         ON T2B.MyPK = T2A.MyPK
        AND T2B.MaxID = T2A.TieBreakColumn

Dunno how much of that works in MySQL though! but if I haven't made any typos that would work in SQL Server

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 07:32:56

quote:
Originally posted by Kristen

"so what is the way around this in a query"

Assuming you just want the child record from second table with the MAX() value on some TieBreakColumn then something like this:

SELECT ...
FROM Table1 AS T1
JOIN Table2 AS T2A
ON T2A.MyPK = T1.MyPK
JOIN
(
SELECT T2B.MyPK, ~~[MaxID] =~~ MAX(T2B.TieBreakColumn) as [MaxID]
FROM Table2 AS T2B
GROUP BY T2B.MyPK
) AS T2B
ON T2B.MyPK = T2A.MyPK
AND T2B.MaxID = T2A.TieBreakColumn

Dunno how much of that works in MySQL though! but if I haven't made any typos that would work in SQL Server

Kristen

As I told you some days back, use AS ALIAS name which will work in all RDBMS

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 08:02:53

I was stuck in SQL Server World! Thanks for that.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-10-18 : 05:52:51

Just an observation please don't get offended to my comment. Majority of the Indians that I have observed in some of the forums are too showy while some are just humble to themselves.

Want Philippines to become 1st World COuntry? Go for World War 3...

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 06:26:54

Same the world over, isn't it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 06:27:54

quote:
Originally posted by jonasalbert20

Just an observation please don't get offended to my comment. Majority of the Indians that I have observed in some of the forums are too showy while some are just humble to themselves.

Want Philippines to become 1st World COuntry? Go for World War 3...

I am interested to know which category I am in

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 06:29:06

quote:
Originally posted by Kristen

Same the world over, isn't it?

Yes it is

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 06:36:05

"I am interested to know which category I am in"

Hang on ... I'll just toss a coin ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 06:40:58

quote:
Originally posted by Kristen

"I am interested to know which category I am in"

Hang on ... I'll just toss a coin ...

Waiting for the result

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 06:42:48

its landed on its edge ...

... I reckon that only happens for MVPs

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-10-18 : 07:01:37

I noticed that the coin is blank on both sides. no head or tail.

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 07:08:36

quote:
Originally posted by jonasalbert20

I noticed that the coin is blank on both sides. no head or tail.

Want Philippines to become 1st World COuntry? Go for World War 3...

Always use proper coins

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 07:23:37

ROTFL! "All formatting should be on the front side of the coin, not the back site ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources