CODEWITHSUNDEEP
mysqldatabase
Kevin
Kevin

Reputation: 6833

How to select rows from two tables that differ in one field?

Sorry for the entry-level database question but I really want to learn this.

I have two tables customer_change and customer_full and I want to select rows from customer_full that same customer(same customer_id) but with different customer_points.

I wrote the following query:

SELECT * 
FROM customer_change a,customer_full b 
WHERE 
    a.ID = b.ID AND 
    a.CUSTOMER_POINTS ！= b.CUSTOMER_POINTS

Now it works. But it will return rows in both tables, how could I change the query to only return the rows in the second table?

Also, the returned rows may contain two rows that have the same IDs, could I modify my query to only include the first row that has this ID?

Could experts offer some help? Thanks!

Upvotes: 1

Views: 192

Answers (2)

Dirk
Dirk

Reputation: 3093

The * will select everything which results from the cross product of all tables in your query. You can "select" specific columns by giving their name in the SELECT clause such as:

SELECT customer_points 
FROM customer_change

Or you can select only columns from a specific table (or multiple tables) by adding the table name to the beginning of the column name followed by a period. Such as:

SELECT customer_change.customer_points, customer_full.ID
FROM customer_change, customer_full

In your case, you can select ONLY the records in the second table:

SELECT b.* 
FROM customer_change a,customer_full b 
WHERE 
    a.ID = b.ID AND 
    a.CUSTOMER_POINTS != b.CUSTOMER_POINTS

Both <> and != are acceptable "not equals" operators.

If you wanted to use JOINs, then you can do:

SELECT b.* 
FROM customer_change a
JOIN customer_full b ON a.ID = b.ID AND a.CUSTOMER_POINTS != b.CUSTOMER_POINTS

Upvotes: 3

Dan J
Dan J

Reputation: 16708

The SQL inequality operator is <>, not !=. (Apologies: I note that != is supported in at least some implementations! As the comments on the question have pointed out, it seems to be a character set problem. I stand by the rest of my answer, though. :))

That said, I recommend learning about JOIN syntax, rather than joining tables using WHERE criteria. The former lends itself to more-readable queries, and lets you exercise finer control over how the tables are joined.

For example, your query above would be:

SELECT * 
FROM customer_change a
JOIN customer_full b ON a.ID = b.ID AND a.CUSTOMER_POINTS <> b.CUSTOMER_POINTS

Upvotes: 5

Related Questions