CODEWITHSUNDEEP
mysqlsqldistinct
unbalanced
unbalanced

Reputation: 1202

SQL distinct in MySQL

I have two tables which don't have any relation or foreign key,I want to combine two columns in my query.

 select   DISTINCT a.OdemeTuru, b.MAdi from odemeturu as a, 
 musteriler as b order by b.MID,a.OID

I can get values from both but it's mixed and distinct is not working.

The result is as follows:

'Nakit', 'Ali' 'Veresiye', 'Ali' 'Kredi Kartı', 'Ali' 'Özel', 'Ali'
'Nakit', 'Ahmet' 'Veresiye', 'Ahmet' 'Kredi Kartı', 'Ahmet' 'Özel',
'Ahmet' 'Nakit', 'Cemil' 'Veresiye', 'Cemil' 'Kredi Kartı', 'Cemil'
'Özel', 'Cemil' 'Nakit', 'Furkan' 'Veresiye', 'Furkan' 'Kredi Kartı',
'Furkan' 'Özel', 'Furkan'

However, I want the following

'Nakit', 'Ali'
'Özel', 'Ahmet'
'Veresiye', 'Cemil'
'Kredi Kartı', 'Furkan'

Where is mistake in my query? (table' row counts are same)

Edit: sorry I forgot some thing. there isnt exist a.tarih

Upvotes: 0

Views: 145

Answers (2)

GarethD
GarethD

Reputation: 69749

The only way I can think of to cross apply 2 tables of 4 rows to get 4 rows and not 16 is to join on a rownumber:

SELECT  a.OdemeTuru, b.Madi
FROM    (   SELECT  @a:= @a + 1 AS RowNumber, OID, OdemeTuru
            FROM    odemeturu,
                    (SELECT @a:= 0) AS a
            ORDER BY OID
        ) AS a
        INNER JOIN
        (   SELECT  @b:= @b + 1 AS RowNumber, MID, Madi
            FROM    musteriler,
                    (SELECT @b:= 0) AS b
            ORDER BY MID
        ) AS b
            ON a.RowNumber = b.RowNumber

In odemeturu the rows are ordered by OID, and the rows in musteriler ordered by MID. The first row of odemeturu is joined with the first row of musteriler, the second with the second and so on. This is fairly arbitrary but seems to match your criteria.

You can change how the subqueries are ordered to change the results.

Example on SQL Fiddle

Upvotes: 2

Rahul
Rahul

Reputation: 77856

If you can join both your tables somehow then you can probably do like this:

select   DISTINCT a.OdemeTuru, b.MAdi from odemeturu a leftjoin musteriler b 
on a.OID = b.MID order by b.MID,a.OID a.tarih

EDIT:

As stated, without any relationship between the tables and MID/OID are also different; the only thing possible would be get all different values using UNION like

select DISTINCT OdemeTuru from odemeturu order by a.OID,a.tarih 
UNION
select distinct MAdi from musteriler order by b.MID

Upvotes: 0

Related Questions