Quering the node table twice in same query.

By fatfish on 19 Jul 2006 at 08:35 UTC

Hi.
I found a problem producing complex queries since the node table in the base of all content.
I have content type with extra db called JOB. It has 2 fields - nid and 'top price'.
Second content type has extra db called APL. It has 2 fields - nid and 'offer price'.
Now - how can I create a query that produces a sortable table of job.title, job.top_price, apl.title, apl.offer_price?
I found myself lost in queering node table from few directions, very often (maybe I should choose 'computers sciences' instead of 'product design' for a diploma...)
a diploma...)

Tnx.
Fish

Comments

sql

drupalxykon commented 19 July 2006 at 12:35

This has little to do with the node table or drupal.

A hint is that you want node.title not job.title

also, do you want job and apl to be related to each other? If so, you want to add a foreign key-primary key between them.

Either that, or they are unrelated but you just want an union query between them..depends on what you are trying to do

-v

http://www.vjrao.com

If they are related

nevets commented 19 July 2006 at 13:31

If they are related, that is job.nid == apl.nid == node.nid and only one record in each table for a given nid and the nid is present in all tables this should get you started.

$sql = "SELECT n.tittle, j,top_price, a.offer_price FROM {node} n JOIN {job} j USING(nid) JOIN {apl} a USING(nid)";

This will produce one record for each nid in all three tables. You can add a WHERE clause to select a subset of the records and an ORDER BY clause to determine the sort order.

if they are related like

drupalxykon commented 19 July 2006 at 18:20

if they are related like that then it makes no sense to have two seperate tables.

I meant an app corresponds to a job in some way even though they are different node ids..thats one way they could be related.

-v

http://www.vjrao.com

there is a 3rd table

fatfish commented 20 July 2006 at 07:27

that stores job and apl relations - jid and aid, so the query's purpes is to show all jobs and there related aplications in a single table. Therefor I need to titles on diferent nodes from the node table in a single query.

tnx.

..:| Tomer Fish |:..
fatFish - Lean Mean Coding Machine

Then you want something like this

nevets commented 20 July 2006 at 13:16

This example assumes the join table is called job_apl_relation where jid is the nid in the job table and aid is the nid in the apl table.

$sql = "SELECT n1.tittle as job_title, n2.title as apl_title, j,top_price, a.offer_price
                         FROM {node} n1 JOIN {job} j ON(n1.nid = j.nid)
                         JOIN {job_apl_relation} ja ON(j.nid = ja.jid)
                         JOIN {node} n2 ON(n2.nid = ja.aid) JOIN {apl} a USING(n2.nid = a.nid)";

but how to DISTINCT

fatfish commented 26 July 2006 at 08:45

I got so meny duplicats and I couldnt find a way to distinct .

$sql = "SELECT DISTINCT (n1.nid) , n1.tittle as job_title, n2.title as apl_title, j,top_price, a.offer_price
                        FROM {node} n1 JOIN {job} j ON(n1.nid = j.nid)
                        JOIN {job_apl_relation} ja ON(j.nid = ja.jid)
                        JOIN {node} n2 ON(n2.nid = ja.aid) JOIN {apl} a USING(n2.nid = a.nid)";

Do not seems to make it.

..:| Tomer Fish |:..
fatFish - Lean Mean Coding Machine