DROP TABLE IF EXISTS messages;
CREATE TABLE messages( message text, reference char(10), sender char(10));
INSERT INTO MESSAGES VALUES
('message1 text' , '000005' , 'M000001'),
('message2 text' , '000010' , 'M000015'),
('message3 text' , '000005' , 'M000004'),
('message4 text' , '000005' , 'M000001');
DROP TABLE IF EXISTS senders;
CREATE TABLE senders( memberid char(10), name char(20) );
INSERT INTO SENDERS VALUES
('M000001' , 'Mr Smith' ),
('M000002' , 'Mr Jones' ),
('M000003' , 'Mrs Thomson' ),
('M000004' , 'Mrs Harisson' );
SELECT m.message, m.reference, s.name
FROM messages m
JOIN senders s ON m.sender=s.memberid
WHERE m.reference='000005';
+---------------+-----------+--------------+
| message | reference | name |
+---------------+-----------+--------------+
| message1 text | 000005 | Mr Smith |
| message4 text | 000005 | Mr Smith |
| message3 text | 000005 | Mrs Harisson |
+---------------+-----------+--------------+
PB
-----
Richard wrote:
Hi
My guess is I have not described my problem well enough then ...
Here is an example of table one :
MESSAGE | REFERENCE | SENDER
----------------------------------------------------
message1 text | 000005 | M000001
message2 text | 000010 | M000015
message3 text | 000005 | M000004
message4 text | 000005 | M000001
And of table 2 :
MEMBERID | NAME
----------------------------------
M000001 | Mr Smith
M000002 | Mr Jones
M000003 | Mrs Thomson
M000004 | Mrs Harisson
So I need the query to give me :
for $reference=000005
The following array :
MESSAGE | NAME
---------------------------------------
"message1 text" | "Mr Smith"
"message3 text | "Mrs Harrison"
"message4 text" | "Mr Smith"
Is this clearer ?
Thanks :)
Peter Brawley a écrit :
Richard,
This is elementary---you most definitely do not need to do it with
PHP code. Given tables messages(senderid, message, reference) and
senders(senderid, name, address), this query
SELECT m.message, m.reference, s.name
FROM messages m
JOIN senders s ON m.senderid=s.senderid
WHERE m.reference='$reference';
retrieves what you say you want. If it "does not work", there is a
mistake in the description of the problem.
PB
-----
Richard wrote:
I've tried it and it does not work,
the problem is that there needs to be 1 table1 row for each table2
row, and table 1 is the message list and the table two is the
members information list.
So I need the same row to be joined to all the message rows with the
same senderid ...
I guess I will have to manage this with the php code and not get the
result directly from the sql query; thanks anyway !
Peter Brawley a écrit :
That query will give one row per table1 row matching your WHERE
clause, with matched row from table2. Is that what you want?
PB
Richard wrote:
Thanks,
I think I have found the correct syntax in a book I've got :
SELECT A.message,B.name
FROM table1 A
JOIN table2 B ON A.senderid=B.senderid
WHERE A.reference="$reference";
I am just about to test this code, however I've now got doubts
that this will work as table1 contains more than one row with
eache userid whereas table two only contains one.... (table one is
the messages and table2 contains user information.
could this work or should I try another aproach?
Thanks :)
Richard
Peter Brawley a écrit :
Richard,
In ...
SELECT t1.message, t1.reference, t2.name
FROM tbl1 t1
JOIN tbl2 ON t1.senderid=t2.senderid
WHERE t1.reference = '$reference';
t1 and t2 are table aliases used to simplify table references.
The comma join syntax you cite will sometimes work, and sometimes
will not. It's not sufficiently reliable for general use.
Explicit join syntax is therefore always preferred.
The WHERE clause cannot precede the JOIN clause.
PB
-----
Richard wrote:
Yes that looks like what I am looking for.
could you explain what "FROM tabl1 t1" means ...I've seen "FROM
tbl1,tbl2" before but I haven't come accross seperation with a
space yet ...
I didn't explain everything I guess what I am looking for is :
SELECT t1.message,t2.name
FROM tbl1 t1
WHERE t1.reference='$reference'
JOIN tbl2 ON t1.senderid=t2.senderid;
But I don't quite understand the t1. or the t2. is it just a way
to make it more understandable?
Could I do :
SELECT message,name
FROM table1
WHERE reference='$reference'
JOIN table2 ON senderid=senderid2;
if this is wrong then I don't understand why there is no
reference to t2 in your code ("FROM tbl1 t1" but nothing for t2)
Sorry about these questions, I'm beginning with mysql !
Peter Brawley a écrit :
Richard
>I have table1 containing : message, senderid, reference
>and table2 contains: senderid, name, address
>I want to do a query that gives me : message, reference and
name ...
Do you mean ...
SELECT t1.message, t1.reference, t2.name
FROM tbl1 t1
JOIN tbl2 ON t1.senderid=t2.senderid;
PB
-----
Richard wrote:
Hello, I'm not sure if I can use union here or what syntax I
should use. I will simplify the tables to only include the
necessary information.
I have table1 containing : message, senderid, reference
and table2 contains: senderid, name, address
I want to do a query that gives me : message, reference and
name ...
Is this possible with one query?
Thanks !
Richard
