Select Record In One Table Where There is A Match In Another

[SpacePhoenix]

I'm not sure that what i want to do is actually possible with a single query. The query i've got atm is:

SELECT subject AS subject, message AS message
              FROM ue_messages
              RIGHT JOIN ue_messages_delivery
              ON ue_messages_delivery.recipient_id = '2'

What I want to do is search the table ue_messages_delivery for message ids where the rows matched will be those matching the recipient_id in ue_messages_delivery and then grabbing the messages from the table ue_messages

An example:

ue_messages
message_id subject message
1 Test Subject 1 Test Message 1
2 Test Subject 2 Test Message 2
3 Test Subject 3 Test Message 3
4 Test Subject 4 Test Message 4

ue_messages_delivery
delivery_id message_id sender_id recipient_id date_sent message_read
6 3 3 3 0000-00-00 00:00:00 0
37 1 1 2 0000-00-00 00:00:00 0
38 2 1 2 0000-00-00 00:00:00 0
39 3 1 2 0000-00-00 00:00:00 0
40 4 1 2 0000-00-00 00:00:00 0
41 5 1 2 0000-00-00 00:00:00 0
42 6 1 2 0000-00-00 00:00:00 0

For example:

ue_messages_delivery searched for all messages for recipient number 2, it would get message ids 37, 38, 39, 41, 42 and them messages would be fetched.

If ue_messages_delivery searched for all messages for recipient number 3, it would get message id 6 and only that message would be fetched.

This will be used in a php app so if i can't do it in one query then i'll have to deal with two seperate queries.

The two tables are:

ue_messages

Table comments: Messages Table (Stores all PMs); InnoDB free: 10240 kB

Field Type Null Default Comments
message_id int(11) No
subject text No
message text No
ue_messages_delivery

Table comments: Message Delivery Table; InnoDB free: 10240 kB

Field Type Null Default Comments
delivery_id int(11) No
message_id int(11) No
sender_id int(11) No
recipient_id int(11) No
date_sent datetime No
message_read tinyint(1) No

[j3ph]

I may not be understanding fully, but wouldn't this do it:

SELECT subject AS subject, message AS message
FROM ue_messages m
INNER JOIN ue_messages_delivery d
ON m.message_id = d.message_id
WHERE d.recipient_id = '2'

[SpacePhoenix]

I may not be understanding fully, but wouldn't this do it:

ty j3ph it worked . At least i was thinking along the right lines but i guess i'll need to read up more on join querys (this is my first time using join querues).

I do wish that sitepoint could do a book on SQL, perhaps as a follow on to Kevin Yanks book.

[r937]

I do wish that sitepoint could do a book on SQL

your wish is granted

it will be announced right after the new year

it has already been pre-announced on amazon

but you will, of course, buy it here, eh

[r937]

What I want to do is search the table ue_messages_delivery for message ids where the rows matched will be those matching the recipient_id in ue_messages_delivery ...

okay, that works like this --

SELECT ...
  FROM ue_messages_delivery AS md
 WHERE md.recipient_id = 2

so far so good, yes? this matches your requirement precisely

... and then grabbing the messages from the table ue_messages

now we require a join --

SELECT ...
  FROM ue_messages_delivery AS md
INNER
  JOIN ue_messages AS m
    ON m.message_id = md.message_id
 WHERE md.recipient_id = 2

see how the join was added in?

the only step remaining is to decide which columns you want --

SELECT m.subject
     , m.message
  FROM ...

[SpacePhoenix]

your wish is granted

it will be announced right after the new year

it has already been pre-announced on amazon

but you will, of course, buy it here, eh

Sorry, have bought it at Borders. It looks like it was published here in the UK about half-way through November. Seeing as SitePoint is Australian I would have expected it to be published over there first, then the ROW.

[r937]

Sorry, have bought it at Borders. It looks like it was published here in the UK about half-way through November.

dude, no way!!!

do you actually have it in your hands now, or are you waiting for it to be delivered?

the difference is crucial

in mid-november i was still making significant changes to it, so i sincerely hope they didn't publish it in the shape it was then...

[SpacePhoenix]

dude, no way!!!

do you actually have it in your hands now, or are you waiting for it to be delivered?

the difference is crucial

in mid-november i was still making significant changes to it, so i sincerely hope they didn't publish it in the shape it was then...

r937, I've had confirmation of the order but have not yet been given a delivery date (it was gone 18:00 local time when i placed the order so will probably be emailed the delivery date tomorrow.

Borders list the publication date as November 15th, 2008 and it shows in their site as being In Stock. Amazon UK show it as "Not in stock; order now and we'll deliver when available" so I guess they've already sold out.

[r937]

okay, whew, for a second there it sounded like you already had a copy

i was jealous, because i haven't seen the final copy yet

i'm guessing none of the online book stores (to say nothing of brick and mortar) have any stock yet, rather than that they might have sold out already, because publication -- i was told -- would not be until end of december