How can I write this SQL SELECT query?

Question

Say I have two tables. new_dogs and name_color_pairs.

The records in name_color_pairs must be unique pairs.

new_dogs
---------
Name
Location Found
Color
Breed


name_color_pairs
---------
name
color

How can I select the name/color pairs from new_dogs that aren't currently in the name_color_pairs table so that they can be inserted?

You should write database, that you are using. Is it MySQL?

LukLed
– LukLed

2011-10-29 23:13:34 +00:00
Commented Oct 29, 2011 at 23:13 — LukLed
– LukLed, Commented Oct 29, 2011 at 23:13

LukLed · Accepted Answer · 2011-10-29 23:11:32Z

4

SELECT name, color FROM new_dogs
EXCEPT
SELECT name, color FROM name_color_pairs

or

SELECT name, color FROM new_dogs nd
WHERE NOT EXISTS (SELECT name, color FROM name_color_pairs ncp WHERE (ncp.name = nd.name) AND (ncp.color = nd.color))

or finally (should be working everywhere):

SELECT name, color FROM new_dogs nd
LEFT JOIN name_color_pairs ncp
ON (ncp.color = nd.color) AND (ncp.name = nd.name)
WHERE ncp.name IS NULL

edited Oct 29, 2011 at 23:11

answered Oct 29, 2011 at 23:02

LukLed

31.9k17 gold badges87 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MikeJerome Over a year ago

any chance you know how to solve this related question? stackoverflow.com/questions/7942650/…

Collectives™ on Stack Overflow

How can I write this SQL SELECT query?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related