CODEWITHSUNDEEP
sqlpostgresqljoin
diegoaguilar
diegoaguilar

Reputation: 8376

Could distinct JOIN types give the same result?

I have following schema:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(40) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE sitters (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users ON DELETE CASCADE UNIQUE NOT NULL,
  slug VARCHAR(255) NOT NULL,
  headline VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE bookings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users ON DELETE CASCADE,
  sitter_id UUID REFERENCES sitters,
  start_date date NOT NULL,
  end_date date NOT NULL,
  pets_ids UUID[] NOT NULL,
  status VARCHAR(15) NOT NULL DEFAULT 'created',
  total_amount_cents INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  booking_id UUID REFERENCES bookings ON DELETE CASCADE,
  sender_id UUID REFERENCES users ON DELETE CASCADE,
  receiver_id UUID REFERENCES users ON DELETE CASCADE,
  content TEXT NOT NULL,
  seen_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

So:

I'd like to:

Given a user_id, a sitter_id and a booking_id return booking's messages if:

So I got: 

SELECT m.*,
b.user_id, b.sitter_id
FROM bookings b
LEFT JOIN messages m
ON m.booking_id=b.id
WHERE b.id=$1 AND (b.user_id=$2 OR b.sitter_id=$3);

I'd like to receive:

And it works properly, but after trying distinct JOINS I see same results. Any scenarios where it could break?

Upvotes: 1

Views: 85

Answers (1)

goodguy5
goodguy5

Reputation: 419

What do you mean by:

And it works properly, but after trying distinct JOINS I see same results. Any scenarios where it could break?

It seems that you're using the word "distinct" in the English sense, not in the SQL sense, which is a bit confusing in an SQL question.

What are you joining on?

  • Any left joins to the other tables should have no effect, with the possible exception of your pets table.
  • Joining to the users or sitters table could potentially cause the query to not work if the user/sitter is not found.

I'd like to recieve booking id fields if booking exists but has not messages

The way that you're selecting columns will cause a null booking id, if there are no messages for the booking.

  • consider listing all columns manually

Please see my simplified SQLFiddle http://sqlfiddle.com/#!15/8b976/2
Please, also consider clarifying your question.

Upvotes: 6

Related Questions