SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)

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

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

    Code SQL:
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  2. #2
    SitePoint Zealot j3ph's Avatar
    Join Date
    Jan 2006
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by j3ph View Post
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    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 --
    Code:
    SELECT ...
      FROM ue_messages_delivery AS md
     WHERE md.recipient_id = 2
    so far so good, yes? this matches your requirement precisely


    Quote Originally Posted by SpacePhoenix View Post
    ... and then grabbing the messages from the table ue_messages
    now we require a join --
    Code:
    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 --
    Code:
    SELECT m.subject
         , m.message
      FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •