MySql Multiple Tables query

Question

I wonder if any can help me understand something I'm trying to solve.

I'm working on a wordpress site but this is more a sql question as I'm just querying to get some results within a template file.

I have a gallery of pictures which are advert boxes, and I need to pull these in relation to a supplied movie name, to do this Im using some custom fields on the ad pic called 'adlink' (link off ad) and ad

I'm using the nextgen gallery plugin and querying those tables, and I have three tables in total that contain the data I need to query.

ngg_pictures, nggcf_field_values & nggcf_fields.

the nggcf tables are custom fields tables,

I have got so far I can get what I need in two seperate queries, but I can't combine these into one query as it means querying the nggcf_field_values table twice, which I can't seem to sort.

I have hardcoded the search criteria in for the mo, but the 'close-encounters' bit would be a passed var, and the '156' would be the pid from the first query.

SELECT `eg_ngg_pictures`.`filename`, `eg_nggcf_field_values`.`fid`, `eg_nggcf_field_values`.`pid`
FROM eg_ngg_pictures, eg_nggcf_field_values
WHERE ((`eg_nggcf_field_values`.`field_value` LIKE 'close-encounters') AND (`eg_nggcf_field_values`.`pid` = eg_ngg_pictures.pid))



SELECT `eg_nggcf_field_values`.`field_value`
FROM eg_nggcf_field_values, eg_nggcf_fields
WHERE ((`eg_nggcf_fields`.`field_name` = 'adlink') AND (`eg_nggcf_fields`.`id` = eg_nggcf_field_values.fid) AND (`eg_nggcf_field_values`.`pid`  = '156'))

any help would be greatly appreciated, I can get the results with what I have, but I like to understand how to combine these two and write better SQl. Thanks MRO

Learn about left, inner and outer joins. I believe it would do what your trying to do. codinghorror.com/blog/2007/10/… is a link — xQbert
– xQbert, Commented Dec 31, 2012 at 12:54

Andomar · Accepted Answer · 2012-12-31 13:14:27Z

After looking at the Wordpress extension, I think the eg_nggcf_fields is the table that contains the name for a custom field. The eg_nggcf_field_values table contains the values of that custom field per picture.

So if you're looking for two fields called moviename and adlink, you have to look up two rows in the field_values table. You can join a table twice if you give it a different alias:

select  pic.filename
,       pic.pid
,       fv1.field_value as MovieName
,       fv2.field_value as Adlink
from    eg_ngg_pictures pic
inner join -- Find ID for the field called 'moviename'
        eg_nggcf_fields f1
on      f1.field_name = 'moviename'
inner join -- Find value for field moviename for this picture
        eg_nggcf_field_values as fv1
on      fv1.pid = pic.pid
        and fv1.fid = f1.fid
inner join -- Find ID for the field called 'adlink'
        eg_nggcf_fields f2
on      f2.field_name = 'adlink'
inner join -- Find value for field adlink for this picture
        eg_nggcf_field_values as fv2
on      fv2.pid = pic.pid
        and fv2.fid = f2.fid
where   fv1.field_value like 'close-encounters'

SchmitzIT · Accepted Answer · 2012-12-31 12:56:15Z

1

First of all, I'd recommend sticking to modern ANSI syntax for JOINing tables, which means using the JOIN clause.

Instead of using:

FROM table1, table2 WHERE table1.id = table2.pid

use:

FROM Table 1 JOIN table2 ON table1.id = table2.id

For simplicity's sake, I'd also recommend you to alias tables, as that tends to make the code more readable. Instead of having to write out egg_ngg_pictures every time, you can simply refer to the alias you assign it instead.

Lastly, when you use a LIKE operator, you usually add a wild-card character (typically %. I.e. LIKE '%123' or LIKE '123%'). You seem to look only for complete matches, which means you can just stick to using =, as that should give you slightly better performance.

Now to rewrite your query, I'd use something like the following:

SELECT 
    pic.filename
    , fieldval.fid
    , fieldval.pid
    , fieldval.field_value
FROM 
    eg_ngg_pictures pic
        JOIN eg_nggcf_field_values fieldval ON fieldval.pid = pic.pid
        JOIN eg_nggcf_fields fields ON fields.id = fieldval.fid
WHERE 
    ((fieldval.field_value = 'close-encounters') 
    AND fields.field_name = 'ad_link'

Note that I am not able to test the query, as I do not have your schema. But by incorporating the two queries into a single query, the join on the field_Values.PID retreieved with the 'close_encounters' value should already exist.

If the query does not work, feel free to create a SQL fiddle with the relevant tables and some data, and I'll try and get it to work with that.

answered Dec 31, 2012 at 12:56

SchmitzIT

9,73912 gold badges71 silver badges96 bronze badges

1 Comment

mro Over a year ago

Hi Thanks very much - that's really usefull, I have been playing around with sql fiddle (thanks for the tip) as yet it's not playing, here's what I have so far sqlfiddle.com/#!2/8138a1/3

Collectives™ on Stack Overflow

MySql Multiple Tables query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related