I have two tables 1:n relationship, customer to orders the schema is in the http://sqlfiddle.com/#!9/a6842e/1 I have created.

The key in customer is composite id and type. I am trying to get the email address from the customer table where the order_id is = 2

So the query is:

SELECT email FROM customer c, orders o WHERE o.custId = c.id AND o.id = 2

This is returning two rows even though I specified o.id = 2

Here's a fiddle

How to get just the email where o.id = 2 and that rows foreign key = primary key of customer table.

Any help appreciated.

  • 1
    Your schema doesn't make a whole lot of sense, unfortunately. Firstly, you have multiple customers linked to the same order. Secondly, since custId does not uniquely identify a customer (it's missing a type), you can't use it as a foreign key (which would have been obvious if you actually tried to declare it). Rethink your data model. – mustaccio Oct 18 '19 at 23:37
  • @mustaccio thank you for the suggestions, yes you're right about the multiple customers with same order this as a rushed fiddle I have an updated one here sqlfiddle.com/#!9/5162e8/1 my question essentially is why are two rows returning when the o.id is specified as 1. Cheers – Abu Nooh Oct 19 '19 at 12:32

if we tried to simplify the query: SELECT email FROM customer c, orders o WHERE o.custId = c.id AND o.id = 2 we will get the following: 1- Get o.id = 2 and that will return (2, 1) row (the command will return only two, I have pasted the whole raw to pinpoint the row).
2- now let us search for custId from the result of point one (the result will be 1).
3- now the command will return email address where the custId=1

  (1, 'G','test1@mail.com'),
  (1, 'R','test3@mail.com'),

after filteration it will be test1@mail.com and test3@mail.com

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.