Combine SQL query

Question

Possible Duplicate:
What is the difference between Join and Union?

How do we combine separate queries into single sql statement? example:

SELECT packageid FROM tblhosting WHERE id='$id' AND userid='$userid'
SELECT id FROM tblcustomfields WHERE relid=tblhosting.packageid AND fieldname='foo'
SELECT value FROM tblcustomfieldsvalues WHERE fieldid=tblcustomfields.id AND relid='$id'

Now, I am aware of nested queryes, so I could write this like this:

but I was wondering if there is a better way to write this query?

[Update2] here is a version without variables, hope its more clear this time

SELECT packageid FROM tblhosting WHERE id='$id' AND userid='$userid'
SELECT id FROM tblcustomfields WHERE relid=tblhosting.packageid AND fieldname='foo'
SELECT value FROM tblcustomfieldsvalues WHERE fieldid=tblcustomfields.id AND relid='$id'

here I only interested in tblcustomfieldvalues.value

It's hard to provide a "good" answer when names and variables are changed in small ways after originally posting the question. — imm, Jun 18 '12 at 23:17

score 1 · Answer 1 · answered Jun 18 '12 at 23:06

What about:

SELECT h.packageid, cf.id, cfv.value
FROM tblhosting h
INNER JOIN tblcustomfields cf ON (cf.relid = h.packageid)
INNER JOIN tblcustomfieldsvalues cfv ON (cfv.fieldid = cf.id)
WHERE h.id = '$serviceid'
  AND h.userid = '$userid'
  AND cf.fieldname = 'foo'

John Woo · Accepted Answer · 2012-06-18T23:51:25.143

1

You can use INNER or LEFT JOIN for that depending on your need.

SELECT  c.value
  FROM  tblhosting a INNER JOIN tblcustomfields b
            ON b.relid = a.packageid
        INNER JOIN tblcustomfieldsvalues c
            ON c.fieldid = b.id
 WHERE  a.id = '$id'  AND   
        a.userid = '$userid' AND 
        b.fieldname = 'foo'

edited Jun 18 '12 at 23:51

answered Jun 18 '12 at 23:42

John Woo

258,903
69
498
492

this query works fine except the fact, it returns multiple different records but if I run 3seperate queries, it returns me a single unique record. – CuriousMind Jun 18 '12 at 23:47
i have updated the answer. I removed the two columns since you only need this column: `tblcustomfieldvalues.value` Right? – John Woo Jun 18 '12 at 23:52

Fluffeh · Answer 3 · 2012-06-18T23:11:34.483

0

If you want to get rows from all three tables only where rows exist in all three tables, which is what I am assuming you want, the SQL looks like this:

select
    a.id,
    b.value,
    c.packageid
from
    tbcustomfields a,
    tblcustomfieldsvalues b,
    tblhosting c
where
    c.userid='$userid'
    and a.relid=b.relid
    and a.relid=c.packageid
    and a.fieldname='foo'
    and b.fieldid=c.id

edited Jun 18 '12 at 23:11

answered Jun 18 '12 at 23:05

Fluffeh

33,228
16
67
80

Please use ANSI joins where possible. – Neil Jun 18 '12 at 23:30

score 0 · Answer 4 · answered Jun 25 '12 at 12:43

SELECT
  tblcustomfieldvalues.value
FROM tblhosting
  LEFT JOIN tblcustomfields
    ON tblcustomfields.relid = tblhosting.packageid
      AND tblcustomfields.fieldname = 'foo'
  LEFT JOIN tblcustomfieldsvalues
    ON tblcustomfieldsvalues.fieldid = tblcustomfields.id
      AND relid = '$id'
WHERE tblhosting.id = '$id'
    AND tblhosting.userid = '$userid'

TRY THIS

Combine SQL query

4 Answers4