CODEWITHSUNDEEP
phpmysql
Fonewiz
Fonewiz

Reputation: 2087

MySQL 2 tables with common column only match if other condition exist

Not sure how to explain this exactly but here goes.

I have these tables:

messages:

------------------------
|msgid|clientid|message|
------------------------
|1    |536     |Hello  |
------------------------
|2    |543     |Hello  |
------------------------
|3    |529     |Hello  |
------------------------

clients:

----------------------
|cleintid|wholesaleid|
----------------------
|536     |479        |
----------------------
|543     |480        |
----------------------
|529     |479        |
----------------------

I want to only pull records from the messages table if the client id in the message record is owned by clients.wholesaleid = 479.

So that should pull records 1 and 3 from the messages table.

I know how to do with with completely separate multiple nested queries but I know there is a smarter way to do this with pure SQL in one query. I have been putting on learning this for way too long so I am here to figure this out.

Normally what I would do in this instances is do a simple query and loop through the clients table looking for all client id's that match the wholesale ID. I would stuff them all in an array or, as I loop through them, do a separate query for all messages for that one client id. It's gross and I know there is a better way. Tons of loops, within loops and slow as heck if my data set gets large.

I know this isn't correct but I am hoping it will help illustrate what I am trying to do.

Something like this but without multiple loops:

SELECT * FROM clients WHERE wholesaleid = 479;

For Each Loop Here

    SELECT * FROM messages WHERE clientid=ThisClientID

    For Each Loop #2 HERE

        Print msgid, clientid, message

    End For Each Loop #2

End For Each Loop

Sorry this is so confusing, just not sure how to better explain how I do it now and where I am trying to get.

I am guessing everyone will tell me to go learn JOINS but, I have spent plenty of time on that and I am not sure how it applies if at all for what I am trying to accomplish.

Upvotes: 1

Views: 66

Answers (2)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

YOU CAN JOIN THOSE TWO TABLE 

SELECT m.*
FROM messages m
INNER JOIN clients c
ON m.clientid = c.clientid
AND c.wholesaleid = 479;

Upvotes: 0

Blank
Blank

Reputation: 12378

Use INNER JOIN(cleintid in table clients might be a typo in your question):

SELECT messages.*
FROM messages
INNER JOIN clients
ON messages.clientid = clients.cleintid
AND wholesaleid = 479;

SQLFiddle Demo

Upvotes: 2

Related Questions