SQL query for multiple tables using JOIN

Question

Need help with SQL query - my query at the moment works with two tables I want to add third table, but i have no idea how to do that, I tried many times but it gives error...

I will try to show all my code as more acurate:

SQL QUERY

$sql = "SELECT main.nid, main.title FROM {node} AS main
                    LEFT JOIN {localizernode} AS lang ON main.nid = lang.nid 
                    WHERE main.type = 'drug' AND main.title LIKE '%s%%' AND lang.language = '%s' AND main.status = 1 
                    ORDER BY main.title ASC";

$result = db_query($sql);
while ($product = db_fetch_object($result)) {
// build up the list
$product_list .= '<li>'.$product->title.'</li>';
}

this QUERY grabs all data from table NODE based on website language where type is drug. And give me a list of all items in side that table, ordering by title

Table "node":

nid | vid | type | title | status

9 | 9 | drug | Title 1 | 1

15 | 15 | drug | Title 2 | 1

Table "content_type_drug" which I want to use/include in QUERY looks like:

vid | nid | value | image_title

9 | 9 | Text value | imagename.jpg

15 | 15 | Text value5 | imagename3.jpg

What I want it to grab from "content_type_drug" the following values: "value", "image_title" and display them in the above list:

$result = db_query($sql);
    while ($product = db_fetch_object($result)) {
    // build up the list
    $product_list .= '<li>'.$product->title.$product->value.$product->image.'</li>';
    }

I am looking for help, and not for FULL solution, please give me any ideas.

What is related field in content_type_drug and to which table it relates — Nouphal.M
– Nouphal.M, Commented Jan 22, 2014 at 15:03
i dont understand. please tell clearly. If the problem you are facing is that of same field names in multiple tables you could use alias like SELECT main.id AS main_id, lang.id AS lang_id ..... — Nouphal.M
– Nouphal.M, Commented Jan 22, 2014 at 15:06

Hassan · Accepted Answer · 2014-01-22 15:13:50Z

1

What i understand that you need to join three tables. So query will be

$sql = "SELECT main.nid, main.title, ctd.value,ctd.image_title FROM {node} AS main
        LEFT JOIN {localizernode} AS lang ON main.nid = lang.nid 
        INNER JOIN {content_type_drug} ctd ON main.vid = ctd.vid 
        WHERE main.type = 'drug' AND main.title LIKE '%s%%' 
        AND lang.language = '%s' AND main.status = 1  ORDER BY main.title ASC";

I am assuming that vid and nid are foreign keys. You can replace this clause with your foreign keys

edited Jan 22, 2014 at 15:13

answered Jan 22, 2014 at 15:05

Hassan

7427 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrei Over a year ago

Ok i guess this will do! But how will I grab info from third table and use it?

Hassan Over a year ago

Get into columns list like ctd.value,ctd.image_title. I have updated query. You can access it like other columns

Martin Dixon · Accepted Answer · 2014-01-22 15:05:26Z

1

To add a new table, you just need to add another join.

LEFT JOIN {content_type_drug} AS drug ON drug.vid = main.vid

So your full query will look like:

SELECT main.nid, main.title, drug.value, drug.image_title FROM {node} AS main
                LEFT JOIN {localizernode} AS lang ON main.nid = lang.nid 
                LEFT JOIN {content_type_drug} AS drug ON drug.vid = main.vid 
                WHERE main.type = 'drug' AND main.title LIKE '%s%%' AND lang.language = '%s' AND main.status = 1 
                ORDER BY main.title ASC

Hope this helps :)

answered Jan 22, 2014 at 15:05

Martin Dixon

282 bronze badges

Collectives™ on Stack Overflow

SQL query for multiple tables using JOIN

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related