Join tables problem - MySQL & PHP

Question

Hello, I'm trying to join two tables. The first table has a list of 11 items which are 'site_names' with an auto id field of 'id'. The second table that I want to connect has an auto id field of 'desc_id' and another field of 'descriptions'. This second table currently has 3 rows of data that I want displayed only for id 1 in table 1.

So, I want to accomplish is to connect the first site in table one with an id of '1' to the entire second table.

I can't seem to figure out how connect only the first entry(id=1) in table 1 to all the rows in table 2 (tb.1->id->1 to tbl.2->desc_id->1,2,3).

I hope that made sense. Any help would be great. Thanks

Answer 1

Try:

select site_name, descriptions
from table_1
inner join table_2
  on 1 = 1
where table_1.site_id = 1

This should join give you what you want.

OK - based on the comment, I'm guessing what you want is:

site1 | desc1 | desc2 | desc3

all on one row. This is a bit trickier - particularly if you want it to remain open to an arbitrary number of descriptions. For just 3 (or, really, any limited subset, but as the number goes up, it gets ugly), you could do:

select site_name, t2.desc, t3.desc, t4.desc
from table_1 
inner join table_2 t2
  on t2.desc_id = 1
inner join table_2 t3
  on t3.desc_id = 2
inner join table_2 t4
  on t4.desc_id = 3
where site_id = 1

This kind of stuff is highly irregular though. It seems to me like something about your schema is probably not quite right to generate this sort of requirement.

Answer 2

Here is the query:

<?php

        $mysql = new mysqli('localhost', 'root', 'root') or die('counld not connect');
        $result = $mysql->query("SELECT ajax_demo.explore.site_name, anthony1.property.descriptions FROM ajax_demo.explore INNER JOIN anthony1.property ON ajax_demo.explore.id = anthony1.property.desc_id") or die($mysql->error);

        if($result) 
        {
                                        while($row = $result->fetch_object()) 
                                        {
                                                $id = $row->id;
                                                $siteName = $row->site_name;
                                                $siteDescription = $row->site_description;


                                                echo "$siteName";
                                                echo "$siteDescription";
                                        }
        }

    ?>

Answer 3

I may be missing something here, but it sounds to me like you need to add a foreign key to the Site table. If I understand your question correctly, your tables should look something like this:

Site
- SiteID
- DescriptionID
- SiteName

Description
- DescriptionID
- Description

Then your query to get Sites and their associated Descriptions would look like this:

SELECT
  s.SiteName,
  d.Description
FROM
  Site s INNER JOIN Description d
    ON s.DescriptionID = d.DescriptionID

This table structure assumes that multiple Sites share single Descriptions (as per your posted question).