ms access - SQL query. I would like to create a search that returns either the name of a contact, or the name of a company(and it's contact) -


i have 3 tables (because involves many many relationship):

tblcontact     tblcontcompjunction   tblcompany contactid      contactid             companyid           contactname    companyid             companyname

currently query built such:

select contactid, companyid, contactname, companyname,(contactname & companyname) searchconcat (tblcontact left join tblcontcompjunction on tblcontact.contactid = tblcontcompjunction.contactid)  left join tblcompany on tblcontcompjunction.companyid = tblcompany.companyid;

the problem have can't contact listed alone when has relatable company. need search determine between selecting company , contact, , fails.

based on comment, want each contactname "once each company , once himself."

this query give each contactname himself.

select     tblcontact.contactname,     'himself' companyname tblcontact

and query give row each contactname/companyname pair.

select     tblcontact.contactname,     tblcompany.companyname     (tblcontact     inner join tblcontcompjunction     on tblcontact.contactid = tblcontcompjunction.contactid)     inner join tblcompany     on tblcontcompjunction.companyid = tblcompany.companyid

use access' query designer set joins in case made error in untested sql.

once have both queries working correctly, can use union query combine them.

select     tblcontact.contactname,     'himself' companyname tblcontact union select     tblcontact.contactname,     tblcompany.companyname     (tblcontact     inner join tblcontcompjunction     on tblcontact.contactid = tblcontcompjunction.contactid)     inner join tblcompany     on tblcontcompjunction.companyid = tblcompany.companyid

Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more