加入两个表，其中一个表不是唯一的

Hey everyone. I need to join two tables according to those parameters:

1) I need only data that are both in the first and in the second table

2) Data in the second table can have more rows attached to one row of the first table, but I need those rows displayed only once.

I tried this

SELECT * FROM table AS p
            JOIN `table2` AS ps ON (p.id = ps.`id_table2`)
            ORDER BY p.id

but I am getting duplicates, as mentioned.

Thanks

Assuming you only care about the final output, use distinct:

SELECT distinct * FROM table AS p
         JOIN `table2` AS ps ON (p.id = ps.`id_table2`)
         ORDER BY p.id

To collapse a group of rows into a single row in the result set, use a GROUP BY clause. The GROUP BY clause should specify all columns which have identical values in each set to collapse into a single row. Your query should not SELECT any columns that are not part of the GROUP BY clause unless they are aggregate columns, allowing the RDBMS to know which of the values you want.

If you only wanted the columns from one table which have a corresponding row in the other, then you don't need the join or group at all.

SELECT * FROM table1 WHERE some_column IN (SELECT related_column FROM table2)

or use a WHERE EXISTS clause.

SELECT distinct * FROM table AS p
            JOIN `table2` AS ps ON (p.id = ps.`id_table2`)
            ORDER BY p.id

That should get rid of the duplicates? Is that what you are looking for?

Assuming MySQL:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.id =
        (
        SELECT  id
        FROM    table2 t2i
        WHERE   t2i.id_table2 = t1.id
        ORDER BY
                id
        LIMIT 1
        )

Change ORDER BY clause to define which of the multiple records do you want.

In SQL Server, Oracle and PostgreSQL (8.4+), use this syntax:

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.id) AS rn
        FROM    table1 t1
        JOIN    table2 t2
        ON      t2.id_table2 = t1.id
        ) q
WHERE   rn = 1

Again, change ORDER BY accordingly.