Databases
StackOverflow2013
Postsdbo
POSelect rows from query with a distinct foreign key?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect rows from query with a distinct foreign key?
    primarykey
    data
    text
    <p>I am having trouble just now with yet another SQL problem. I really need to take some time out to learn this properly.</p> <p>Anyway I have this query that someone else wrote and it gets values from a few different tables.</p> <p>Now more than one item can have the same ProductID. So there may be 3 items returned all with the same ProductID but they have different descriptions etc.</p> <p>I want to select only 1 item per ProductID. I have tried using DISTINCT and group by but I get a lot of errors. Also this is for an ACCESS database.</p> <p>I think it's because of the logic used in the select query that is messing up my grouping.</p> <p>Here is the query (I have tried formatting it a little better, used an online tool but its still a huge mess)</p> <pre><code>SELECT tblproducts.productid, tblproducts.categorycode, tblproducts.scaletitle, tblproducts.picture, tblitems.cost, tblitems.modelnumber, tblitems.itemid, Iif([tblitems]![tradeapproved],Iif(([tblitems]![markup] / 100) &lt;&gt; 0,(Iif(([tblitems]![supplierdiscount] / 100) &lt;&gt; 0, [tblitems]![cost] - ([tblitems]![cost] * ([tblitems]![supplierdiscount] / 100)), [tblitems]![cost])) * ([tblitems]![markup] / 100), 0) + Iif(([tblitems]![supplierdiscount] / 100) &lt;&gt; 0, [tblitems]![cost] - ([tblitems]![cost] * ([tblitems]![supplierdiscount] / 100)), [tblitems]![cost]) + [tblitems]![tradeapprovedcost] + [tblitems]![shippingcost], Iif(([tblitems]![markup] / 100) &lt;&gt; 0,(Iif(([tblitems]![supplierdiscount] / 100) &lt;&gt; 0, [tblitems]![cost] - ([tblitems]![cost] * ([tblitems]![supplierdiscount] / 100)), [tblitems]![cost])) * ([tblitems]![markup] / 100), 0) + Iif(([tblitems]![supplierdiscount] / 100) &lt;&gt; 0, [tblitems]![cost] - ([tblitems]![cost] * ([tblitems]![supplierdiscount] / 100)), [tblitems]![cost]) + [tblitems]![shippingcost]) AS price FROM (tblitems INNER JOIN tblproducts ON tblitems.productid = tblproducts.productid) INNER JOIN tblsuppliers ON tblproducts.supplierid = tblsuppliers.supplierid WHERE tblproducts.categorycode = 'BS' AND tblitems.tradeapproved = 0 AND tblsuppliers.active = on AND tblitems.isaccessory = false ORDER BY Iif([tblitems]![tradeapproved],Iif(([tblitems]![markup] / 100) &lt;&gt; 0,(Iif(([tblitems]![supplierdiscount] / 100) &lt;&gt; 0, [tblitems]![cost] - ([tblitems]![cost] * ([tblitems]![supplierdiscount] / 100)), [tblitems]![cost])) * ([tblitems]![markup] / 100), 0) + Iif(([tblitems]![supplierdiscount] / 100) &lt;&gt; 0, [tblitems]![cost] - ([tblitems]![cost] * ([tblitems]![supplierdiscount] / 100)), [tblitems]![cost]) + [tblitems]![tradeapprovedcost] + [tblitems]![shippingcost], Iif(([tblitems]![markup] / 100) &lt;&gt; 0,(Iif(([tblitems]![supplierdiscount] / 100) &lt;&gt; 0, [tblitems]![cost] - ([tblitems]![cost] * ([tblitems]![supplierdiscount] / 100)), [tblitems]![cost])) * ([tblitems]![markup] / 100), 0) + Iif(([tblitems]![supplierdiscount] / 100) &lt;&gt; 0, [tblitems]![cost] - ([tblitems]![cost] * ([tblitems]![supplierdiscount] / 100)), [tblitems]![cost]) + [tblitems]![shippingcost]) </code></pre> <p>Can anyone post a quick fix for this? Thanks</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USddd
    1. USddd
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThat code is a bloody labyrinth. I tried formatting but lost patience this far into the evening. Can you please format it to something legible? Or perhaps post a simplified version?
      singulars
      1. POSelect rows from query with a distinct foreign key?
      1. USEric
    2. CO@Eric, same story here... tried formatting it but gave up.
      singulars
      1. POSelect rows from query with a distinct foreign key?
      1. USJames Skidmore
    3. COOk, I know it's huge, plus the code I've been given has this in it like 12 times! It's a nightmare to work with, I'll make the edits and post a better one
      singulars
      1. POSelect rows from query with a distinct foreign key?
      1. USddd
 

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