StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POneed help with tricky mySQL query
text
Body
copied!<p>I'm asking for an awful lot here - but maybe some SQL guru can show me how to extract the data I want and save me 10+ hours of google-time(tm)?</p> <p>These are my tables, with only relevant fields displayed:</p> <pre><code>**event** id cust_id .... </code></pre> <p>.</p> <pre><code>**art** id art_name ... </code></pre> <p>.</p> <pre><code>**event_art** event_id art_id ... </code></pre> <p>.</p> <pre><code>**price** cust_id art_id price ... </code></pre> <p>Prices in the "price" DB with user ID "0" is standard price, if an entry exists with art_id and cust_id that is customer specific price for that article.</p> <p>What I have is cust_id and what I have for output now is just the customer specific prices with SQL:</p> <pre><code>SELECT * FROM price WHERE cust_id='{$custID}' </code></pre> <p>But I'd like to include prices for previously ordered articles, even if they do not have a customer specific price.</p> <p>So what I need is to:</p> <p><strong>1</strong> Get all id's from table event where cust_id = custID</p> <p><strong>2</strong> Get all distinct article ID's on those orders from table event_art</p> <p><strong>3</strong> Output "id" and "art_name" of article from "art" and "price" from price table using custID or 0 for standard price if no entry exists.</p> <p>To me this sounds like a multi-line JOIN that's a bit outside my scope of SQL knowledge. Could somebody help me out, point me to a guide that deals with similar issues or... well, something?</p> <p>Thanks in advance!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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