CODEWITHSUNDEEP
phpmysqlpdo
zuc0001
zuc0001

Reputation: 930

Selecting values from database by linking tables

I am looking for an efficient way to select values from multiple tables in my MySQL database, based off a "super" value from the main table.

For example the structure may be as such:

widgets         {id, widgetType, userId, pageId}
widgets_text    {parentId, `value`, style}
widgets_images  {parentId, src, link}

In this example above, the parentId in widgets_text and widgets_images is the "Id" in the table widgets

If I wanted to select ALL text and image widgets based off an entered Page id (pageId), how would I create a query for this?

Thanks in advance.

(Also I might add, if I wanted to do an insert statement for a new image [for example], how would I go about doing that?)

EDIT: Example of what I am doing at the moment:

SELECT
    *
FROM
    widgets w
    INNER JOIN widgets_text wt ON wt.parentId = w.id
    INNER JOIN widgets_images wi ON wi.parentId = wt.parentId;

^^ separate query must be run for each sub-table.

Upvotes: 2

Views: 48

Answers (2)

bish
bish

Reputation: 3419

To join the text and images table you could use the following statement where X is your entered pageID

select *
from widgets w
left join widgets_text wt on (wt.parentId = w.id)
left join widgets_images wi on (wi.parentId = w.id)
where w.pageId = X;

I'm using LEFT JOIN because from your questions it's not clear if these tables will defenitly have rows regarding to a widget row

Upvotes: 2

Ramki
Ramki

Reputation: 519

SELECT widgets.*,widgets_text.*,widgets_images.*
FROM widgets
INNER JOIN widgets_text on widgets_text.parentId = widgets.id
INNER JOIN widgets_images on widgets_images.parentId = widgets.id
WHERE widgets.pageId = '".$page_value."'

Use Inner Join

Upvotes: 1

Related Questions