Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is my take using traditional join syntax:</p> <pre><code>SELECT rocks.name, rocks.latitude, rocks.longitude, rocks.indoor, rocks.address, rocks.phone, rocks.email, rocks.website, rocks.closed, rock_types.type FROM rocks, rock_types WHERE rocks.latitude &gt; -180 AND rocks.latitude &lt; 180 AND rocks.longitude &gt; -180 AND rocks.longitude &lt; 180 AND rocks.type_id = rocks_types.id AND rock_types.type IN ('DWS', 'Top rope') </code></pre> <p>The <code>rocks.id = rocks_types.id</code> does the link between both tables.</p> <p>Here is how you can write the same query using <code>INNER JOIN</code> (both leads to the same result)</p> <pre><code>SELECT rocks.name, rocks.latitude, rocks.longitude, rocks.indoor, rocks.address, rocks.phone, rocks.email, rocks.website, rocks.closed, rock_types.type FROM rocks INNER JOIN rocks_types ON rocks.type_id = rocks_types.id WHERE rocks.latitude &gt; -180 AND rocks.latitude &lt; 180 AND rocks.longitude &gt; -180 AND rocks.longitude &lt; 180 AND rock_types.type IN ('DWS', 'Top rope') </code></pre> <p><strong>EDIT</strong>: Based on your comment, the following will give you a comma separated list of types:</p> <pre><code>SELECT rocks.name, rocks.latitude, rocks.longitude, rocks.indoor, rocks.address, rocks.phone, rocks.email, rocks.website, rocks.closed, (SELECT GROUP_CONCAT(DISTINCT rock_types.type SEPARATOR ',') FROM rock_types WHERE rock_types.id = rocks.id AND rock_types.type IN ('DWS', 'Top rope') GROUP BY rock_types.id ) AS type FROM rocks WHERE rocks.latitude &gt; -180 AND rocks.latitude &lt; 180 AND rocks.longitude &gt; -180 AND rocks.longitude &lt; 180 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. PONeed help with my query
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAndrew Moore
    1. USAndrew Moore
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PONeed help with my query
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COthank you Andrew. This would work if every rock had only one type associated with. However, that is not always the case. The rocks table is a list of rocks indexed by id. The rock_types table map the id to 1 or more types so for example id=1 might have 2 rows in rock_types, 1 for DWS and 1 for top rope. This query will only return one of those. Is it possible to get all the types associated with that id?
      singulars
      1. PO
      1. USgiroy
    2. COAhhh, should of included that in your original question. In which case, let me work this out.
      singulars
      1. PO
      1. USAndrew Moore
    3. COI edited my question with the solution to your problem.
      singulars
      1. PO
      1. USAndrew Moore
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload