Run a Join Statement That Excludes Duplicate Rows

Run a JOIN statement that excludes duplicate rows

I think you want something like this:

select db1.id
from (select db1.*, count(*) over (partition by db1.uid) as cnt
      from db1
     ) db1 left join
     db2
     on db2.rid = db1.rid
where cnt = 1 or cb2.rid is not null;

SQL query one to many relationship join without duplicates

I wouldn't mix data retrieval and data display, which is what I think you are asking about. Do you have some sort of column to indicate which payment should be displayed first? I'm thinking something like:

SELECT columnlist, 
rn = ROW_NUMBER() OVER (PARTITION BY sales.salesID ORDER BY payment.paymentID)
FROM sales JOIN payments ON sales.salesID=payments.salesID

Then, in your GUI, just display the values for the first 3 columns where RN = 1, and blank out the values where RN > 1.

Remove duplicate rows on multiple joins

I think if you move some of your logic into your join (changed to a LEFT JOIN), you can get the answer, moved more logic into another left join

select m.movementid, m.orderid, m.pickupdate, m.pickupnotes, 
b.bin_size, b.bin_type, 
l.address, l.suburb, l.postcode, l.state, 

IF(r.run_id IS NULL, 0, 1) as active_on_pick

from bb_movement m
inner join bb_bins b on b.bin_id = m.bin_id
inner join bb_location l on l.locationid = m.locationid

LEFT join bb_runsheet rs 
 ON rs.movement_id = m.movementid
    AND rs.run_action = 'Pick'

LEFT join bb_run r 
  ON r.run_id = rs.run_id
    AND (r.run_state = 'Active' or r.run_state='Ready')

where m.mvtstate = 'Active'
order by m.locationid, m.pickupdate

excluding duplicate fields in a join

There is no column exclusion syntax in SQL, there is only column inclusion syntax (via the * operator for all columns, or listing the column names explicitly).

Generate list of only columns you want

However, you could generate the SQL statement with its hundreds of column names, minus the few duplicate columns you do not want, using schema tables and some built-in functions of your database.

SELECT
    'SELECT sampledata.c1, sampledata.c2, ' || ARRAY_TO_STRING(ARRAY(
        SELECT 'demographics' || '.' || column_name
        FROM information_schema.columns
        WHERE table_name = 'demographics' 
        AND column_name NOT IN ('zip')
        UNION ALL
        SELECT 'community' || '.' || column_name
        FROM information_schema.columns
        WHERE table_name = 'community' 
        AND column_name NOT IN ('fips')
    ), ',') || ' FROM sampledata JOIN demographics USING (zip) JOIN community USING (fips)'
AS statement

This only prints out the statement, it does not execute it. Then you just copy the result and run it.

If you want to both generate and run the statement dynamically in one go, then you may read up on how to run dynamic SQL in the PostgreSQL documentation.

Prepend column names with table name

Alternately, this generates a select list of all the columns, including those with duplicate data, but then aliases them to include the table name of each column as well.

SELECT
    'SELECT ' || ARRAY_TO_STRING(ARRAY(
        SELECT table_name || '.' || column_name || ' AS ' || table_name || '_' || column_name
        FROM information_schema.columns
        WHERE table_name in ('sampledata', 'demographics', 'community')
    ), ',') || ' FROM sampledata JOIN demographics USING (zip) JOIN community USING (fips)'
AS statement

Again, this only generates the statement. If you want to both generate and run the statement dynamically, then you'll need to brush up on dynamic SQL execution for your database, otherwise just copy and run the result.

If you really want a dot separator in the column aliases, then you'll have to use double-quoted aliases such as SELECT table_name || '.' || column_name || ' AS "' || table_name || '.' || column_name || '"'. However, double-quoted aliases can cause extra complications (case-sensitivity, etc); so, I used the underscore character instead to separate the table name from the column name within the alias, and the aliases can then be treated like regular column names else-wise.

SQL Server 2005 - exclude rows with consecutive duplicate values in 1 field

The reason regular grouping doesn't help in this situation is because the grouping criteria needs to reference fields in 2 different records to determine if a group break should occur. Since SQL 2005 lags behind the newer versions, we don't have a lag function to look at the prior record's value. Instead, we need to do a self join to get access to the prior record. To do that, we need to create a temporary sequence field in a CTE using ROW_NUMBER(). Then use that generated sequence in the self join to look at the prior record. We end up with something like:

;WITH tmp AS (
  SELECT myDate,myStatus,ROW_NUMBER() OVER (ORDER BY myDate) as seq
  FROM myTable )
SELECT tmp.* FROM tmp LEFT JOIN tmp t2 ON t2.seq = tmp.seq-1
WHERE t2.seq is null OR t2.myStatus!=tmp.myStatus

So, even though the original data doesn't have a sequence column, we can generate it on the fly in order to be able to find the prior record (if any) for any given other record using the self join. Then we get the desired result of selecting only the records where the status has changed from the prior record.

How to exclude rows that don't join with another table?

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Full image of join

From aticle : http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

---

Related Topics