-1

For example, I have a Cargo Shipment table and within that table there are two columns; Origin and Destination. How would I make it so that any results I get from my select statement will be excluded if the values in these columns are the same?

To be more specific, I could have a row where both Origin and Destination equals 'Chicago', how would I exclude that row without also excluding rows that have either Origin or Destination as Chicago.

Mr. GeeCee
  • 7
  • 6
  • Sorry, I should have added that it should only be for a specific origin and destination, so if they were both for example 'Dallas' I would want that to show, but if they are both 'Chicago' I don't want that to show' – Mr. GeeCee Sep 21 '16 at 15:46

4 Answers4

6
SELECT
    *
FROM
    Cargo
WHERE
    Origin != Destination
Nick J Chackowsky
  • 88
  • 4
  • This worked perfectly thank you! Do you know how to exclude a specific name for the origin and destination so that all other duplicates can be allowed? – Mr. GeeCee Sep 21 '16 at 15:49
  • Could you clarify your last question? Are you asking how to exclude a specific value if it appears as both origin and destination, but allow other duplicated values? If so you could use `SELECT * FROM Cargo WHERE Origin <> 'val' OR Destination <> 'val'` – Nathan M. Sep 21 '16 at 15:56
  • That's exactly what I was asking, sorry for the confusion. – Mr. GeeCee Sep 21 '16 at 15:57
  • No problem. Also, if you wanted to do something similar with a list of values, you could use something like `SELECT * FROM Cargo WHERE Origin <> Destination OR Origin NOT IN ('list', 'of', 'vals')`. – Nathan M. Sep 21 '16 at 16:07
2

You can use the WHERE clause to exclude all rows where the origin and destination both equal a specific value using

SELECT *
  FROM SHIPMENTS
 WHERE ORIGIN <> 'val'
       OR DESTINATION <> 'val'

or, if you wanted to exclude all items where the origin and destination are in a list of values, you could use

SELECT *
  FROM SHIPMENTS
 WHERE ORIGIN <> DESTINATION
       OR ORIGIN NOT IN ('list', 'of', 'vals')
Nathan M.
  • 286
  • 1
  • 9
0

If you just need to compare the two columns use the not equal operator =! or <> in your where clause, depending on your needs.

select... where Origin != Destination

Hope it helps.

Here you have a post about this operators Not equal <> != operator on NULL

Community
  • 1
  • 1
Victor Grimaldo
  • 58
  • 10
0

Try this: for the change requirement

SELECT *
FROM SHIPMENTS
WHERE ORIGIN <> 'Chicago' AND DESTINATION <> 'Chicago'
Shushil Bohara
  • 5,556
  • 2
  • 15
  • 32
  • Thanks I will try this! Would this also work if I used the like clause to exclude results that start with the same city name? For example, if i added and (OriginName not like 'Chicago%' and DestName not like 'Chicago%') – Mr. GeeCee Sep 21 '16 at 15:53
  • Of course it must address that and other required conditions as well. – Shushil Bohara Sep 21 '16 at 15:55