Select mysql with no duplicate

[bLuEmEzzy]

I added "DISTINCT" in my code. but seems the output is still the same, it have duplicate records. Thank u :D (note: I changed the field names and tables here)

SELECT DISTINCT b.field1, b.field2, b.field3, b.field4, c.field1, c.field2, c.field3 from tbl1 b inner join tbl2 c on b.logid = c.logid WHERE b.logid ='".$_SESSION['login_id']."'

[rubberman]

Show a sample of the output please.

[JOSheaIV]

Can you please show us your table schemas?

I will say this, I have seen duplicates occur when you don't inner join properly. For instance if you don't utilize all the primary keys it can return multiple results due to matches in one table causing the other to repeat.

Some ways to prevent this are, as I mentioned, change the columns you are joining on so you can ensure you are joining a unique record with a unique record. If you don't the record in say the left table may have multiple matches in the right table, and because of that, you'll pull back multiple records.

Another way is to use a Left or Right Join. This way you make sure one table brings back ALL its records, however, the other brings back columns only if a match is found (usually the first). These can be nice, as long as you write them correctly (for instance sometimes you might need COALESCEs on columns selected from the outer joined table in case it does NOT pull back a match)

[Taywin]

The question is vauge. Which field(s) do you want to be distinct? What do you mean by "duplicated"? Even though you may use JOIN to get rid of duplicated, it is still unclear what you really want to do in your question...

[bLuEmEzzy]

okay, I'll give you the details.

[bLuEmEzzy]

Please see attached file. I am confused right now with my sql statement. I changed the field names btw. Thank you!

[Nutster]

DISTINCT is doing its job properly. Distinct looks at all fields and eliminates ones that are completely duplicated. In your set, for example, field 1 equals 0001 in three records. Most of the fields are the same, but the last two fields are different, so DISTINCT considers them different records. So if DISTINCT is working properly, what is causing the extra records? I suspect the JOIN condition, but it looks reasonable.

I will need to see the two tables, tbl1 and tbl2, from your sample to determine what is happening in that join to give you those results. Which fields are your primary keys. I suspect you are not joining on a primary key and so the join merges all applicable records in one table with all applicable records in the other and this is the kind of result you get.

[jkon]

As Nutser noticed (and as you can see in the image of resulted rows you attached) they are distinct , (you have different field7 and field8 in rows with the same field1,field2,field3,field4,field5,field6). From the section “this should be the output” in the same attachment I guess that you want to take only THE FIRST of each row that has the same field1,field2,field3,field4,field5,field6. There are many ways to do it but I guess the easiest is using GROUP BY.
e.g. (just altering the SQL query from your first post)

SELECT DISTINCT b.field1, b.field2, b.field3, b.field4, c.field1, c.field2, c.field3 FROM tbl1 AS b INNER JOIN tbl2 AS c ON b.logid = c.logid WHERE b.logid = ? GROUP BY b.field1, b.field2, b.field3, b.field4 

There are some issues that I don't quite understand with your SQL query string but I guess that you know more about your data structure. e.g. You used DISTINCT only for that reason or there might be more rows with same b.field1, b.field2, b.field3, b.field4, c.field1, c.field2, c.field3 ? If it is the first one you don't have any reason to use DISTINCT at all.

[bLuEmEzzy]

Ok, in table 1 the primary key is (field1) then in table 2 there'ss a primary key I named it (itemid), another field is field 1(same in table 1).

[bLuEmEzzy]

Hi Guys Thank You for all your help. I got it already, the problem is on my innerjoin statement.

"c on b.field1 = c.field1"

SELECT DISTINCT b.field1, b.field2, b.field3, b.field4, c.field1, c.field2, c.field3 from tbl1 b inner join tbl2 c on b.field1 = c.field1 WHERE b.logid ='".$_SESSION['login_id']."'

[daniellemacey]

you need to specificy the table you're using