CODEWITHSUNDEEP
mysqljoin
Daanvn
Daanvn

Reputation: 1266

JOIN data from 2 MySQL tables

I have a question about joining the data from 2 tables from a MySQL Database. First I will explain what I currently have and then what I want just to be as clear as possible.

I have 2 tables in the Database which look like this:

Table: Subscriptions
Columns:
ID          int(11) PK AI
Klant ID    int(11) 
Mail ID     int(11) 
Status      varchar(15) 
Datum       varchar(15)

ID   Klant_ID Mail_ID Status Datum
123  6        6       90     21-03-2013
124  6        6       10     21-03-2013
125  6        5       90     21-03-2013
126  6        5       10     21-03-2013
127  6        1       90     20-03-2013
128  6        1       10     20-03-2013
129  6        2       10     21-03-2013
130  6        2       90     21-03-2013
131  6        4       90     21-03-2013
132  6        4       10     21-03-2013

And:

Table: Mail
Columns:
ID  int(11) PK AI
Content longtext 
Datum   varchar(15) 
Titel   varchar(150) 

ID  Content                 Datum       Titel
1   (alot of encoded html)  18-03-13    test
2   (alot of encoded html)  18-03-13    test2
4   (alot of encoded html)  18-03-13    alles weer testen
5   (alot of encoded html)  20-03-13    testje
6   (alot of encoded html)  21-03-13    Statusupdate week 6

I am using these 2 queries to select the data from the tables now:

SELECT ID, Titel FROM Mail
SELECT * FROM Subscriptions,
(SELECT MAX(ID) as ids, Mail_ID FROM Subscriptions
    WHERE Klant_ID = '".$_GET["ID"]."' GROUP BY Mail_ID) table2
WHERE ID=table2.ids

I want to get a query using JOIN to be able to create this table using html:

enter image description here

I personaly haven't got much experience using JOIN since I first used it yesterday, I am able to make a simple JOIN query but I just don't know how to do this. If you have more questions ask them in the comments. If anyone could help me with this it would be great!

Upvotes: 0

Views: 106

Answers (4)

M.I.T.
M.I.T.

Reputation: 1042

SELECT e.ID ,e.Titel,ea.Status,ea.Datum 
    FROM Mail e 
        LEFT JOIN Subscriptions ea 
            ON e.ID = ea.Mail_ID
            WHERE ea.Klant_ID = '".$_GET["ID"]."' 
            GROUP BY Mail_ID)
    ORDER BY e.ID ASC

Upvotes: 0

Daanvn
Daanvn

Reputation: 1266

Tyvm @kolonel peteruk, @Kaii and @JaMaBing for your answers!

With your help I was able to merge my query with yours. I finally got it to work using this query:

SELECT Mail.ID, Mail.Titel, Subscriptions.ID, Subscriptions.Status, Subscriptions.Datum,      Subscriptions.Mail_ID, Subscriptions.Mail_ID, Subscriptions.Klant_ID 
FROM NAW.Subscriptions
JOIN NAW.Mail 
ON Mail.ID = Subscriptions.Mail_ID, 
(SELECT MAX(Subscriptions.ID) as ids, Mail_ID 
FROM NAW.Subscriptions
WHERE Klant_ID =6  
GROUP BY Mail_ID) table2
WHERE Subscriptions.ID=table2.ids

Upvotes: 0

JaMaBing
JaMaBing

Reputation: 1041

this is a simple Join between your tables

SELECT Mail_ID, Titel, Status, Subscriptions.Datum FROM Subscriptions
JOIN Mail ON (Subscription.Mail_ID=Mail.ID)
    WHERE Klant_ID = '".$_GET["ID"]."' GROUP BY Mail_ID

the order of the rows is random by a join, if you want to get the last data, your query is right.

Upvotes: 1

ksugiarto
ksugiarto

Reputation: 951

Try:

select mail.id, mail.titel, subscriptions.status, subscriptions.datum
from mail join subscriptions on mail.id = subscriptions.mail_id

Upvotes: 1

Related Questions